RE: Query execution question
Date: Wed, 16 Sep 2009 12:18:26 -0500
Yes, I have seen the plan flip-flopping in the AWR tables when I selected against dba_hist_sqlstat in the past (it hasn't happened in the last 7 days so it is no longer there).
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kerry Osborne
Sent: Wednesday, September 16, 2009 11:31 AM
To: Michael Schmitt
Subject: Re: Query execution question
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 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
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.
On Sep 16, 2009, at 10:58 AM, Michael Schmitt wrote:
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$sql
SQL_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 better
When 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?
ThanksReceived on Wed Sep 16 2009 - 12:18:26 CDT