RE: Query execution question
- From: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Sep 2009 12:18:26 -0500
Hi Kerry,
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).
Thanks
Mike
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kerry Osborne
Sent: Wednesday, September 16, 2009 11:31 AM
To: Michael Schmitt
Cc: 'oracle-l@xxxxxxxxxxxxx'
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,
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$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?
Thanks
Other related posts:
