As an Oracle performance engineer, I often work with customers on benchmarks and diagnosis of various issues. Now, everyone can gather an AWR report and send it my way, but that is just the tip of the iceberg. AWR reports are great for summarizing activity at a high level and can even find a SQL that is taking longer than expected. So, once we have identified a poor performing SQL, how do we fix it?
…that is where SQLMON comes into the picture.
gathering sqlmon reports
The power of the SQLMON report is very well documented. It provides an insight into EXACTLY what was happening when that query was running on the system. It documents the run time, SQL plan, CPU activity, IO activity, Offload, etc… The report can be gathered by running the “dbms_sqltune.report_sql_monitor” utility to extract a report. Each SQL in Oracle maps to a specific “SQL_ID” as this is really just a hash of the sql text….But, that is not all.