Re: Need Statspack help

> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 68.26 In-memory Sort %: 99.97
> Library Hit %: 99.84 Soft Parse %: 99.69
> Execute to Parse %: 44.71 Latch Hit %: 99.98
> Parse CPU to Parse Elapsd %: 78.52 % Non-Parse CPU: 97.17
>

How big is your Buffer cache..? (SQL> show sga)

> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> db file scattered read 76,596 763
> 83.81
> CPU time 79
> 8.67
> db file sequential read 10,528 44
> 4.80
> db file parallel write 452 6
> .63
> log file sequential read 103 6
> .61
> -------------------------------------------------------------

What is the value of "CPU used by this session" further down the Statspack report..?

You need to compare this "CPU used...." value with the total of the wait time in the table above (Top 5 Timed Events) to see where most of you DB time is being spent...

If CPU is the highest percentage then deal with the most inefficient SQL with respect to buffer hits (i.e. Logical reads). If the 'db file scattered read' and 'db file sequential reads' are the biggest percentage then concentrate on the SQL which causes the most Physical reads...

These 'inefficient' SQL statements will be included further down your statspack report.

Matt Received on Wed Oct 20 2004 - 02:29:23 CDT