Re: Query execution question

From: Kerry Osborne <>
Date: Wed, 16 Sep 2009 11:30:44 -0500
Message-Id: <>

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

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 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
> ------------- ---------- --------------- ---------- ----------
> ---------- -------------- --------------- --------------
> 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

Received on Wed Sep 16 2009 - 11:30:44 CDT

Original text of this message