Re: Query runs fine on InstanceA but slow on InstanceB...
Date: Sat, 7 Jan 2012 09:02:04 -0800 (PST)
Message-ID: <1325955724.27793.YahooMailNeo_at_web121003.mail.ne1.yahoo.com>
So if you have isolated the bad plan and the good plan and assuming you are on 10g or higher, is it not an option to create an outline/profile and import it into the instance b to ensure you only impact this one process or export stats for both instances to a table for comparisons or to import them into instance b to see if this solves the issue? Is this an exercise to figure out EXACTLY what differences exist between the two instances or to solve the one problem with the one process? I guess I haven't experienced the issue where using a smaller trace to help me dig into a larger trace has been a problem. I often have only a few complex joins, it's commonly the size of objects joined that impact me and finding ways to eliminate the hashing and sorting as my goal to performance gains here... :)
Kellyn Pot'Vin
Sr. Database Administrator and Developer
DBAKevlar.com
From: David Mann <dmann99_at_gmail.com> To: "Allen, Brandon" <Brandon.Allen_at_oneneck.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, January 6, 2012 10:32 PM
Subject: Re: Query runs fine on InstanceA but slow on InstanceB...
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-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 07 2012 - 11:02:04 CST