Using Real time SQL Monitor in command line!

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: 

1. Diagnostics and Tuning Pack license.
2. STATISTICS_LEVEL= 'TYPICAL' or 'ALL'

Methods to run Real time SQL Monitor: 

1. Oracle Enterprise Manager
2. SQL Developer
3. Command line. 

Conditions where SQL is monitored by default: 

1. When the SQL has consumed at least five seconds of the CPU or I/O time in a single execution
2. When the SQL execute in parallel
3. 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 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

DBMS_SQL_MONITOR package provides information about Real-Time SQL Monitoring and Real-Time Database Operation Monitoring whereas DBMS_SQLTUNE is a comprehensive package consisting of all SQL related tuning capabilities and is an interface for SQL tuning on demand. 

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_SQLTUNE (oracle.com)
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. 

Also, I have added the section of how to generate Active SQL monitor reports. 

Create Oracle database Flashback logs outside FRA!

 Many times, as a DBA we would be required to create database restore points so that if something messed up in the database be it due to a database upgrade or patching, application upgrade touching the database objects, etc., it would be easy to go back to the time when the operation was started without the need of restoring the database. 

To facilitate this easy feature, one would need to enable or create the restore point be it a normal restore point or a guaranteed restore point. I will always prefer the guaranteed restore point if I want to secure the database of unexpected mess as a roll back plan. The flashback logs of a normal restore point can be resued if there is a space pressure in the FRA or it is no longer needed to satisfy the flashback retention target and hence the recovery window might be limited. 

One downside of creating restore point is that Oracle logs another type of log file apart from the standard archive logs called the flashback logs. Until version 21c, if we enable restore point or to use flashback feature, Oracle will always use the Fast Recovery Area (FRA) space to create the flashback logs. FRA is a shared space for all the database restore and recovery related files such as archive log, control file, backup piece and also the flashback log. 

Below is an example of the FRA contents from a 19c database

You can see with the default settings, both archived logs and flashback log uses the same FRA and when there is a growth in flashback log, there is a possibility of database stalling due to insufficient space when either of the logs fills the FRA. 

In Oracle 23c, there are 2 new parameters introduced as below 

db_flashback_log_dest
db_flashback_log_dest_size

These 2 parameters define where the flashback logs are stored outside of the FRA making the management of flashback logs easier.

SQL> alter system set db_flashback_log_dest_size=4G scope=both;

System altered.

SQL> alter system set db_flashback_log_dest='/media/sf_Oracle/Linux-823' scope=both;

System altered.

SQL> sho parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest                string      /media/sf_Oracle/Linux-823
db_flashback_log_dest_size           big integer 4G
db_flashback_retention_target        integer     1440
SQL> 

Though we have enabled the new parameters to have a separate location for flashback logs, we still have to define FRA location and size before we start utilizing the new location for flashback logs. Otherwise, if you try to create restore point we will get the error as below. 

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE;
CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'FREEPDB1_RP'.
ORA-38786: Recovery area is not enabled.


SQL> 

Now, let's enable FRA by allocating space and location and then check the contents before and after restore point creation. 

SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

SQL> alter system set db_recovery_file_dest='/opt/oracle/FRA';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/FRA
db_recovery_file_dest_size           big integer 4G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

7 rows selected.

SQL> 

We can see that once the restore point is created, FLASHBACK LOG entry from v$flash_recovery_area_usage has disappeared. So how do we check the details of the flashback logs? We do have a new view GV$/V$FLASHBACK_LOG_DEST to check the details. 

SQL> select * from V$FLASHBACK_LOG_DEST;

NAME                                               SPACE_LIMIT SPACE_USED NUMBER_OF_FILES     CON_ID
-------------------------------------------------- ----------- ---------- --------------- ----------
/media/sf_Oracle/Linux-823                          4294967296  419430400               2          0

SQL> 

Okay, so what is the use case of having a location outside of FRA? 

We don't always create restore points (in turn flashback logs) and they are created as necessary. Most important times are before patching of application or database. FRA mostly is dedicated to a specific database and let's say we have multiple databases with varying work loads scheduled for patching or application upgrade involving multiple database. Managing FRA on each database could be cumbersome as few DB would easily fill up space but most database won't generate much logs. We can NFS mount a file system on multiple involved database servers and dedicate that location for flashback logs so that the space management can be done outside of the database easily as and when needed. 

From performance point of view, if we have a separate location (disk) for the flahsback logs, i/o performance can be improved as i/o will be distributed between archive logs and flashback logs as both has to be written for the database to function. 

References: 

OLTP and Core Database (oracle.com)

Happy Flashback logging...!!!