This article presents new feature of 11g Real time sql monitoring.

Real time sql monitoring introduced in Oracle 11G enables to monitor performance of SQL statements while they are running. Statistics about monitored queries are visible in new views.

Requirements to monitor a query by database

Oracle will monitor a query if following requirements are fulfilled:

• following instance parameters are set

STATISTIC_LEVEL=TYPICAL or STATISTIC_LEVEL=ALL
CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING

• the query is started in parallel or
• it has consumed at least five seconds of the CPU or I/O time in a single execution or
• you are using hint MONITOR

  select /*+ MONITOR */ from

You can block monitoring for a query if you specify hint NO_MONITOR

select /*+ NO_MONITOR */ from

Views for real time monitoring

New views are collecting statistics about monitored queries:

V$SQL_MONITOR – here you can see informations like elapsed time, CPU time, number of read and writes, I/O wait time and many others. Statistics are available at least one minute after the query ends. It’s refreshed in almost real time once a second.

V$SQL_PLAN_MONITOR – here you can see very detailed statistics for each step in explain plan used by the query and they are as well updated once a second.

Both above views plus old V$SQL, V$SQL_PLAN, V$SESSION, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS can give perfect information about execution of your query.

Enterprise Manager 11g and sql monitoring

You can access real time monitoring feature in Enterprise Manager Database Control. You can find link “SQL Monitoring” on “Performance” tab of Enterprise Manager.

once you click the link you should see queries which Oracle is monitoring. In my case I can see only one on my database 🙂 well my database is sleeping well right now.

If you click on the statement you will get more information like:

– duration of this query

– full explain plan (standard form or graphical form)

– how database time was consumed  per CPU,I/O etc

– how much time was consumed by PL/SQL, Java

– how much buffer gets was executed

– how many I/O request was executed

– how many I/O bytes was used per read/write

– full statistics for each step in explain plan

– usage of parallel processes

– statistics per wait events

– CPU usage across time

– I/O request across time

– I/O throughput across time

– PGA usage across time

– Temp usage across time

By clicking “Report” button you can generate very simple report

or you can save all above pages by clicking “Save”. Saving all above is very usefully in case you want to document something from database or you want send some details to somebody else.

From main page you can also drill down to session which executed this query or historical activity of the query.

DBMS_SQLTUNE and sql monitoring

In case you don’t have access to Enterprise Manager you can generate such nice reports using function REPORT_SQL_MONITOR from package DBMS_SQLTUNE. The function has got many parameters and can produce very detail report(you can turn off some sections if you want) in many formats like text, simple HTML, active HTML, XML.

DBMS_SQLTUNE.REPORT_SQL_MONITOR
( sql_id IN VARCHAR2 DEFAULT NULL, 
  session_id IN NUMBER DEFAULT NULL, 
  session_serial IN NUMBER DEFAULT NULL, 
  sql_exec_start IN DATE DEFAULT NULL, 
  sql_exec_id IN NUMBER DEFAULT NULL, 
  inst_id IN NUMBER DEFAULT NULL, 
  start_time_filter IN DATE DEFAULT NULL, 
  end_time_filter IN DATE DEFAULT NULL, 
  instance_id_filter IN NUMBER DEFAULT NULL, 
  parallel_filter IN VARCHAR2 DEFAULT NULL, 
  plan_line_filter IN NUMBER DEFAULT NULL, 
  event_detail IN VARCHAR2 DEFAULT 'YES', 
  bucket_max_count IN NUMBER DEFAULT 128, 
  bucket_interval IN NUMBER DEFAULT NULL, 
  base_path IN VARCHAR2 DEFAULT NULL, 
  last_refresh_time IN DATE DEFAULT NULL, 
  report_level IN VARCHAR2 DEFAULT 'TYPICAL', 
  type IN VARCHAR2 DEFAULT 'TEXT', 
  sql_plan_hash_value IN NUMBER DEFAULT NULL) 
RETURN CLOB;

simple example

let’s prepare some data

create table test_tbl
(id number);

begin
  for i in 1..100
  loop
    insert into test_tbl values(i);
  end loop;
  commit;
end;
/

let’s run query to monitor

set timing on
select /*+ MONITOR */ count(*) cnt
from test_tbl, test_tbl, test_tbl, test_tbl;
  CNT
----------
 100000000 

Elapsed: 00:00:04.662

you can read details about it in V$SQL_MONITOR

select key, sql_id, status 
from v$sql_monitor 
where sql_text='select /*+ MONITOR */ count(*) cnt 
from test_tbl, test_tbl, test_tbl, test_tbl';

KEY                        SQL_ID        STATUS            
-------------------------- ------------- -------------------
657129996295               9fvrwtjugnkup DONE (ALL ROWS)     

Elapsed: 00:00:00.016

and generate your report as TEXT

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'TEXT')
from dual;

SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR */ count(*) cnt from test_tbl, test_tbl, test_tbl, test_tbl

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)     
 Instance ID         :  1                   
 Session             :  SYS (204:115)       
 SQL ID              :  9fvrwtjugnkup       
 SQL Execution ID    :  16777216            
 Execution Started   :  02/03/2013 01:57:49 
 First Refresh Time  :  02/03/2013 01:57:49 
 Last Refresh Time   :  02/03/2013 01:57:54 
 Duration            :  5s                  
 Module/Action       :  SQL Developer/-     
 Service             :  SYS$USERS           
 Program             :  SQL Developer       
 Fetch Calls         :  1                   

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    4.85 |    4.76 |     0.09 |     1 |     12 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=2897041765)
===========================================================================================================================================
| Id |         Operation         |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                           |          | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
===========================================================================================================================================
|  0 | SELECT STATEMENT          |          |         |      |         4 |     +2 |     1 |        1 |       |          |                 |
|  1 |   SORT AGGREGATE          |          |       1 |      |         4 |     +2 |     1 |        1 |       |          |                 |
|  2 |    MERGE JOIN CARTESIAN   |          |    100M | 275K |         4 |     +2 |     1 |     100M |       |          |                 |
|  3 |     MERGE JOIN CARTESIAN  |          |      1M | 2752 |         4 |     +2 |     1 |       1M |       |          |                 |
|  4 |      MERGE JOIN CARTESIAN |          |   10000 |   31 |         4 |     +2 |     1 |    10000 |       |          |                 |
|  5 |       TABLE ACCESS FULL   | TEST_TBL |     100 |    2 |         4 |     +2 |     1 |      100 |       |          |                 |
|  6 |       BUFFER SORT         |          |     100 |   29 |         4 |     +2 |   100 |    10000 |  4096 |          |                 |
|  7 |        TABLE ACCESS FULL  | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
|  8 |      BUFFER SORT          |          |     100 | 2752 |         4 |     +2 | 10000 |       1M |  4096 |          |                 |
|  9 |       TABLE ACCESS FULL   | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
| 10 |     BUFFER SORT           |          |     100 | 275K |         5 |     +1 |    1M |     100M |  4096 |   100.00 | Cpu (5)         |
| 11 |      TABLE ACCESS FULL    | TEST_TBL |     100 |      |         1 |     +2 |     1 |      100 |       |          |                 |
===========================================================================================================================================

or generate full report using ACTIVE option. Save the output in HTML file to view it.

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'ACTIVE')
from dual;

ACTIVE reports have a rich, interactive user interface similar to Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

or generate full report using HTML option. Save the output in HTML file to view it.

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'9fvrwtjugnkup',
   report_level=>'TYPICAL',
   type=>'HTML')
from dual;

HTML version is pretty nice and simple

Hidden parameters

There are three interesting hidden parameters that can help:

_sqlmon_max_plan: Default = 20 per CPU.  This hidden parameter establishes a maximum number of plan entries that can be monitored.

_sqlmon_max_planlines:  Default = 300.   This hidden parameter establishes the number of plan lines beyond which a plan cannot be monitored.

_sqlmon_threshold: Default = 5. This parameter establishes time execution in seconds beyond which sql is monitored.

Session force

Alternative to use MONITOR hint is to force monitoring sql statments with ALTER SYSTEM SET EVENTS command

ALTER SYSTEM SET EVENTS 
'sql_monitor [sql: 5hc07qvt8v737|9ht3ba3arrzt3] force=true';

Have a fun 🙂

Tomasz