Re: Help with statspack report comparison

"Martin T." <0xCDCDCDCD_at_gmx.at> wrote in message news:46f64be8$0$1340$834e42db_at_reader.greatnowhere.com...
> Hello,
> [Oracle 9i2, Windows XP / Windows Server 2003]
>
> I am currently trying to figure out why one of our sites has such a
> horrible performance. To this end I took statspack snapshots over the
> last few days from the "bad" site and from one "good" site and now I'm
> trying to compare two reports for both sites.
>
> Both sites are running the same software with the same usage patterns.
>
> The *good* (marked AT10) site is running on XPsp2 with one 7200RPM HD
> with a dual core P4 (3,4 GHz) and 1GB of RAM
>
> The *bad* site (marked DE30) is running Win2003r2 with one 7200RPM HD
> with a dual core Xeon (2,13 GHz) and 2GB of RAM.
>
> The most striking figure (for me, at this time at least) seems to be the
> <db file scattered read> timed events. The problem is that I am not too
> sure what to make of this.

Some of the SQL you've listed seems compatible with the summary figures. In particular, note:

> 18,446,371 1,640 11,247.8 67.4 50.80 51.76
> 395610491
> SELECT MDT.ID , MB.Time_stamp TS_START , ME.Time_stamp TS_
> END , VALIDATED_REASON , DECODE( SIGN(MB.Time_stamp - NVL
> (:b4, sysdate)), -1,-- Zeit vor Setup-Ende DECODE(
> SIGN(900 - MDT.Validated_Reason), 1, -- manuelle Down
> times DECODE(MDT.Validated_Reason, 0, 0, 1, 0, 1),
>
> 18,420,632 160 115,129.0 67.3 59.56 60.43
> 979285868
> begin MONITOR.GET_VALUES_ON_SHIFT_CHANGE(:1,:2,:3,:4, :5); end;
>

It looks as if the procedure may be the thing calling the query - and if so,
it calls it around 10 times per executions. But in the bad case

> 48,577,464 227 213,997.6 77.0 136.63 654.59
> 979285868
> begin MONITOR.GET_VALUES_ON_SHIFT_CHANGE(:1,:2,:3,:4, :5); end;
>
> 47,854,480 6,286 7,612.9 75.8 120.13 127.97
> 395610491
> SELECT MDT.ID , MB.Time_stamp TS_START , ME.Time_stamp TS_
> END , VALIDATED_REASON , DECODE( SIGN(MB.Time_stamp - NVL
> (:b4, sysdate)), -1,-- Zeit vor Setup-Ende DECODE(
> SIGN(900 - MDT.Validated_Reason), 1, -- manuelle Down
> times DECODE(MDT.Validated_Reason, 0, 0, 1, 0, 1),
>

Number of procedure executions is up by about 30%, but number of queries per call is now closer to 30. With a total workload up from 18M to 48M logical I/Os.

BUT - the "gets per exec" has dropped from 11,200 to 7,600 for the query. This is consistent with a change of plan introducing tablescan (or index fast
full scan) activity - fewer block gets to acquire the data, but a brute-force
approach which can lead to many more scattered reads.

I think you need to check execution plans for this query, then check why it seems to be called so many more times. (Possibly it's caught in the 'process
the first row' trap).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html