Re: Query runs fine on InstanceA but slow on InstanceB...

On Fri, Jan 6, 2012 at 6:30 PM, Allen, Brandon
<Brandon.Allen@xxxxxxxxxxx> wrote:
> Great suggestions from Kellyn and Cary, but from the OP, it sounds like he's 
> already isolated the problem to a specific query, and already identified that 
> it's getting two different explain plans and what he's really trying to 
> figure out is why the two different plans are being chosen.
>
> David, can you please confirm if my understanding is correct so we can focus 
> the discussion more?
>
> Thanks,
> Brandon

Hi Brandon,

You are correct, I already have the SQL isolated and can repeat the
behavior on both instances. I have a cut and dried good execution plan
vs bad execution plan situation. I am just trying to get a 'Why' for
the Execution Plan differences in an efficient, repeatable manner.

If I had the same Execution Plan on both systems then I think 10046
would be the way to go so I could see where it was spending its time.

WIth smaller/less complex queries I have had some success by browsing
10053's side by side and seeing where the CBO decisions diverge. But I
recently had a query that had a10053 that was 80mb for the 'good' plan
and 140mb for the 'bad' plan - this is a bit much to digest on my own
right now.

So I guess you could say I have a hybrid approach right now... If the
10053 doesn't give up its secrets easily I work it from the other end
to see if I can find differences in CBO inputs.

For these larger queries I hit my checklist and run queries to compare
CBO inputs that can affect execution plans...
o Data volume current and history - COUNT(*), dba_tables.num_rows,
SYS.WRI$_OPTSTAT_TAB_HISTORY
o Instance parameter differences - it is easy to compare using
v$parameter or by diffing pfiles, or parameter section from the 10053
traces
o System statistics differences - sys.aux_stats$
o Object/Column/Index stats - this is currently the evolving part of
my investigations, I check basic table/column stats but haven't waded
into partitioning/index/histogram stats yet with my comparison queries

Of course I always have the big hammer of SQL Profiles, Stored
Outlines, or SPM but when using these I usually have a nagging feeling
that I know the query ran better on InstanceA and there is something
that needs to be tweaked to get InstanceB's Execution Plan in line.

-- 
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
http://www.freelists.org/webpage/oracle-l


Other related posts: