source
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.
well my database is sleeping well right now.
If you click on the statement you will get more informations 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
