Re: Help with statspack report comparison

On Sep 23, 7:16 am, "Martin T." <0xCDCDC..._at_gmx.at> wrote:
> 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.
>
> It would be great if you could take a quick look at the infos below and
> just throw a few suggestions at me what might get me on the right track
> to find the problem and a solution for it.
>
> Thanks!
> - Martin

(Snipped statistics)

The single 7200 RPM (likely IDE) drive may be contributing to some of the difference in performance. A 27GB 7200RPM hard drive with 1MB onboard cache will have different performance characteristics than a 500GB 7200RPM hard drive with 16MB onboard cache and native command queuing. Make certain that this is not contributing to the difference in performance. The strategy of operating system level caching of files differs between Windows XP and 2003 - you may want to check the setting on Windows 2003 so that Windows 2003 minimizes the amount of memory used for operating system level caching of files.

It is quite likely that the 3.4GHz P4 server running Windows XP is using faster memory than the 2.13 GHz Xeon server running Windows 2003. The logical IO usage seems high per execution, so memory speed might be part of the problem.

Are the init.ora (or spfile) parameters identical for the two servers?

I can't see much in the Statspack reports, but things that seem to stand out:
XP: Parse CPU to Parse Elapsd %: 92.59
2003:Parse CPU to Parse Elapsd %: 27.09
2003 is doing *something* else when parsing SQL statements.

XP: db file scattered read: 31,453 requests, 0.003 s per request 2003:db file scattered read: 262,952 requests, 0.033 s per request 2003 is apparently 10 times slower on average at multi-block reads, such as full tablescans, and is performing 8 times as many requests.

XP: db file sequential read: 51,028 requests, 0.001 s per request 2003:db file sequential read: 47,490 requests, 0.031 s per request 2003 is apparently 30 times slower on average at single-block reads, such as index accesses, and is performing roughly the same number of requests.

XP: direct path read: did not make it into the top 5 wait events 2003:direct path read: 10,995 requests totaling 295 seconds 2003 is possibly sorting to disk, check sort_area_size

One of the SQL statements seems to stand out: begin MONITOR.GET_VALUES_ON_SHIFT_CHANGE(:1,:2,:3,:4, :5); end; XP: 115,128.95 buffer gets per execution, 160 executions, 0.372 seconds of CPU per execution, CPU time is close to execution time 2003:213,997.64 buffer gets per execution, 227 executions, 0.60 seconds of CPU per execution, CPU time is 20.87% of elapsed time

I would suggest a more focused approach. Pick a session that is executing slowly, and start a 10046 trace at level 12. Pick a session running the same application/module against the XP database, and start a 10046 trace at level 12. Perform the same operations in each of the sessions, then examine the 10046 trace files to see why there are so many logical reads (buffer gets) per execution, and why the CPU time is 1/5 of the elapsed time on the above query - it is likely partially caused by the slower disk access speed.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Sep 23 2007 - 09:15:28 CDT