Making the Oracle Query “Magically” Faster

By: Richard Niemiec on April 20, 2013


Consider the following query in which the user adds a hint called “RICHS_SECRET_HINT.” The user overheard a conversation about this hint at a recent user group and believes this hint (buried deep in the X$ tables) is the hidden secret to tuning. First, the query is run and no index can be used (a large EMPLOYEES table with over 14M rows):

 

0421_001

 

 

 

 

 

 

 

There is no index that can be used on this query. A full table scan is performed.

The user now adds Rich’s secret hint to the query:

0421_002

 

 

 

 

 

 

 

The hint worked and the query is “magically” faster, although a full table scan was still performed in the second query. Actually, the data is now stored in memory and querying the data from memory is now much faster than going to disk for the data—so much for the magic! By effectively using the 11g Result Cache, you can magically make things faster as well.

 

TIP

When running a query multiple times in succession, it becomes faster because you have now cached the data in memory (although full table scans are aged out of memory quicker than indexed scans). At times, people are tricked into believing that they have made a query faster, when in actuality they are accessing data stored in memory. Flushing the buffer cache or restarting the test system can help you get accurate tuning results for comparisons.

Trackbacks

Leave a Reply