Tuesday, August 07, 2012

SQL Monitoring in 11g

With Oracle 11g, especially R2, Oracle is making it very easy to get an overview of the SQL statements (both running and finished) and how they perform. See all cursors:
select dbms_sqltune.report_sql_monitor_list(type=>'HTML', report_level=>'ALL') from dual;
Show detailed information for any given SQL ID:
select dbms_sqltune.report_sql_monitor(type=>'ACTIVE', sql_id=>'SQL ID') monitor_report from dual;
There are many options available, such as reporting on the last statement executed in the session, etc.
select dbms_sqltune.report_sql_monitor(type=>'ACTIVE') monitor_report from dual;

--set echo off timing off pagesize 0 linesize 1000 trimspool on trim on
--set long 2000000 longchunksize 2000000
select dbms_sqltune.report_sql_monitor
       ) as monitor_report
from   dual

No comments: