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.
Even time you run a SQL, a specific execution plan must be created or reused. This execution plan is identified by the “sql_exec_id”…. But wait, this is still not all.
You may have run the same SQL multiple times. Maybe the first run was on a quite system and the second was during a batch window. Each execution timestamps the “sql_exec_start” in the v$sql_monitor table.
You can quickly see how one SQL statement could have multiple runs and plans. This can get messy and cumbersome to gather by hand. Enterprise manager allows you to look at individual runs and save the SQLMON file, but let’s say you have 20 executions with 3 different plans?
…clearly there has got to be a better way.
the birth of the getSQLMON script
I created a simple script to gather all SQLMON reports for a given portion of the SQL text. I basically searched the v$sql_text table for any SQLid’s that matched a specific pattern. It then creates a directory for this particular run and a subdirectory for every run that matches a given pattern.
$ ./getSQLMON.sh Usage: ./getSQLMON.sh <SQLPATTERN> $ ./getSQLMON.sh RTL19 SQL_ID SQL_EXEC_ID EPOCH SQLTXT DURATION_SECS ------------- ----------- ---------- -------------------- ------------- 8g97qvjxkdvq5 16777218 1439194608 select /*+ RTL19 */ 8 8g97qvjxkdvq5 16777216 1439194779 select /*+ RTL19 */ 3 8g97qvjxkdvq5 16777216 1439363758 select /*+ RTL19 */ 2 Gather SQLMON reports for these SQLids ALL done... Reports are in sqlmon_081215_1308 $ cd sqlmon_081215_1308 $ ls sqlid_8g97qvjxkdvq5_epoch_1439194608 sqlid_8g97qvjxkdvq5_epoch_1439363758 sqlid_8g97qvjxkdvq5_epoch_1439194779 sqlmon_summary_081215_1308.txt $ ls sqlid* sqlid_8g97qvjxkdvq5_epoch_1439194608: sqlmon_sqlid_8g97qvjxkdvq5_execid_16777218_epoch_1439194608_dt_081215_1308.html sqlmon_sqlid_8g97qvjxkdvq5_execid_16777218_epoch_1439194608_dt_081215_1308.txt sqlid_8g97qvjxkdvq5_epoch_1439194779: sqlmon_sqlid_8g97qvjxkdvq5_execid_16777216_epoch_1439194779_dt_081215_1308.html sqlmon_sqlid_8g97qvjxkdvq5_execid_16777216_epoch_1439194779_dt_081215_1308.txt sqlid_8g97qvjxkdvq5_epoch_1439363758: sqlmon_sqlid_8g97qvjxkdvq5_execid_16777216_epoch_1439363758_dt_081215_1308.html sqlmon_sqlid_8g97qvjxkdvq5_execid_16777216_epoch_1439363758_dt_081215_1308.txt
Once the tool has been run, the directory structure can be distributed for further analysis. This tool has been very helpful for me to mine SQLMON reports when running benchmarks or analyzing running various systems. I have placed the getSQLMON tool in GitHub.
Hopefully it will be of use to you as well!!