Re: SQL Performance Problem between 2 Databases WITH FIX included for this case

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 16 Jan 2012 15:27:59 -0700
Message-Id: <EFF12803-0540-4BB9-B79B-1EE9CECAC0BA_at_centrexcc.com>

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-l

Received on Mon Jan 16 2012 - 16:27:59 CST