If the statement is using bind variables it sounds like it may well be related to bind variable peeking. The problem commonly occurs with histograms on skewed columns. You should see the plan flip-flopping in the the AWR tables if that's the issue. Here's a quick script to show that.
set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,(elapsed_time_delta/decode(nvl(executions_delta,0), 0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0), 0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
Lot's has been written about this issue - check out Doug Burns,
Randolf Geist, Jonathan Lewis blogs for articles on the issue (mine
has a few as well). Sorry in advance to anyone I've slighted as I know
a bunch of people have written about this issue.
Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Sep 16, 2009, at 10:58 AM, Michael Schmitt wrote:
Hi All,I was hoping the list could help provide me with some knowledge that I am lacking. We have a 10.2.0.3 database that our developer team runs nightly processing against. Randomly, one of the perl scripts that runs SQL against the database takes hours instead of the 5 minutes. Typically we just kill the script and rerun it, and it runs fine afterwards. I have been trying to replicate the issue, but have only had luck 1 time in the test system.Something I noticed when the script executes normally is that I see the following from v$sqlSQL_ID LOADED_VERSIONS EXECUTIONS HASH_VALUE PLAN_HASH_VALUE OPTIMIZER_COST ------------- ---------- --------------- ---------- ---------- ---------- -------------- --------------- -------------- ghrx1dj10tt6t 1 0 1108141273 3153586504 179 (we seem to run into issues when this one is executed) ghrx1dj10tt6t 1 26440 1108141273 2439843730 241 (this one actually works best)The statement that is being executed has two different entries in v $sql with different PLAN_HASH_VALUES and COSTS associated with them. In our case, the plan with the higher cost is performing betterWhen the statement takes hours, it actually uses the plan with the lower cost.I am trying to figure out was is causing this issue. I was thinking it might have something to do with bind peeking. Would bind peeking cause the statement to use the plan with the higher optimizer cost (which is a good thing on our case), and sometimes cause it to chose the plan with the lower cost?Thanks
