RE: Query execution question

From: Michael Schmitt <mschmitt_at_uchicago.edu>
Date: Wed, 16 Sep 2009 12:18:26 -0500
Message-ID: <E37E556CF8A6C44381D2DA9FC354D2410153FF4BFB7D_at_EVS03.ad.uchicago.edu>


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_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
Cc: 'oracle-l_at_freelists.org'
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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 16 2009 - 12:18:26 CDT

Original text of this message