Re: SQL Performance Problem between 2 Databases WITH FIX included for this case
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Mon, 16 Jan 2012 15:27:59 -0700
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).
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
