Physical reads: 8,115.82 8,040.10
Physical writes: 58.41 57.87[/COLOR]
User calls: 689.05 682.62
Parses: 163.76 162.23
Hard parses: 0.25 0.25
Sorts: 9.97 9.88
Logons: 3.09 3.06
Executes: 167.27 165.71
Transactions: 1.01
% Blocks changed per Read: 0.02 Recursive Call %: 3.74
Rollback per transaction %: 0.11 Rows per Sort: 1656.81
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.81 Redo NoWait %: 100.00
Buffer Hit %: 88.61 In-memory Sort %: 99.66
Library Hit %: 100.00 Soft Parse %: 99.85
Execute to Parse %: 2.10 Latch Hit %: 99.74
Parse CPU to Parse Elapsd %: 58.94 % Non-Parse CPU: 99.69
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 82.49 81.25
% SQL with executions>1: 59.74 59.22
% Memory for SQL w/exec>1: 62.31 62.24
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 11,979 39.52
buffer busy waits 243,458 7,505 24.76
db file sequential read 2,645,488 4,219 13.92
db file scattered read 2,405,437 3,470 11.45
latch free 24,311 1,319 4.35
-------------------------------------------------------------
----增加索引后的STATSPACK report:[/COLOR]
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 588 28-Oct-05 07:55:36 ####### .0
End Snap: 589 28-Oct-05 08:25:39 ####### .0
Elapsed: 30.05 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,152M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 9,908K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 6,282.58 5,820.91
Logical reads: 125,382.66 116,169.03
Block changes: 54.51 50.50
Physical reads: 1,963.06 1,818.81
Physical writes: 86.78 80.40
User calls: 915.43 848.16
Parses: 226.71 210.05
Hard parses: 0.32 0.29
Sorts: 5.03 4.66
Logons: 0.30 0.28
Executes: 240.94 223.24
Transactions: 1.08
% Blocks changed per Read: 0.04 Recursive Call %: 6.72
Rollback per transaction %: 0.05 Rows per Sort: 4362.82
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.86 Redo NoWait %: 100.00
Buffer Hit %: 98.49 In-memory Sort %: 98.14
Library Hit %: 99.91 Soft Parse %: 99.86
Execute to Parse %: 5.91 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: % Non-Parse CPU:
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.20 94.31
% SQL with executions>1: 67.01 66.79
% Memory for SQL w/exec>1: 64.57 64.44
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 1,339,126 20,393 56.91
db file scattered read 236,367 7,671 21.41
buffer busy waits 324,274 7,061 19.70
log file sync 2,073 252 .70
db file parallel read 153 229 .64
-------------------------------------------------------------