Query optimization or tuning is an interesting subject. Sometimes we would get complaints from application folks that their query is running slow in the timelines of running 4 hours against 1 or 2 hrs normal execution. During these times, we can't wait for the query to run to its completion only to see the query didn't run as expected.
How can we find whether the query is running as expected? One of the simple ways is to run the explain plan to see whether the plan has changed from the existing one and is the join methods and filters are applied as expected and also whether the cost of the operation is reduced. But the problem here is Oracle can totally predict a wrong plan (due to missing stats for example) or with the adaptive plans can change its plan during run time as well.
In order to know the details of the query execution we need to wait until the query completes. This is not welcome in all the situations as DBA can't wait for the query execution to complete before they decide on next course of investigation plan.
We can use DBMS_XPLAN.display_cursor or ?/rdbms/admin/utlxpls.sql or similar tools to get the explain plan of the query but for these to work to give the detailed statistics, the query should be run to its completion. Waiting for 2 or 3 hours just to check the execution details of the query seems not fun.
Starting version 11g, Oracle introduced a feature called Real time SQL Monitoring. This is such a cool and useful feature where the stats can be seen in real time even the query is currently running.
Requirements to use Real time SQL Monitor:
- Diagnostics and Tuning Pack license.
- STATISTICS_LEVEL= 'TYPICAL' or 'ALL'
Methods to run Real time SQL Monitor:
- Oracle Enterprise Manager
- SQL Developer
- Command line.
Conditions where SQL is monitored by default:
- When the SQL has consumed at least five seconds of the CPU or I/O time in a single execution
- When the SQL execute in parallel
- When the SQL has /*+ monitor */ hint
In this post, we are going to look into how to run Real time SQL Monitor using command line and view its results which will be useful to analyze the query execution
There are 2 packages via which sql can be monitored in real time.
DBMS_SQLTUNE
DBMS_SQL_MONITOR
DBMS_SQLTUNE is a package that deals with tuning SQL on demand and is available from Oracle 11g onwards. The package deals with SQL profiles, creating and executing tuning tasks, tracing SQL, monitor SQL and reporting monitored SQL, etc.. We will look how to report real time SQL monitor using DBMS_SQLTUNE with an example.
To report real time SQL, all we need is the sql_id along with the report type. By default, the report will be of text format but we have an option to get the report in nice and tidy html format. If the report needs to be generated only for a specific sql_plan_hash_value, then the value for this parameter can be provided.
Usage example:
DBMS_SQLTUNE
I'm now running a query with /*+monitor */ hint as this query takes less than 5 sec but I need to check the path the query has taken.
Once the query has run or if it is a long running query we can check the v$sql_monitor view to see if the query has been captured for real time monitoring. We can then use the dbms_sqltune.report_sql_monitor procedure to report the real time statistics of the query.
SQL> alter session set container=odb1;
Session altered.
SQL> set lines 200
SQL> select /*+ monitor */ * from oe.order_items join oe.orders on oe.order_items.order_id=oe.orders.order_id;
ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY ORDER_ID ORDER_DATE ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
---------- ------------ ---------- ---------- ---------- ---------- ------------------------------ -------- ----------- ------------ ----------- ------------ ------------
2354 1 3106 48 61 2354 14-JUL-08 05.18.23.234567 PM direct 104 0 46257 155
2354 2 3114 96.8 43 2354 14-JUL-08 05.18.23.234567 PM direct 104 0 46257 155
2354 3 3123 79 47 2354 14-JUL-08 05.18.23.234567 PM direct 104 0 46257 155
2354 4 3129 41 47 2354 14-JUL-08 05.18.23.234567 PM direct 104 0 46257 155
2354 5 3139 21 48 2354 14-JUL-08 05.18.23.234567 PM direct 104 0 46257 155
...
...
...
2458 2 3123 79 112 2458 16-AUG-07 02.34.12.234359 PM direct 101 0 78279.6 153
2458 3 3127 488.4 114 2458 16-AUG-07 02.34.12.234359 PM direct 101 0 78279.6 153
2458 4 3134 17 115 2458 16-AUG-07 02.34.12.234359 PM direct 101 0 78279.6 153
665 rows selected.
SQL>
SQL> SET LINESIZE 300
SQL> COLUMN sql_text FORMAT A100
SQL> SELECT sql_id, status, sql_text FROM v$sql_monitor where sql_text like '%monitor%';
SQL_ID STATUS SQL_TEXT
------------- ------------------- ----------------------------------------------------------------------------------------------------
abf24z16pukw9 DONE (ALL ROWS) select /*+ monitor */ * from oe.order_items join oe.orders on oe.order_items.order_id=oe.orders.orde
r_id
SQL> SET LONG 1000000
SQL> SET FEEDBACK OFF head off
SQL> spool monitor_sql.html
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'abf24z16pukw9',type=> 'HTML') AS report FROM dual;
o/p will contain lots of html contents
as the report is generated as html..
...
...
...
SQL> spool off
SQL>
We now have the report generated by name monitor_sql.html. We can view the report to see the real time execution plan statistics of the query. 
Since the query is now completed, we see the complete execution plan statistics and the completion of the query is 100%. This report is very useful when there is a long running query and if we need to investigate what's causing slowness.
To showcase this, I just inserted 3 fold data into sales table but didn't gather statistics and ran a query without any filters.
The first report generated shows below.
The first report generated shows below.
The query runs for a very long time and the subsequent reports shows the progress of the query until it completes. Here, we can find that the query expected 919k rows from the sales table but the actual rows returned is 4M which is 4 folds higher than the expected value and hence we can easily say that the statistics is not proper for the table.
DBMS_SQL_MONITOR
Generating real time sql monitoring report using DBMS_SQL_MONITOR is similar to using DBMS_SQLTUNE and the report is also generated same as this.
DBMS_SQL_MONITOR is introduced in 12c and we can now monitor SQL and PL/SQL calls containing SQL as well.
Usage will be as below
SQL> SET LONG 1000000 SQL> SET FEEDBACK OFF head off SQL> spool monitor_sql.html SQL> SELECT DBMS_SQL_MONITOR.report_sql_monitor(sql_id =>'1uhbtu4azdyqh',type=> 'HTML') AS report FROM dual; SQL> spool off SQL>Report generated will be similar to the one we saw before using DBMS_SQLTUNE with just the package name change.
While SQL monitoring report of type => HTML gives a well formatted html report to analyze the query execution, we can generate ACTIVE report which would provide much better and additional information such as optimizer environment, parallel activity section for parallel queries, etc. Active report is my favorite go to tool for query tuning.
So how would you generate the SQL monitor ACTIVE report? Use type => ACTIVE in the REPORT_SQL_MONITOR function as below.
SQL> SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 PAGESIZE 0 TRIM ON TRIMSPOOL ON ECHO OFF FEEDBACK OFF SQL> spool monitor_sql.html SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'abf24z16pukw9',type=> 'ACTIVE') AS report FROM dual; ... ... ... SQL> spool off
The report generated will be like below.
We can see that the report is kind of interactive where we can change the execution plan to graphical mode if one wishes to understand better and we can toggle between various options to check the complete picture of where the query is executed like the Optimizer Environment, etc. Timeline section will keep updating if we generate the report frequently while the query is running.
When the graphical mode is toggled, we get the easily understandable view of the execution plan
Hope this helps investigate the real time sql using command line.
References:
DBMS_SQL_MONITOR (oracle.com)
Monitoring SQL Statements with Real-Time SQL Monitoring (Doc ID 1380492.1)
Happy Tuning...!!!
P.S: I'm not able to generate the real time reports on the Database version 23 Free and have raised a community discussion. Let's wait to hear on this..
Update:
The community discussion has been answered by SQL Developer fame Jeff Smith.
DIAGNOSTIC+TUNING pack is by default disabled and we need to enable it to generate the reports.
DIAGNOSTIC+TUNING pack is by default disabled and we need to enable it to generate the reports.
Also, I have added the section of how to generate Active SQL monitor reports.
