CA Tuesday Tip: DB Performance Reports - The AWR report - #3: Misc Tips

Discussion created by Shawn_Moore Employee on Feb 3, 2011
Latest reply on Feb 4, 2011 by Shawn_Moore
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 2/1/2011

Here are some misc. tips regarding AWR reports:

1) Use the SQL ordered by... tables in the AWR report to find poorly performing queries that are still running.

This can be easily identified by looking at the Elap per Exec (s) or CPU per Exec (s) fields. If those fields are blank, then you've got a runaway query. Number of executions will also show up as zero.

CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
18,614 94,233 0 70.39 0ktshjvw4cbkr JDBC Connect Client BEGIN CMN_JOB_RATE_MATRIX_SP (...

2) The SQL Module will indicate, JDBC Connect Client, for queries that are originating from a JDBC Connection. These are 99.9% of the time from Clarity.

3) The Oracle initialization parameters (from init.ora) are at the bottom of the file. This is really useful, if you need to know the Oracle version, various nls settings and more!

4) I suggest taking an hour snap shot (30 minutes before and 30 minutes after) for isolated performace problems. (the system was slow around 10:16 am). For overall performance problems it may be beneficial to cover 4 to 8 hours.