求高手分析一个生产数据库的awr报告。
本帖最后由 pangxx 于 2012-05-28 17:13 编辑请哪位高手大哥帮我分析一下一个生产数据库的 awr报告。看看哪有问题。我们下午3点到4点系统非常慢。
（附件 的文件把 .gz删掉就是一个 html的报告。不是真正的gz压缩包）
部分日志：
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
WMSPRD 3825535826 wmsprd 1 10.2.0.4.0NOHPDC-PRD
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 13708 28-May-12 15:00:24 155 100.2
End Snap: 13709 28-May-12 16:00:07 152 97.5
Elapsed: 59.72 (mins)
DB Time: 256.81 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 2,080M 1,824MStd Block Size: 8K
Shared Pool Size: 1,968M 2,224M Log Buffer: 14,340K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 86,476.97 3,932.81
Logical reads: 376,174.40 17,107.69
Block changes: 1,168.31 53.13
Physical reads: 25,512.22 1,160.25
Physical writes: 16.61 0.76
User calls: 409.67 18.63
Parses: 236.66 10.76
Hard parses: 4.65 0.21
Sorts: 191.96 8.73
Logons: 0.17 0.01
Executes: 1,819.95 82.77
Transactions: 21.99
% Blocks changed per Read: 0.31 Recursive Call %: 91.62
Rollback per transaction %: 7.09 Rows per Sort: 20.76
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.78 Redo NoWait %:100.00
BufferHit %: 93.24 In-memory Sort %:100.00
Library Hit %: 99.54 Soft Parse %: 98.04
Execute to Parse %: 87.00 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 83.05 % Non-Parse CPU: 98.16
Shared Pool Statistics Begin End
------------
Memory Usage %: 55.23 63.24
% SQL with executions>1: 49.33 63.55
% Memory for SQL w/exec>1: 44.27 52.63
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 7,024 45.6
db file scattered read 12,521,993 2,029 0 13.2 User I/O
db file sequential read 1,676,535 1,306 1 8.5 User I/O
log file sync 72,926 1,183 16 7.7 Commit
log file parallel write 64,044 1,134 18 7.4 System I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> Total time in database user-calls (DB Time): 15408.4s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 10,536.5 68.4
DB CPU 7,023.6 45.6
PL/SQL execution elapsed time 245.3 1.6
parse time elapsed 231.4 1.5
hard parse elapsed time 202.4 1.3
PL/SQL compilation elapsed time 11.8 .1
failed parse elapsed time 5.5 .0
hard parse (sharing criteria) elapsed time 3.7 .0
connection management call elapsed time 3.4 .0
hard parse (bind mismatch) elapsed time 3.0 .0
repeated bind elapsed time 1.4 .0
sequence load elapsed time 0.8 .0
DB time 15,408.4 N/A
background elapsed time 1,893.2 N/A
background cpu time 425.2 N/A
-------------------------------------------------------------
Wait Class DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> 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
Avg
%Time Total Wait wait Waits
Wait Class Waits-outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O 17,317,275 .0 3,942 0 219.8
Commit 72,926 .2 1,183 16 0.9
System I/O 67,209 .0 1,182 18 0.9
Application 196 15.3 125 637 0.0
Other 1,015 12.3 124 122 0.0
Configuration 22 27.3 6 279 0.0
Concurrency 24,170 .0 2 0 0.3
Network 1,323,148 .0 2 0 16.8
-------------------------------------------------------------
Wait Events DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> 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
%TimeTotal Wait wait Waits
Event Waits-outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file scattered read 12,521,993 .0 2,029 0 158.9
db file sequential read 1,676,535 .0 1,306 1 21.3
log file sync 72,926 .2 1,183 16 0.9
log file parallel write 64,044 .0 1,134 18 0.8
read by other session 2,974,401 .0 577 0 37.8
enq: TX - row lock contentio 75 40.0 124 1658 0.0
buffer exterminate 142 86.6 124 871 0.0
control file parallel write 1,209 .0 46 38 0.0
db file parallel read 13,163 .0 31 2 0.2
write complete waits 6100.0 6 976 0.0
control file sequential read 1,940 .0 2 1 0.0
SQL*Net message to client 1,265,656 .0 1 0 16.1
latch: cache buffers chains 23,383 .0 1 0 0.3
enq: TX - index contention 7 .0 1 90 0.0
SQL*Net break/reset to clien 121 .0 1 5 0.0
latch free 516 .0 0 1 0.0
SQL*Net more data to client 52,376 .0 0 0 0.7
direct path read 129,968 .0 0 0 1.6
log file switch completion 5 .0 0 38 0.0
SQL*Net more data from clien 5,116 .0 0 0 0.1
os thread startup 4 .0 0 36 0.0
latch: shared pool 84 .0 0 1 0.0
enq: HW - contention 8 .0 0 10 0.0
latch: cache buffers lru cha 127 .0 0 1 0.0
cursor: pin S wait on X 5 80.0 0 9 0.0
buffer busy waits 430 .0 0 0 0.0
log file single write 8 .0 0 5 0.0
log file sequential read 8 .0 0 3 0.0
LGWR wait for redo copy 144 .0 0 0 0.0
latch: In memory undo latch 170 .0 0 0 0.0
latch: object queue header o 38 .0 0 0 0.0
latch: library cache 72 .0 0 0 0.0
direct path write temp 364 .0 0 0 0.0
wait list latch free 1 .0 0 8 0.0
rdbms ipc reply 4 .0 0 1 0.0
direct path write 493 .0 0 0 0.0
enq: JS - queue lock 2 .0 0 1 0.0
latch: redo allocation 30 .0 0 0 0.0
latch: library cache pin 10 .0 0 0 0.0
direct path read temp 358 .0 0 0 0.0
latch: session allocation 8 .0 0 0 0.0
enq: SQ - contention 3 .0 0 0 0.0
latch: row cache objects 2 .0 0 0 0.0
latch: library cache lock 1 .0 0 0 0.0
buffer deadlock 2100.0 0 0 0.0
library cache load lock 2 .0 0 0 0.0
cursor: pin S 1 .0 0 0 0.0
SQL*Net message from client 1,265,657 .0 164,212 130 16.1
Streams AQ: qmn slave idle w 128 .0 3,487 27243 0.0
Streams AQ: qmn coordinator 256 50.0 3,487 13621 0.0
PL/SQL lock timer 651 98.6 627 963 0.0
Streams AQ: waiting for time 1100.0 195195191 0.0
jobq slave wait 40100.0 118 2951 0.0
SGA: MMAN sleep for componen 1,445 95.7 20 14 0.0
JS external job 6 .0 4 658 0.0
class slave wait 1 .0 0 0 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> ordered by wait time desc, waits desc (idle events last)
Avg
%TimeTotal Wait wait Waits
Event Waits-outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file parallel write 64,042 .0 1,134 18 0.8
control file parallel write 1,209 .0 46 38 0.0
db file sequential read 2,041 .0 33 16 0.0
db file scattered read 153 .0 1 10 0.0
os thread startup 4 .0 0 36 0.0
control file sequential read 1,229 .0 0 0 0.0
events in waitclass Other 220 .0 0 0 0.0
log file single write 8 .0 0 5 0.0
log file sequential read 8 .0 0 3 0.0
latch: shared pool 1 .0 0 0 0.0
direct path read 22 .0 0 0 0.0
latch: library cache 1 .0 0 0 0.0
buffer busy waits 1 .0 0 0 0.0
direct path write 22 .0 0 0 0.0
rdbms ipc message 74,708 17.8 35,434 474 0.9
Streams AQ: qmn slave idle w 128 .0 3,487 27243 0.0
Streams AQ: qmn coordinator 256 50.0 3,487 13621 0.0
smon timer 57 1.8 3,415 59909 0.0
pmon timer 156,643 .7 3,068 20 2.0
Streams AQ: waiting for time 1100.0 195195191 0.0
SGA: MMAN sleep for componen 1,445 95.7 20 14 0.0
-------------------------------------------------------------
Operating System Statistics DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
Statistic Total
-------------------------------- --------------------
BUSY_TIME 783,582
IDLE_TIME 7,812,847
IOWAIT_TIME 195,007
NICE_TIME 0
SYS_TIME 156,702
USER_TIME 625,367
LOAD 3
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 16,420,700,160
NUM_CPUS 24
NUM_CPU_SOCKETS 24
-------------------------------------------------------------
Service Statistics DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 14,398.6 6,697.8 ########## ##########
wmsprd 1,010.2 326.16,816,815 ##########
SYS$BACKGROUND 0.0 0.0 4,799 101,456
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes:User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)
Service Name
----------------------------------------------------------------
User I/OUser I/OConcurcyConcurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
15891331 338816 24130 176 0 0 1288138 114
wmsprd
1422133 51389 33 0 0 0 33376 37
SYS$BACKGROUND
3827 4042 8 14 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: WMSPRD/wmsprdSnaps: 13708-13709
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Elapsed CPU Elap per% Total
Time (s) Time (s)Executions Exec (s)DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
3,325 3,273 56 59.4 21.6 dyhvh1j2uqjkn
本帖最后由 bxuan 于 2012-07-03 17:10 编辑
楼主可以再用ADDM工具生成一个分析报告，看看是不是某个sql语句导致了大量的磁盘IO
有一条sql语句，SQL Id：60agtq91c80j8 这条语句执行了23次，每次需要耗费47.21秒来执行。。
页:
[1]