i am on 7.5.0. I had a question with regrads to sql tuning.
The sql i am trying to tune only runs ones a day. So obviously, the data
is not in the buffer cache. So when i get the timing info, i only want to
use it for the first time. Using QSO…we did get the alternatve…
which is good and runs under 2 mins. But if we take the same alternative
and run it the next days it takes about 15 mins or so. obviously because of the
data not being in buffer cache.
The alternate that we got for 2 mins was due the the data being in buffer cache…
if you know what i mean…the original ran, alt1, alt2. alt3 ran…and so on…
so the data was in the buffer cache and thats why we got the 2 min time for that alt…
but we need something like…run all sql once…but also…include,
alter system flush buffer_cache; after each execution…so it gets the
senerio like the sql is being ran once a day(when no data is present in cache…)
is there a way to do this in QSO or any work around for this type of situation?
It is a very good question, normally, most alternatives will read the same data from the memory, if it is not in memory, it will read it from harddisk, we call it Physical Read. For some alternatives that introduce extra sorting, grouping, join order or filter criterias order changes may cause different physical reads. In your situation, the 2 mins alternative most like already the best alternative, If you want to further confirm it is also good in Phycal Read, please check the Session Logical Read, if there is any one lower than this one, you can try it and see whether it perform less Physical Read.
I have a very similar issue with 8.0 and 8.5 (beta). I have a query that I want to optimize that uses 2 bind variables for date ranges (common scenario for reporting I guess); when I run the alternatives in batch, I get asked to input the value for those variables which works just fine. However, since the queries are made using the same date ranges, the alternatives, when tested just seems to get better and better because data and/or execution plans gets cached by Oracle (my guess). If I take any of those “better SQL” and run it in Toad with different dates (but same amount of days and similar data volume), I get back to my original query time. So it seems that many, if not all the alternative queries are actually “false positives”.
I tried the “flush buffer” feature of the 8.5 (beta) version, but I get the same behavior, is there something I’m not getting? Is there a way to make the batch process execute each query with different bind variable values (in a set of pre-defined values for “replayability purpose”)?
To identify the problem that why a tuned SQL cannot keep good performance in other environment:
Check the execution plan of the SQL from SGA, if the execution plan is changed from what Quest SQL Optimizer proposed, it means the new syntax cannot stabilized the query plan for the SQL in the life environment, you better select other SQL alternative with Hints that has better enforcement to the plan generation.
If the query plan is the same as what we proposed, I guess your SQL statement’s performance is highly depending the actual IO, so, you may need some configuration level tuning, for example add more memory, cache/pin some tables into memory and etc…
There may be a problem for SQL with bind variables, if the SQL is bind sensitive, the query plan will be changed with various bind values pass in. Currently, it is a limitation in our product (I don’t find any other product can do that) that we cannot test and explore all potential query plans, I am working on a new design to solve this problem, it is a bind sensitive with multiple binds values SQL tuning function.