Re: Query runs fine on InstanceA but slow on InstanceB...
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "dmann99@xxxxxxxxx" <dmann99@xxxxxxxxx>, "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Sat, 7 Jan 2012 09:02:04 -0800 (PST)
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@xxxxxxxxx>
To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
