SQL Monitoring without MONITOR Hint
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.
Carlos Sierra's Tools and Tips 
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:
SQL>BEGIN
2 SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
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 /
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
*
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?
regards,
Mehdi LISSIR
Eddy
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