|
Need For SQL Statement Tuning
|
The need for tuning, a simple example
SELECT MPRN
FROM TLM_CONSUMERS
WHERE ZGC = 21991022
The above statement looks quite simple.But, It is a real complex statement, multiple sorts, table scans, complex inner and outer joins.
It has unique index on the ZGC column.
after obtaining an explain plan for the statement and sure enough Oracle was doing a full table scan for this statement, each and every time it was run. The statement had been executed 331 times over the past few days and caused disk reads of 180 Gigs. In fact this statement and its multiple executions were responsible for approximately 90% of the workload on the Server. Here was the glue in the hard drives.
Quite simply, the ZGC column (and therefore the index) is a VARCHAR2 column. The value being looked for was a numeric and so Oracle didn't use the index. It was performing a full table scan on 1.2 million rows and had done so 331 times over the past few days.
To put that in context, Oracle was having to physically read an average of 200Mb per minute, every minute just to satisfy this statement. I imagine the execution time of this statement was somewhere in the 10's of minutes.
A simple change to the statement of adding single quotes around the value like this
SELECT MPRN
FROM TLM_CONSUMERS
WHERE ZGC = '21991022'
resulted in a lightning fast execution time as would be expected, and almost zero workload on the server. Not only would the user or process executing the statement notice the difference, everyone using that server would notice the difference.
The above Example was quite simple and yet changing few things could improve the result multiple times.
In my coming chapters, I will give a detailed aspects of SQL Statment Tuning.
|
|
NEXT CHAPTER
More Tutorials on Oracle dba ...
Liked it ? Want to share it ? Social Bookmarking
Source : Oracle Documentation |
Oracle DBA
Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests
to
webmaster@oracleonline.info
|
