I need to research what caused a high load in our Oracle database some days ago. I wish to see a list of of high-load SQL statements. We don't use any Oracle tools like ADDM or AWR.

I tried to research AWR tables. Could someone give me any clue which criteria should I put in query?

  • What about turning on extended sql tracing? You don't use AWR/ADDM or you don't a license for them? Big difference.
    – Stringer
    Aug 27 '15 at 21:19

You might look at trying out Luca Canali's PerfSheet4 tool, which helps extract and visualize data from AWR tables in Excel. It comes with a video and should help you get started with some of the necessary data gathering and analysis for troubleshooting.

You probably want to start with Query: Top 3 wait events and CPU option from the sheet which according to the blog post does:

Extracts data from dba_hist_system_event and dba_hist_sysstat. This query selects the top 3 non-idle wait events and the CPU usage for each instance. It computes delta values and rates (delta value over delta time).

You can also look at the queries in the spreadsheet, and use those as examples for your own queries into the AWR tables, should you need something more customized to your particular problem. Hope that helps. =)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.