Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQL Monitoring without MONITOR Hint

with 2 comments

I recently got this question:

<<<Is there a way that I can generate SQL MONITORING report for a particular SQL_ID ( This SQL is generated from application code so I can’t add “MONITOR”  hint) from command prompt ? If yes can you please help me through this ?>>>

Since this question is of general interest, I’d rather respond here:

As you know, SQL Monitoring starts automatically on a SQL that executes a PX plan, or when its Serial execution has consumed over 5 seconds on CPU or I/O.

If you want to force SQL Monitoring on a SQL statement, without modifying the SQL text itself, I suggest you create a SQL Patch for it. But before you do, please be aware that SQL Monitoring requires the Oracle Tuning Pack.

How to turn on SQL Monitoring for a SQL that executes Serial, takes less than 5 seconds, and without modifying the application that issues such SQL

Use SQL Patch with the MONITOR Hint. An easy way to do that is by using the free sqlpch.sql script provided as part of the cscripts (see right-hand side of this blog under Downloads).

To use sqlpch.sql script, pass as parameter #1 your SQL_ID and for parameter #2 pass “GATHER_PLAN_STATISTICS MONITOR” (without the double quotes).

This sqlpch.sql script will create a SQL Patch for your SQL, which will produce SQL Monitoring (and the collection of A-Rows) for every execution of your SQL.

Be aware there is some overhead involved, so after you are done with your analysis drop the SQL Patch.

Script sqlpch.sql shows the name of the SQL Patch it creates (look at its spool file), and it gives you the command to drop such SQL Patch.

For the actual analysis and diagnostics of your SQL (after you have executed it with SQL Patch in place) use free tool SQLd360.

And for more details about sqlpch.sql and other uses of this script please refer to this entry on my blog.


Written by Carlos Sierra

February 29, 2016 at 10:16 am

2 Responses

Subscribe to comments with RSS.

  1. Hi Carlos,
    Thank you for the script coe_gen_sql_patch.sql which fixes the patches, I noticed since version 12R2 this script comes out in error, I think because of the package SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH which is different in 12R2 compared to the previous version.
    There is the errors:
    3 sql_text => :sql_text,
    4 hint_text => ‘&&hint_text.’,
    5 name => ‘coe_&&sql_id.’,
    6 category => ‘DEFAULT’,
    7 description => ‘/*+ &&hint_text. */’
    8 );
    9 END;
    10 /
    ERROR at line 2:
    ORA-06550: line 2, column 3:
    PLS-00306: wrong number or types of arguments in call to ‘I_CREATE_PATCH’
    ORA-06550: line 2, column 3:
    PL/SQL: Statement ignored

    please have you a new version of this script?
    Mehdi LISSIR


    August 17, 2020 at 9:19 am

    • Please download new version of cs scripts just uploaded, and use cs_spch_create.sql instead.

      Carlos Sierra

      August 17, 2020 at 10:41 am

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: