Oracle wait events and Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Oracle wait events and Query

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Oracle wait events and Query

    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
    sumit

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width