STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORACLEDB 3205663384 oracledb 1 9.2.0.1.0 NO GXFZDZJC Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 30-Oct-07 17:52:36 32 3.9
End Snap: 2 30-Oct-07 18:06:40 27 4.6
Elapsed: 14.07 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,008M Std Block Size: 8K
Shared Pool Size: 112M Log Buffer: 10,240KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 9,678.78 7,993.04
Logical reads: 25,874.58 21,368.05
Block changes: 20.67 17.07
Physical reads: 7,026.25 5,802.50
Physical writes: 0.81 0.67
User calls: 23.71 19.58
Parses: 15.31 12.64
Hard parses: 4.28 3.54
Sorts: 1.28 1.06
Logons: 0.03 0.02
Executes: 17.80 14.70
Transactions: 1.21 % Blocks changed per Read: 0.08 Recursive Call %: 56.86
Rollback per transaction %: 0.00 Rows per Sort: 214.88Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.94 Redo NoWait %: 100.00
Buffer Hit %: 72.85 In-memory Sort %: 100.00
Library Hit %: 88.78 Soft Parse %: 72.03
Execute to Parse %: 14.00 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 31.97 % Non-Parse CPU: 99.64 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.68 83.20
% SQL with executions>1: 26.98 19.66
% Memory for SQL w/exec>1: 18.82 16.21Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 520 42.83
db file sequential read 2,806,692 398 32.79
db file scattered read 1,231,927 289 23.78
log file sync 1,046 2 .20
log file parallel write 1,265 2 .19
-------------------------------------------------------------
Wait Events for DB: ORACLEDB Instance: oracledb Snaps: 1 -2
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 2,806,692 0 398 0 2,746.3
db file scattered read 1,231,927 0 289 0 1,205.4
log file sync 1,046 0 2 2 1.0
log file parallel write 1,265 1,085 2 2 1.2
buffer busy waits 13,745 0 2 0 13.4
latch free 23 20 0 9 0.0
control file parallel write 275 0 0 1 0.3
direct path write (lob) 436 0 0 0 0.4
control file sequential read 205 0 0 0 0.2
SQL*Net break/reset to clien 98 0 0 0 0.1
db file parallel write 58 29 0 0 0.1
direct path read (lob) 96 0 0 0 0.1
SQL*Net more data to client 375 0 0 0 0.4
LGWR wait for redo copy 19 0 0 0 0.0
SQL*Net message from client 10,723 0 10,286 959 10.5
virtual circuit status 28 28 820 29297 0.0
wakeup time manager 27 27 810 30000 0.0
SQL*Net more data from clien 2,056 0 0 0 2.0
SQL*Net message to client 10,718 0 0 0 10.5
-------------------------------------------------------------
Background Wait Events for DB: ORACLEDB Instance: oracledb Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 1,265 1,085 2 2 1.2
control file parallel write 275 0 0 1 0.3
control file sequential read 108 0 0 0 0.1
db file parallel write 58 29 0 0 0.1
LGWR wait for redo copy 19 0 0 0 0.0
rdbms ipc message 3,489 2,258 5,096 1461 3.4
smon timer 2 2 600 ###### 0.0
------------ ----------- ------------ -------- ----------- ------- ------------
ORACLEDB 3205663384 oracledb 1 9.2.0.1.0 NO GXFZDZJC Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 30-Oct-07 17:52:36 32 3.9
End Snap: 2 30-Oct-07 18:06:40 27 4.6
Elapsed: 14.07 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,008M Std Block Size: 8K
Shared Pool Size: 112M Log Buffer: 10,240KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 9,678.78 7,993.04
Logical reads: 25,874.58 21,368.05
Block changes: 20.67 17.07
Physical reads: 7,026.25 5,802.50
Physical writes: 0.81 0.67
User calls: 23.71 19.58
Parses: 15.31 12.64
Hard parses: 4.28 3.54
Sorts: 1.28 1.06
Logons: 0.03 0.02
Executes: 17.80 14.70
Transactions: 1.21 % Blocks changed per Read: 0.08 Recursive Call %: 56.86
Rollback per transaction %: 0.00 Rows per Sort: 214.88Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.94 Redo NoWait %: 100.00
Buffer Hit %: 72.85 In-memory Sort %: 100.00
Library Hit %: 88.78 Soft Parse %: 72.03
Execute to Parse %: 14.00 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 31.97 % Non-Parse CPU: 99.64 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.68 83.20
% SQL with executions>1: 26.98 19.66
% Memory for SQL w/exec>1: 18.82 16.21Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 520 42.83
db file sequential read 2,806,692 398 32.79
db file scattered read 1,231,927 289 23.78
log file sync 1,046 2 .20
log file parallel write 1,265 2 .19
-------------------------------------------------------------
Wait Events for DB: ORACLEDB Instance: oracledb Snaps: 1 -2
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 2,806,692 0 398 0 2,746.3
db file scattered read 1,231,927 0 289 0 1,205.4
log file sync 1,046 0 2 2 1.0
log file parallel write 1,265 1,085 2 2 1.2
buffer busy waits 13,745 0 2 0 13.4
latch free 23 20 0 9 0.0
control file parallel write 275 0 0 1 0.3
direct path write (lob) 436 0 0 0 0.4
control file sequential read 205 0 0 0 0.2
SQL*Net break/reset to clien 98 0 0 0 0.1
db file parallel write 58 29 0 0 0.1
direct path read (lob) 96 0 0 0 0.1
SQL*Net more data to client 375 0 0 0 0.4
LGWR wait for redo copy 19 0 0 0 0.0
SQL*Net message from client 10,723 0 10,286 959 10.5
virtual circuit status 28 28 820 29297 0.0
wakeup time manager 27 27 810 30000 0.0
SQL*Net more data from clien 2,056 0 0 0 2.0
SQL*Net message to client 10,718 0 0 0 10.5
-------------------------------------------------------------
Background Wait Events for DB: ORACLEDB Instance: oracledb Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 1,265 1,085 2 2 1.2
control file parallel write 275 0 0 1 0.3
control file sequential read 108 0 0 0 0.1
db file parallel write 58 29 0 0 0.1
LGWR wait for redo copy 19 0 0 0 0.0
rdbms ipc message 3,489 2,258 5,096 1461 3.4
smon timer 2 2 600 ###### 0.0
2.数据数据空间使用过大，也是造成IO忙的问题之一，通过对statstpack的分析发现其中有几个数据文件疑似处于热点数据文件， 此为数据分配不均衡而造成的数据IO忙。
3.Logical reads和Physical reads的比较发现，其中有27.15%的逻辑读导致了物理IO；
4.而平均每秒钟的Physical Writes仅为0.81个数据块；
5.每秒钟用户CALL次数较频繁为23.71
6.对于parses 和Hard parses应该越低越好，对于性能好的服务器，此项有可能达到0
7.每秒钟产生的排序次数；sorts:1.28
8.每秒钟产生的事务数：1.21，这个表明数据库的任务的繁重与否。
9.数据库在Buffer Hit%上的命中率不高， 通过修改db_cache_size增大可能改善此情况，
10.Libary Hit%表示在共享区上的命中率，此参数也不高可通过加大共享池(share_pool_cache,绑定参数，及cursor_sharing参数.
11. Soft Parse %: 72.03说明一点考虑要绑定同时SQL基本没有被重用。
12.db file sequential read数据过大表面在单个数据块上存在大量等待，而db file scattered read：该事件通常与全表扫描有关。请问接下来我应该如何对数据库进行优化？？？请大侠指导。谢谢。