Home » RDBMS Server » Performance Tuning » Help required in analyzing AWR report (Oracle 10.2.0.3.0)
Help required in analyzing AWR report [message #553639] |
Mon, 07 May 2012 07:58 |
|
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello
I am trying to investigate 'why a particular sql statement stuck / took long long to execute' on couple of ocassions
Following are the major waits from the AWR on the two ocassions
Note : DB server has single db instance and 16 processors
First Ocassion - Snap time 59.30 min
------------------------------------
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Streams capture: waiting for archive log 3,326 42.72 1,941 584 0.12
read by other session 933,417 0.00 1,495 2 34.74
db file scattered read 305,611 0.00 1,179 4 11.37
db file sequential read 371,949 0.00 1,099 3 13.84
log file sequential read 44,297 0.00 879 20 1.65
Streams capture: waiting for subscribers to catch up 173 76.30 580 3355 0.01
SQL*Net more data to dblink 32,231 0.00 195 6 1.20
Streams AQ: qmn slave idle wait 6,317 0.19 16,828 2664 0.24
SQL*Net message from client 121,323 0.00 16,641 137 4.52
LogMiner: wakeup event for preparer 19,298 30.25 7,103 368 0.72
LogMiner: wakeup event for builder 239,065 1.90 7,093 30 8.90
wait for unread message on broadcast channel 101,322 5.69 6,430 63 3.77
LogMiner: client waiting for transaction 93,725 3.77 4,567 49 3.49
Streams AQ: waiting for messages in the queue 712 100.00 3,553 4990 0.03
Streams AQ: delete acknowledged messages 830 73.86 3,547 4274 0.03
virtual circuit status 118 100.00 3,505 29702 0.00
Streams AQ: qmn coordinator idle wait 2,223 35.54 3,487 1569 0.08
Streams AQ: deallocate messages from Streams Pool 988 30.67 802 812 0.04
SQL*Net message from dblink 25,497 0.00 684 27 0.95
LogMiner: wakeup event for reader 5,566 5.07 555 100 0.21
jobq slave wait 60 100.00 180 3000 0.00
SGA: MMAN sleep for component shrink 84 7.14 0 1 0.00
single-task message 3 0.00 0 17 0.00
class slave wait 16 0.00 0 0 0.00
Second Ocassion - Snap time 360.06 min
------------------------------------
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Streams capture: waiting for archive log 3,326 42.72 1,941 584 0.12
read by other session 933,417 0.00 1,495 2 34.74
db file scattered read 305,611 0.00 1,179 4 11.37
db file sequential read 371,949 0.00 1,099 3 13.84
log file sequential read 44,297 0.00 879 20 1.65
Streams capture: waiting for subscribers to catch up 173 76.30 580 3355 0.01
SQL*Net more data to dblink 32,231 0.00 195 6 1.20
Streams AQ: qmn slave idle wait 6,317 0.19 16,828 2664 0.24
SQL*Net message from client 121,323 0.00 16,641 137 4.52
LogMiner: wakeup event for preparer 19,298 30.25 7,103 368 0.72
LogMiner: wakeup event for builder 239,065 1.90 7,093 30 8.90
wait for unread message on broadcast channel 101,322 5.69 6,430 63 3.77
LogMiner: client waiting for transaction 93,725 3.77 4,567 49 3.49
Streams AQ: waiting for messages in the queue 712 100.00 3,553 4990 0.03
Streams AQ: delete acknowledged messages 830 73.86 3,547 4274 0.03
virtual circuit status 118 100.00 3,505 29702 0.00
Streams AQ: qmn coordinator idle wait 2,223 35.54 3,487 1569 0.08
Streams AQ: deallocate messages from Streams Pool 988 30.67 802 812 0.04
SQL*Net message from dblink 25,497 0.00 684 27 0.95
LogMiner: wakeup event for reader 5,566 5.07 555 100 0.21
jobq slave wait 60 100.00 180 3000 0.00
SGA: MMAN sleep for component shrink 84 7.14 0 1 0.00
single-task message 3 0.00 0 17 0.00
class slave wait 16 0.00 0 0 0.00
While I googled for the major waits listed above I could see most are mentioned as 'Idle Wait Events'
However in my case it is taking major time and it is difficult to ingore the waits
Could you please suggest on this?
Thanks and Regards
Orapratap
|
|
|
|
|
|
|
Re: Help required in analyzing AWR report [message #553751 is a reply to message #553750] |
Tue, 08 May 2012 05:32 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
He's saying that those stats don't necessarily have anything to do with the problem SQL.
Some of those waits are background ones and won't affect the SQL at all.
If you have general performance issues those stats can be useful to identify what might be the cause.
If it's a few specific statements then those stats are almost certainly useless and you need to look at the explain plan /tkprof instead.
|
|
|
Re: Help required in analyzing AWR report [message #553773 is a reply to message #553639] |
Tue, 08 May 2012 08:08 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Go to the sections of the AWR report headed "SQL ordered by elapsed time", "SQL ordered by reads", and so on. Find your statement, note the sql_id. Then use the awrsqrpt.sql script to generate reports for that particular sql_id, at times when it was running well and at times when it wasn't. You'll see the execution plan and the execution statistics, so check out any differences.
|
|
|
Goto Forum:
Current Time: Mon Jul 01 22:52:49 CDT 2024
|