While it is disappointing that there is no “secret hint” for tuning (ORDERED 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 faster the second time because it was cached in memory.
What if the tables used most often were cached in memory all the time? Well, the first problem is that if you cannot cache every table in memory, you must focus on the smaller and more often used tables to be cached. You can also use multiple buffer pools. The following query is run against an unindexed customer table to return one of the rows:
The database is then stopped and restarted so as to not influence the timing statistics (you can also perform an “ALTER SYSTEM FLUSH BUFFER_CACHE” but only do this on a test system). The table is altered to cache the records:
Query the unindexed, but now cached, SALES table and it still takes 0.84 seconds. The table has been altered to be cached, but the data is not in memory yet. Every subsequent query will now be faster (after the first one). I query the unindexed (but now cached) SALES table to return one of the rows in 0.04 seconds, or 21 times faster (this increase in speed could add up fast if this query is run thousands of times):
The query is faster because the table is now cached in memory; in fact, all queries to this table are now fast regardless of the condition used. A cached table is “pinned” into memory and placed at the “most recently used” end of the cache; it is pushed out of memory only after other full table scans to tables that are not cached are pushed out. Running a query multiple times places the data in memory so subsequent queries are faster—only caching a table ensures that the data is not later pushed out of memory. Oracle 11g caches frequently used data, by default, as you access things over and over.
TIP
Caching an often-used but relatively small table into memory ensures that the data is not pushed out of memory by other data. Be careful, however—cached tables can alter the execution path normally chosen by the optimizer, leading to an unexpected execution order for the query (for instance, affecting the driving table in nested loop joins
Leave a Reply