| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Help me read my statspack report
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
Top 5 Timed Events
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)
 |
 |