DBMS_SQL_MONITOR

http://docs.oracle.com/database/121/ARPLS/d_sql_monitor.htm#ARPLS74779

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

1.) Create table:

create table cmagno.test_monitor
(
num number,
text varchar2(1000),
dt date
)
tablespace users;

create table cmagno.test_monitor2
(
num number,
text varchar2(1000),
dt date
)
tablespace users;

create table cmagno.test_monitor3
(
num number,
text varchar2(1000),
dt date
)
tablespace users;

create unique index cmagno.uk_test_monitor on cmagno.test_monitor(num) tablespace users;
create unique index cmagno.uk_test_monitor2 on cmagno.test_monitor2(num) tablespace users;
create unique index cmagno.uk_test_monitor3 on cmagno.test_monitor3(num) tablespace users;

exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'CMAGNO',TABNAME=>'TEST_MONITOR',CASCADE=> true);
exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'CMAGNO',TABNAME=>'TEST_MONITOR2',CASCADE=> true);
exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'CMAGNO',TABNAME=>'TEST_MONITOR3',CASCADE=> true);

2.) Populating the Tables

declare

i number;
begin

for i in 1..1000 loop
    insert into cmagno.test_monitor
    values
    (i,'test monitor:'||to_char(i),sysdate);
    
    insert into cmagno.test_monitor2
    values
    (i,'test monitor:'||to_char(i),sysdate);
    
    insert into cmagno.test_monitor3
    values
    (i,'test monitor:'||to_char(i),sysdate);
end loop;
commit;
end;
/

3.) BEGIN Operation

12:08:47 SQL> show user
USER is "CMAGNO"

col dbop_name format a10
col module format a20
col program format a20
col action  format a25

SELECT dbop_name, dbop_exec_id, module, program, action, status
FROM   v$sql_monitor;


DBOP_NAME  DBOP_EXEC_ID MODULE               PROGRAM              ACTION                    STATUS
---------- ------------ -------------------- -------------------- ------------------------- -------------------
                      0 DBMS_SCHEDULER       ORACLE.EXE (J002)    CLEANUP_TRANSIENT_TYPE    DONE (ALL ROWS)
                      0 DBMS_SCHEDULER       ORACLE.EXE (J002)    CLEANUP_TRANSIENT_TYPE    DONE
                      0                      ORACLE.EXE (MMON)                              DONE (ALL ROWS)
                      0 MMON_SLAVE           ORACLE.EXE (M003)    Check SMB Size            DONE (FIRST N ROWS)


VARIABLE oper_id NUMBER;

BEGIN
  :oper_id := DBMS_SQL_MONITOR.begin_operation (
                   dbop_name       => 'OPER_1',
                   dbop_eid        => :oper_id,
                   forced_tracking => DBMS_SQL_MONITOR.force_tracking
                 );
END;
/

PL/SQL procedure successfully completed.

12:13:36 SQL> print :oper_id;

   OPER_ID
----------
         1

SELECT dbop_name, dbop_exec_id, module, program, action, status
FROM   v$sql_monitor
WHERE  username = 'CMAGNO';

DBOP_NAME  DBOP_EXEC_ID MODULE               PROGRAM              ACTION                    STATUS
---------- ------------ -------------------- -------------------- ------------------------- ------------
OPER_1                1 SQL*Plus             sqlplus.exe                                    EXECUTING

4.) Operation

-- Some Operations to simulate

set serveroutput on

declare

cursor c1 is
select count(*) cnt from 
cmagno.test_monitor;

cursor c2 is
select count(*) cnt from 
cmagno.test_monitor2;

cursor c3 is
select count(*) cnt from 
cmagno.test_monitor3;

cursor c4 is
select a.text||' LOOP 1' text
from cmagno.test_monitor a, cmagno.test_monitor2 b 
where 
a.num=b.num and
a.num <10;

cursor c5 is
select a.text||' LOOP 2' text
from cmagno.test_monitor a, cmagno.test_monitor3 b 
where 
a.num=b.num and
a.num <10;

v_cnt_1 number;
v_cnt_2 number;
v_cnt_3 number;

begin

    for xx in c1 loop
        v_cnt_1 := xx.cnt;
    end loop;
    
    for xx in c2 loop
        v_cnt_2 := xx.cnt;
    end loop;
    
    for xx in c3 loop
        v_cnt_3 := xx.cnt;
    end loop;
    
    for xx in c4 loop
        dbms_output.put_line(xx.text);
    end loop;
    
    for xx in c5 loop
        dbms_output.put_line(xx.text);
    end loop;
    
    update cmagno.test_monitor 
    set dt=sysdate+1
    where num =100;
    
    update cmagno.test_monitor2 
    set dt=sysdate+1
    where num =100;
    
    update cmagno.test_monitor3 
    set dt=sysdate+1
    where num =100;
    commit;
end;
/

5.) End Operation

BEGIN
  DBMS_SQL_MONITOR.end_operation (
    dbop_name       => 'OPER_1',
    dbop_eid        => :oper_id
  );
END;
/

6.) Checking

SELECT dbop_name, dbop_exec_id, module, program, action, status
FROM   v$sql_monitor
WHERE  username = 'CMAGNO';

DBOP_NAME  DBOP_EXEC_ID MODULE               PROGRAM              ACTION                    STATUS
---------- ------------ -------------------- -------------------- ------------------------- --------
OPER_1                1 SQL*Plus             sqlplus.exe                                    DONE

7.) Report

-- On this test i am using TEXT, but can be used: TEXT, HTML, ACTIVE and XML
-- Refer to: http://docs.oracle.com/database/121/ARPLS/d_sql_monitor.htm#ARPLS74789

spool report.txt

SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'OPER_1',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;

spool off

REPORT
--------------------------------------------------------------
SQL Monitoring Report

Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  CMAGNO (247:55406)
 DBOP Name           :  OPER_1
 DBOP Execution ID   :  1
 First Refresh Time  :  03/03/2016 12:13:35
 Last Refresh Time   :  03/03/2016 12:15:51
 Duration            :  136s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus.exe

Global Stats
=========================================================
| Elapsed |    IO    |  Other   | Buffer | Read | Read  |
| Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
=========================================================
|    0.06 |     0.03 |     0.03 |      3 |    9 | 120KB |
=========================================================

-- Test with ACTIVE

spool report_active.html

SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'OPER_1',
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;

spool off

 (CHROME best option) 
DBMS_SQL_MONITOR_ACTIVE
7.) Report list

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_list.txt

SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(
  DBOP_NAME    => 'OPER_1',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;

spool off;


                                                                                SQL Monitoring List
                                                                               =====================


===================================================================================================================================================================================
| Status | Duration | Inst Id | SQL Id or DBOP Name | Exec Id |        Start        |  User  | Module/Action | Dop | DB Time | IOs |                   SQL Text                   |
===================================================================================================================================================================================
| DONE   |     136s |    1    |       OPER_1        |    1    | 03/03/2016 12:13:35 | CMAGNO | SQL*Plus/-    |     |   0.06s |   9 |                                              |
===================================================================================================================================================================================
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s