Re: Query runs fine on InstanceA but slow on InstanceB...
Date: Sat, 7 Jan 2012 00:32:01 -0500
Message-ID: <CAGazuyXKWvBm-cdyBBWL46qwcvXkrE+5y9Mg2xKML=tkUCqR4Q_at_mail.gmail.com>
On Fri, Jan 6, 2012 at 6:30 PM, Allen, Brandon <Brandon.Allen_at_oneneck.com> 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-lReceived on Fri Jan 06 2012 - 23:32:01 CST