Whenever I try to tune long-running queries, I get a skewed result for all the alternative queries executed and do not get a true representation of how long this alternative will take to run. Most of the queries that get executed, are typically run once for a long period of time, so if the caching of the rows in a query could be minimized, a truer test of comparison to the original could be had. A fairly new hint NOCACHE could help in that it caches the blocks retrieved for the query-block at the least recently used end of the LRUlist in the buffer cache and so will be invalidated in a short amount of time. And as such will force the next execution of the query to behave as though it’s the first time to execute the sets of table joins in the query.
Actually, there is a new batch test run feature for flushing Buffer_Cache in version 8.5 beta version. This new feature can clear data in buffer cache before each execution of SQL alternative. It achieve the same purpose as what you suggested.
Richard
But the NOCACHE hint only would apply to those specific tables involved in the query. Doesn’t the clear buffer cache option apply to ALL buffer cache ? Many times when I am handed a query to tune, it’s already been promoted to production. And as such, clearing ALL buffer cache could be detrimental to production efficiency ??
Hi Rich,
According to the Oracle documentation (http://www.oracle.com/pls/db112/to_URL?remark=ranked&urlname=http:%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FE11882_01%2Fserver.112%2Fe17118%2Fsql_elements006.htm%23SQLRF50904), the NOCACHE hint only affects caching behavior “when a full table scan is performed”. Furthermore, it states that NOCACHE is the standard behavior for full table scanning. CACHE was therefore made available as a hint for small lookup tables being full table scanned but that are used often enough to benefit from full caching. Oracle has made numerous improvements in its caching algorithms since this hint first appeared and I wouldn’t be surprised if its usefulness has significantly diminished.
Now it would be great, for SQL tuning purposes, if Oracle provided a mechanism for marking and then flushing the cached data brought in by a query during its execution. That would definitely improve our lives as SQL tuners! Unfortunately, the NOCACHE hint isn’t quite what we need for our purposes.
The best we have available right now is the system-wide mechanism for flushing the entire contents of the buffer cache and as Richard stated we’ve implemented an option in the upcoming version of SQL Optimizer for Oracle 8.5 to flush the buffer cache between SQL alternative test executions. Now, this “brute force” method is not perfect and should only be done on a non-production database where users won’t be severely impacted. Some tuning professionals were already doing this manually and so we’ve added a level of automation to their efforts with this option implementation.
Kind Regards,
Greg
You are correct. I had a misinterpretation of utilizing the @QUERYBLOCK option for the NOCACHE hint. But even in that situation, only when I specify FULL on that QUERYBLOCK does it take effect. Thank you.