RE: SQL Performance Problem between 2 Databases WITH FIX included for this case
Date: Mon, 16 Jan 2012 16:33:35 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6ADB9795FA_at_SPOBMEXC14.adprod.directory>
I'm still partial to the 10046 due to all the information it gives you. Does Tom's bstat/estat script give execution plans with row counts and wait events and recursive sqls? If not, I can get all that at once :)
(But I still have to remember to actually look at all of it and not get too single minded about a particular piece of it)
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]
Sent: Monday, January 16, 2012 4:28 PM
To: Taylor, Chris David
Cc: 'oracle-l_at_freelists.org'
Subject: Re: SQL Performance Problem between 2 Databases WITH FIX included for this case
A 10053 trace won't do you any good if the plans are the same. But you don't necessarily need a 10046 trace either. Just looking at v$session_event will give you that info as well. I've taken a page out of Tom Kyte's runstats script and created bstat / estat sql scripts. bstat save the current values to a GTT. Estat does the same and the prints the deltas. Or you could use Tanel's snapper script. Although that could present a problem for the faster query.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
On 2012-01-16, at 3:04 PM, Taylor, Chris David wrote:
>
> D1:
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ---------- ------------
> SQL*Net message to client 1 0.00 0.00
> direct path write temp 1016 0.09 0.44
> latch free 1 0.00 0.00
> direct path read temp 4572 0.03 3.79 <------------------ LOOK
> db file sequential read 238 0.01 0.55
> db file scattered read 229 0.01 0.50
> SQL*Net message from client 1 4.74 4.74
> *******************************************************************************
>
> D2:
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ---------- ------------
> SQL*Net message to client 1 0.00 0.00
> direct path write temp 1030 0.08 0.18
> db file sequential read 2 0.00 0.00
> local write wait 171 0.01 0.25
> direct path read temp 66950 0.19 39.85 <-----------------------LOOK
> SQL*Net message from client 1 32.32 32.32
> *******************************************************************************
>
> I'm such a dumb*** sometimes.
>
> I was out of (or low in) PGA memory in D2 due to the heavy usage by the development staff.
>
> Bump up PGA_AGGREGATE_TARGET and performance goes back to sub 3 secs for the query. Now D1 and D2 perform exact.
>
> This is WHY a 10046 trace should be FIRST step in examining a performance issue so you can *know* what you are waiting on before even thinking about 10053 traces. (In my honest opinion anyway).
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 16 2012 - 16:33:35 CST