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):
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:
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.
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.
[…] and LEADING are the hints closest to magic), you can use the information from the article Making the Oracle Query “Magically” Faster to learn from, and then you can use this knowledge to your advantage. In that article, the query ran […]