Oracle wait events and Query
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?
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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.
Click Here to Expand Forum to Full Width