Identifying root cause of high cpu load

oracleperformanceperformance-tuning

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?

Best Answer

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. =)

Related Question