Re: Any Comments - StatsPack Report

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 5 Apr 2007 05:42:39 -0700
Message-ID: <1175776959.409571.45250@q75g2000hsh.googlegroups.com>

On Apr 5, 1:51 am, ronald.bra..._at_gmail.com wrote:
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster
> Host
> ------------ ----------- ------------ -------- ----------- -------
> ------------
> PRODDWDB 2881301062 proddwdb 1 9.2.0.7.0 NO
> dcdwdb01
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> --------- ------------------ -------- ---------
> -------------------
> Begin Snap: 4375 02-Apr-07 14:00:02 26 4,277.3
> End Snap: 4376 02-Apr-07 15:00:05 28 3,977.3
> Elapsed: 60.05 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 512M Std Block Size:
> 8K
> Shared Pool Size: 304M Log Buffer:
> 102,400K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 2,213.36
> 1,702.91
> Logical reads: 475.92
> 366.16
> Block changes: 18.70
> 14.39
> Physical reads: 1,080.71
> 831.48
> Physical writes: 865.00
> 665.51
> User calls: 9.03
> 6.95
> Parses: 1.12
> 0.86
> Hard parses: 0.00
> 0.00
> Sorts: 1.24
> 0.95
> Logons: 0.01
> 0.01
> Executes: 4.83
> 3.72
> Transactions: 1.30
>
> % Blocks changed per Read: 3.93 Recursive Call %: 49.96
> Rollback per transaction %: 0.06 Rows per Sort: ########
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 45.10 In-memory Sort %: 73.43
> Library Hit %: 99.65 Soft Parse %: 99.55
> Execute to Parse %: 76.86 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 12.10 % Non-Parse CPU: 99.52
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 87.74 87.42
> % SQL with executions>1: 74.94 73.86
> % Memory for SQL w/exec>1: 49.10 49.06
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> db file sequential read 231,624
> 97 42.67
> CPU time
> 71 31.11
> db file scattered read 111,197
> 28 12.43
> SQL*Net more data to client 488,425
> 12 5.43
> log file parallel write 4,802
> 8 3.65

(snip)

What are we supposed to see from Statspack report?

Interesting things that I found:
Buffer cache is 512MB, log buffer is a little less than 100MB, redo is 1,703 bytes per transaction - something doesn't look right here. Parse CPU to parse elapsed time is 12%
26.5% of the sorts are sent to the temp tablespace 97 seconds spent reading indexes, yet elapsed time is 42.67 seconds - parallel?
28 seconds spent reading table rows, yet elapsed time is 12.43 seconds - parallel?
CPU time is 31 seconds
1.5 million waits on direct path read
12,550 seconds waiting for another request from a session - how many sessions connected?
Number of parse calls appears to be the same as the number of execution calls.

Statistic                                      Total per Second per
Trans
Cached Commit SCN referenced                 137,843 38.3
29.4
DBWR buffers scanned                          89,667 24.9
19.2
DBWR free buffers found                       71,579 19.9
15.3
DBWR summed scan depth                        89,667 24.9
19.2
SQL*Net roundtrips to/from client             32,466 9.0          6.9
db block changes                              67,384 18.7
14.4
db block gets                                601,120 166.8
128.4
no work - consistent read gets               970,939 269.5
207.3
physical reads                             3,893,806 1,080.7
831.5
physical reads direct                      2,952,361 819.4
630.4
physical writes                            3,116,587 865.0
665.5
physical writes direct                     3,081,221 855.2
658.0
physical writes non checkpoint             3,116,346 864.9
665.5
redo size                                  7,974,736 2,213.4
1,702.9
sorts (disk)                                   1,183 0.3          0.3
sorts (memory)                                 3,270 0.9          0.7
sorts (rows)                             918,067,682 254,806.5
196,042.6
table fetch by rowid                       1,382,832 383.8
295.3
table scan rows gotten                    33,900,640 9,409.0
7,239.1
user commits                                   4,680 1.3          1.0


                 Av      Av     Av                    Av
Buffer
Av Buf   Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits

Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------

TEMP 2,951,224 819 0.0 1.0 3,080,877 855 0 0.0

All of the tablespaces appear to be on the same spindle or the same RAID array - this may be OK if you are running a wide RAID 10 array, but probably is not OK if you have a single spindle, a RAID 1 array, or RAID 5 array.

It looks like the SORT_AREA_SIZE may be at the default of 64KB.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Apr 05 2007 - 07:42:39 CDT