Re: Help me read my statspack report

On Dec 14, 10:05 am, emdproduct..._at_hotmail.com wrote:
> Our system is slow as hell at 10-11 time.
>
> How is my report, could somebody help me to diasnotic the problem?
> Thanks very much for your help.
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> --------- ------------------ -------- ---------
> -------------------
> Begin Snap: 25 13-Dec-06 10:00:03 94 20.0
> End Snap: 26 13-Dec-06 11:00:06 112 20.1
> Elapsed: 60.05 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 344M Std Block Size:
> 8K
> Shared Pool Size: 352M Log Buffer:
> 1,024K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 21,327.92
> 4,135.43
> Logical reads: 22,301.97
> 4,324.29
> Block changes: 167.93
> 32.56
> Physical reads: 601.19
> 116.57
> Physical writes: 8.31
> 1.61
> User calls: 191.58
> 37.15
> Parses: 78.20
> 15.16
> Hard parses: 15.28
> 2.96
> Sorts: 23.45
> 4.55
> Logons: 0.23
> 0.04
> Executes: 334.39
> 64.84
> Transactions: 5.16
>
> % Blocks changed per Read: 0.75 Recursive Call %: 65.29
> Rollback per transaction %: 4.27 Rows per Sort: 4280.83
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 97.33 In-memory Sort %: 100.00
> Library Hit %: 95.32 Soft Parse %: 80.46
> Execute to Parse %: 76.61 Latch Hit %: 99.49
> Parse CPU to Parse Elapsd %: 97.32 % Non-Parse CPU: 92.36
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 94.04 94.02
> % SQL with executions>1: 28.31 26.99
> % Memory for SQL w/exec>1: 26.11 25.70
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 5,177
> 84.22
> db file scattered read 182,425 595
> 9.69
> db file sequential read 104,046 212
> 3.45
> log file sync 18,224 77
> 1.25
> latch free 2,531 20
> .32
> -------------------------------------------------------------
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> CPU used by this session 517,708 143.7
> 27.9
> CPU used when call started 517,574 143.7
> 27.9
> CR blocks created 2,461 0.7
> 0.1
> DBWR buffers scanned 9,577 2.7
> 0.5
> DBWR checkpoint buffers written 7,706 2.1
> 0.4
> DBWR checkpoints 2 0.0
> 0.0
> DBWR free buffers found 9,560 2.7
> 0.5
> DBWR lru scans 8 0.0
> 0.0
> DBWR make free requests 8 0.0
> 0.0
> DBWR summed scan depth 9,577 2.7
> 0.5
> DBWR transaction table writes 20 0.0
> 0.0
> DBWR undo block writes 2,700 0.8
> 0.2
> SQL*Net roundtrips to/from client 475,115 131.9
> 25.6
> SQL*Net roundtrips to/from dblink 0 0.0
> 0.0
> active txn count during cleanout 1,034 0.3
> 0.1
> background checkpoints completed 1 0.0
> 0.0
> background checkpoints started 2 0.0
> 0.0
> background timeouts 4,389 1.2
> 0.2
> branch node splits 1 0.0
> 0.0
> buffer is not pinned count 65,452,475 18,166.1
> 3,522.4
> buffer is pinned count 125,721,967 34,893.7
> 6,765.8
> index fetch by key 113,983,237 31,635.7
> 6,134.1
> index scans kdiixs1 4,294,754 1,192.0
> 231.1
> session logical reads 80,354,005 22,302.0
> 4,324.3
> session pga memory 80,554,320 22,357.6
> 4,335.1
> session pga memory max 240,183,028 66,662.0
> 12,925.6
> session uga memory 4,377,641,540 1,214,999.0
> 235,585.1
> session uga memory max 885,522,568 245,773.7
> 47,654.9
> shared hash latch upgrades - no w 4,020,072 1,115.8
> 216.3
> shared hash latch upgrades - wait 1,830 0.5
> 0.1
> sorts (memory) 84,508 23.5
> 4.6
> sorts (rows) 361,764,006 100,406.3
> 19,468.5
> switch current to new buffer 23,142 6.4
> 1.3
> table fetch by rowid 5,500,640 1,526.7
> 296.0
> table fetch continued row 517,704 143.7
> 27.9
> table scan blocks gotten 61,677,018 17,118.2
> 3,319.2
> table scan rows gotten 8,963,725,778 2,487,850.6
> 482,387.6
> table scans (long tables) 2,592 0.7
> 0.1
> table scans (short tables) 2,231,741 619.4
> 120.1
>
> Tablespace
> ------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> USERS
> 240,076 67 2.5 7.5 225 0 661
> 1.4
> DOC
> 22,986 6 3.6 8.8 267 0 0
> 0.0
> ADT
> 9,981 3 4.3 8.1 1,470 0 4
> 0.0
> FCB
> 8,275 2 4.2 7.6 118 0 3
> 0.0
> DOCIDX
> 2,419 1 5.3 1.0 1,481 0 2
> 0.0
> TEMP
> 1,351 0 4.2 15.0 1,479 0 0
> 0.0
> UNDOTBS1
> 1 0 20.0 1.0 2,723 1 10
> 0.0
> FCBIDX
> 419 0 21.3 1.0 453 0 81
> 2.2
> PAGEIDX
> 407 0 16.3 1.0 359 0 0
> 0.0

You are starving the CPU (see top 5 events, always start there), by issuing way too much sorts (see 'sort (rows)'), too many chained rows (see 'table fetch continued row') and way way too much full table scans (see 'tablescans (short tables)' and 'tablescans (long tables)' You must have inefficient and untuned sql. You can track down the SQL starving your system, by running statspack(5).

-- 
Sybrand Bakker
Senior Oracle DBA