getSQLMON… mining SQLMON reports from Oracle

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!! 

Advertisements

0 Responses to “getSQLMON… mining SQLMON reports from Oracle”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: