------------ ---------- ---------- ------ --------
direct path read 1,467 0 278 190 0.7
db file scattered read 1,483 0 152 103 0.7
buffer busy waits 1,441 0 146 102 0.7
control file parallel write 244 0 100 408 0.1
log file parallel write 508 439 85 168 0.2
db file parallel write 344 172 80 233 0.2
db file sequential read 319 0 23 71 0.1
control file sequential read 100 0 6 59 0.0
direct path write 8 0 0 25 0.0
latch free 23 22 0 1 0.0
LGWR wait for redo copy 1 0 0 0 0.0
rdbms ipc message 2,435 1,959 5,846 2401 1.1
smon timer 3 3 922 ###### 0.0
SQL*Net message from client 32 0 0 1 0.0
SQL*Net message to client 32 0 0 0 0.0
每秒redo 生产只有3.4kb,且
系统cpu， mem 都没有大的开销；
log file parallel write 平均等待168ms ；基本可以表明是系统io负载过高
direct path read 等待过高 正是由于io 负载过高而表征出来;
'wait time'= 49982.8604 cs
'Service Time' = 48414 cs
'Response Time' = 98396.8604 cs
--'Response Time' components:---
CPU time =49.2%
direct path read =26.7%
db file sequential read =7.3%
enqueue =6.9%
db file scattered read =3.9%
buffer busy waits =2.1%
对statspack 详细分析后发现，系统的服务时间占用了49.2%，而cpu的高开销，
也从一个侧面表明，应用存在大开销cpu 的操作，这类操作一般可能是排序，或者大量硬解析，
但是top event里并没有看到shared pool 相关的等待事件。
继续看下 SQL ordered by Gets
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
333,146 7 47,592.3 2.6 43.73 ######### 3804332362
select count(0) from (select LIXORA_QX.*,(SELECT YQLB FROM LIXORA_QX
YQ WHERE LIXORA_HLJ.SEQ_NUMBER = LIXORA_QX.DQYQXH) AS YQLB from T_S
B_QX where (1=1) order by C_DATE) --果然存在一个排序操作
建议：
1》sql 语句调整，减少排序；可以在c_date 字段上加索引；或者去掉order by
2》更换更高性能的存储
|