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 Go to next message
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 #553641 is a reply to message #553639] Mon, 07 May 2012 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am trying to investigate 'why a particular sql statement stuck / took long long to execute' on couple of ocassions


You will not find that in what you posted.

Regards
Michel
Re: Help required in analyzing AWR report [message #553642 is a reply to message #553639] Mon, 07 May 2012 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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


I think you don't understand what "Idle waits" are.
These waits are waits of kind "I am waiting for someone to get me some work to do".

Regards
Michel
Re: Help required in analyzing AWR report [message #553643 is a reply to message #553639] Mon, 07 May 2012 08:11 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Thanks for the quick reply

1) What should be my starting point for such post execution analysis? (with no tkprof report)

2) In this particular case I have nothing else but only 2 backdated AWR reports. Where I shall look in this case? Aother section in these reports?

Possible that I may not get to root of the problem but can I at least narrow down the possible area which might have caused this?

Thanks and Regards
Orapratap
Re: Help required in analyzing AWR report [message #553750 is a reply to message #553643] Tue, 08 May 2012 05:26 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Could you please suggest on the above?

Do you want to say AWR is not useful in case where sql is already executed in the past?

If not, where shall I look into in the existing AWR report?

I understood what you have commented on "idle waits" and can imagine it in the context "SQL*Net message from client"

But in our case much waits are related to "archivelogs" and "streams" and these waits are the major ones

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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: unexplained performance degradation of PL/SQL procedures
Next Topic: Performance issue in query
Goto Forum:
  


Current Time: Thu Mar 28 10:38:28 CDT 2024