Printable View
Hi All,
Couple of questions
(1) How do I relate a Wait event (reference Top 5 Timed Foreground Events in AWR report) to a query may be the culprit of this wait?
(2) How do I find the segments, datafile, block details affected by the above wait event?
(3) How do I find the query casuing the any above wait events in AWR?
Regards
Sumit
The top 5 is an overall listing, there could be more than one statement or activity contributing the amounts of whatever that are being reported.
You can look at the IO stats (tablespaces and datafiles) to see which ones are being frequently accessed (or have high, >7-10ms avg times). Then, what are the objects that live/have space in those files? Look at the top SQL in terms of resources, generally, a "bad" or resource intensive statement will be present in more than category. Click the link for the SQL ID to jump to the statement (in the report), and start from there.
Building on Stecal's comment...
Be sure your system is set for timed statistics... timed_statistics = true.
Also, when looking at your top 5 wait events be sure you know what they mean, idle wait events like "parallel query dequeue", etc may appear on the top 5 but you wouldn't worry about them. Focus on both I/O and Memory related ones.
There is no straigh forward answer to your question.
We've to use our intuition.
But silly things like DB file scattered read, just look top SQL read and do your stuff.
BUt most of the time they are not this simple.