DBMS_SQLTUNE – Profile

References:

https://docs.oracle.com/database/121/TGSQL/tgsql_profiles.htm#TGSQL612

1.) Identify the SQL_IF of your command, you can use the V$SQL for example:
SQL_ID: 0th9yu2b4ad7k

2.) Create a Tuning Task: DBMS_SQLTUNE.CREATE_TUNING_TASK
3.) Set some specific Parameters , if needed, there are some cases where 
the command can take too much time, so you need to increase the time for analysis.
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, 
parameter => 'TIME_LIMIT', value => 15000);

4.) Execute the Tuning Task:
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);

The anonymous block below will execute all steps:
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0th9yu2b4ad7k');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, parameter => 'TIME_LIMIT', value => 15000);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);
end;
/

This command will return the NAME of the task: TASK_9931
5.) After the execution, you need to check the outcome of the analysis:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_9931') AS recommendations FROM dual;

6.) If you agree with the suggestion, you can apply the PROFILE:
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_9931', 
task_owner => 'SYS', replace => TRUE);

7.) If you want to disable the PROFILE:
You can find the name on this view DBA_SQLTUNE_PLANS
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('<NAME>','STATUS','DISABLED');
end;
/

8.) If you want to DROP the PROFILE:
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE (  name => '<NAME>' );
END;
/

 

DBMS_UTILITY.EXPAND_SQL_TEXT

DBMS_UTILITY.EXPAND_SQL_TEXT

This is a nice and easy feature implemented on 12c.
It consists in expand the sql_texts when you are using views.

Find below the example:

1.) Create some tables to Test:

create table dep
(id number,
 dep_name varchar2(100));
 
 ALTER TABLE dep
ADD CONSTRAINT dep_pk PRIMARY KEY (id);
 
insert into dep values (1,'dep 1');
insert into dep values (2,'dep 2');
insert into dep values (3,'dep 3');
commit;

create table emp
(id number,
emp_name varchar2(100),
id_dep number);

 ALTER TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY (id);

ALTER TABLE emp
ADD CONSTRAINT emp_dep_fk
   FOREIGN KEY (id_dep)
   REFERENCES dep (id);

insert into emp values (1, 'emp 1', 1);
insert into emp values (2, 'emp 2', 1);
insert into emp values (3, 'emp 3', 1);

insert into emp values (4, 'emp 4', 2);
insert into emp values (5, 'emp 5', 2);
insert into emp values (6, 'emp 6', 2);

insert into emp values (7, 'emp 7', 3);
insert into emp values (8, 'emp 8', 3);
insert into emp values (9, 'emp 9', 3);

commit;
 
2.) Create a view

create view v_qtd_emp_by_dep as
select count(*) qtd_emp, a.dep_name
from dep a, emp b
where
a.id=b.id_dep
group by a.dep_name;

15:12:19 SQL> select * from v_qtd_emp_by_dep;

      QTD_EMP DEP_NAME
------------- ----------
            3 dep 2
            3 dep 3
            3 dep 1
            
3.) EXPAND_SQL_TEXT

SET SERVEROUTPUT ON 
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'select * from v_qtd_emp_by_dep',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

SELECT "A1"."QTD_EMP" "QTD_EMP","A1"."DEP_NAME" "DEP_NAME" 
FROM  
(SELECT COUNT(*) "QTD_EMP","A3"."DEP_NAME" "DEP_NAME" 
FROM SYS."DEP" "A3",SYS."EMP" "A2" 
WHERE "A3"."ID"="A2"."ID_DEP" 
GROUP BY "A3"."DEP_NAME") "A1"

PL/SQL procedure successfully completed.

Advanced Queuing

Oracle Database 12c:
Advanced Queuing

Tested on:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

References:
http://www.oracle.com/technetwork/database/oracleadvancedqueuingwp-2524215.pdf
https://docs.oracle.com/database/121/ADQUE/jm_exmpl.htm#ADQUE1600

1.) Configure AQ Administrator account
The AQ Administrator user (‘aq_admin’) creates and owns the queuing infrastructure. The role 
AQ_ADMINISTATOR_ROLE that allows for the creation and administration of the queuing infrastructure needs 
to be granted to the ‘aq_admin’ user:

CREATE USER aq_admin IDENTIFIED BY aq_admin
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp; 

ALTER USER aq_admin QUOTA UNLIMITED ON users;

grant connect, resource to aq_admin;
GRANT aq_administrator_role TO aq_admin; 
GRANT connect TO aq_admin

GRANT create type TO aq_admin;

2.) Set up a simple message 
The following steps must be executed as the aq_admin user.

conn aq_admin/aq_admin
 
CREATE TYPE tst_message_type AS OBJECT 
(
id number,
name varchar2(40),
value number,
obs varchar2(4000)
);

3.) Create Queue Table and Queue 
Queues are implemented using a queue table that can hold multiple queues with the same payload type. 
The following creates a queue table ‘TST_QUEUE_TB’ and a queue ‘TST_QUEUE’. 

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE 
(queue_table => 'aq_admin.tst_queue_tb',
queue_payload_type =>'aq_admin.tst_message_type'); 

exec DBMS_AQADM.CREATE_QUEUE 
(queue_name => 'TST_QUEUE', 
queue_table => 'aq_admin.tst_queue_tb',
queue_type => DBMS_AQADM.NORMAL_QUEUE,
max_retries => 0, 
retry_delay => 0,
retention_time => 1209600, 
dependency_tracking => FALSE, 
comment => 'Test AQ',
auto_commit => FALSE);

4.) Start Queue

exec DBMS_AQADM.START_QUEUE('TST_QUEUE');

5.) Configure AQ user account 

conn / as sysdba

CREATE USER aq_user IDENTIFIED BY aq_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT aq_user_role TO aq_user; 
grant connect, resource to aq_user;
grant select on aq_admin.tst_queue_tb to aq_user;
GRANT EXECUTE ON aq_admin.tst_message_type TO aq_user;
exec DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( privilege =>'ALL', queue_name => 'aq_admin.tst_queue',grantee => 'aq_user', grant_option => FALSE);

6.) ENQUEUE MESSAGE
 
CONNECT aq_user/aq_user

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
BEGIN
  l_event_msg := AQ_ADMIN.tst_message_type(1,'TEST 1',1000,'OBS 1');

  DBMS_AQ.enqueue(queue_name          => 'aq_admin.tst_queue',        
                  enqueue_options     => l_enqueue_options,     
                  message_properties  => l_message_properties,   
                  payload             => l_event_msg,             
                  msgid               => l_message_handle);

  COMMIT;
END;
/


7.) DEQUEUE MESSAGE

CONNECT aq_user/aq_user

SET SERVEROUTPUT ON

DECLARE
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
BEGIN
  DBMS_AQ.dequeue(queue_name          => 'aq_admin.tst_queue',
                  dequeue_options     => l_dequeue_options,
                  message_properties  => l_message_properties,
                  payload             => l_event_msg,
                  msgid               => l_message_handle);

  DBMS_OUTPUT.put_line ('id          : ' || l_event_msg.id);
  DBMS_OUTPUT.put_line ('Name: ' || l_event_msg.name);
  DBMS_OUTPUT.put_line ('Value   : ' || l_event_msg.value);
  DBMS_OUTPUT.put_line ('Obs   : ' || l_event_msg.obs);
  
  COMMIT;
END;
/

id          : 1
Name: TEST 1
Value   : 1000
Obs   : OBS 1

PL/SQL procedure successfully completed.

8.) Test Procedure:

DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_event_msg           AQ_ADMIN.tst_message_type;
  l_dequeue_options     DBMS_AQ.dequeue_options_t;
  
  i number;
  qtd_msgs number;
  
BEGIN
   --- enqueue
  
  for i in 1..10 loop
  
      l_event_msg := AQ_ADMIN.tst_message_type(i,'TEST'||to_char(i),i*100,'OBS'||to_char(i));

      DBMS_AQ.enqueue(queue_name          => 'aq_admin.tst_queue',        
                      enqueue_options     => l_enqueue_options,     
                      message_properties  => l_message_properties,   
                      payload             => l_event_msg,             
                      msgid               => l_message_handle);

    
  end loop;
  commit;
  --- dequeue
   select count(*) into qtd_msgs from aq_admin.tst_queue_tb where state=0;
  
  for i in 1..qtd_msgs loop
  
       DBMS_AQ.dequeue(queue_name          => 'aq_admin.tst_queue',
                      dequeue_options     => l_dequeue_options,
                      message_properties  => l_message_properties,
                      payload             => l_event_msg,
                      msgid               => l_message_handle);

      DBMS_OUTPUT.put_line ('id          : ' || l_event_msg.id);
      DBMS_OUTPUT.put_line ('Name: ' || l_event_msg.name);
      DBMS_OUTPUT.put_line ('Value   : ' || l_event_msg.value);
      DBMS_OUTPUT.put_line ('Obs   : ' || l_event_msg.obs);
  
    end loop;  
  commit;
END;
/

DBMS_SQLTUNE

http://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94548

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.) Tables

14:24:45 SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17:00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20:00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22:00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02:00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01:00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19:00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10
14:24:55 SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        

2.) Parameters

SQL> @config
14:29:21 SQL> SHOW PARAMETER statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
14:30:09 SQL> SHOW PARAMETER control_management_pack_access

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
14:30:19 SQL>

3.) Hint MONITOR

select /*+ MONITOR */ count(a.empno), b.dname
from 
scott.emp a, scott.dept b
where a.deptno=b.deptno
group by b.dname
order by 1 desc;

4.) Checking V$SQL_MONITOR

col program format a20
col module format a20
col action format a20
col sql_text format a100

select 
sql_id, username, program, module, action, sql_text
from v$sql_monitor
where upper(sql_text) like '%SCOTT%';

SQL_ID        USERNAME             PROGRAM              MODULE               ACTION               SQL_TEXT
------------- -------------------- -------------------- -------------------- -------------------- -----------------------------------------------
ccwwc9ud5a4mg SYS                  sqlplus.exe          sqlplus.exe                               select /*+ MONITOR */ count(a.empno), b.dname
                                                                                                  from
                                                                                                  scott.emp a, scott.dept b                                                                                          

5.) Report

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.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => 'ccwwc9ud5a4mg',
  type         => 'TEXT',
  report_level => 'ALL') AS RPT
FROM dual;
SPOOL OFF                                                                                                  

QL Text
-----------------------------
elect /*+ MONITOR */ count(a.empno), b.dname from scott.emp a, scott.dept b where a.deptno=b.deptno group by b.dname order by 1 desc

lobal Information
-----------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session             :  SYS (247:51977)
SQL ID              :  ccwwc9ud5a4mg
SQL Execution ID    :  16777216
Execution Started   :  03/03/2016 14:33:29
First Refresh Time  :  03/03/2016 14:33:29
Last Refresh Time   :  03/03/2016 14:33:29
Duration            :  .001363s
Module/Action       :  sqlplus.exe/-
Service             :  SYS$USERS
Program             :  sqlplus.exe
Fetch Calls         :  2

lobal Stats
======================================
 Elapsed |  Other   | Fetch | Buffer |
 Time(s) | Waits(s) | Calls |  Gets  |
======================================
    0.00 |     0.00 |     2 |     14 |
======================================

QL Plan Monitoring Details (Plan Hash Value=2613774539)
===================================================================================================================================
 Id |       Operation        | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
    |                        |      | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
===================================================================================================================================
  0 | SELECT STATEMENT       |      |         |      |         1 |     +0 |     1 |        3 |       |          |                 |
  1 |   SORT ORDER BY        |      |      14 |    8 |         1 |     +0 |     1 |        3 |  2048 |          |                 |
  2 |    HASH GROUP BY       |      |      14 |    8 |         1 |     +0 |     1 |        3 |  759K |          |                 |
  3 |     HASH JOIN          |      |      14 |    6 |         1 |     +0 |     1 |       14 |    1M |          |                 |
  4 |      TABLE ACCESS FULL | DEPT |       4 |    3 |         1 |     +0 |     1 |        4 |       |          |                 |
  5 |      TABLE ACCESS FULL | EMP  |      14 |    3 |         1 |     +0 |     1 |       14 |       |          |                 |
===================================================================================================================================

6.) Report - ACTIVE

SPOOL report_active.html
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => 'ccwwc9ud5a4mg',
  type         => 'ACTIVE',
  report_level => 'ALL') AS RPT
FROM dual;
SPOOL OFF    

-- > Results below:
DBMS_SQLTUNE_ACTIVE_REPORT
7.) Report - List 

SPOOL report_list.txt
SELECT DBMS_SQLTUNE.report_sql_monitor_list(
  type         => 'TEXT',
  report_level => 'ALL') AS rpt
FROM dual;
SPOOL OFF


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


================================================================================================================================================================================
|    Status     | Duration |   SQL Id or   | Exec Id  |     Start     |  User  |         Module/Action          | Dop | DB Time | IOs  |               SQL Text                |
|               |          |   DBOP Name   |          |               |        |                        |     |         |      |                                       |
================================================================================================================================================================================
| DONE (ALL     |    0.00s | ccwwc9ud5a4mg | 16777216 |  03/03/2016   | SYS    | sqlplus.exe/-          |     |   0.00s |      | select /*+ MONITOR */ count(a.empno), |
| ROWS)         |          |               |          |   14:33:29    |        |                        |     |         |      | b.dname from scott.emp a, scott.dept  |
|               |          |               |          |               |        |                        |     |         |      | b where a.deptno=b.deptno group by    |
|               |          |               |          |               |        |                        |     |         |      | b.dname order by 1 desc               |
| DONE (FIRST N |      19s | f6cz4n8y72xdc | 16777216 |  03/03/2016   |        | MMON_SLAVE/Check SMB Size      |     |     19s |  641 | SELECT space_usage_kbytes FROM        |
| ROWS)         |          |               |          |   11:38:44    |        |                        |     |         |      | v$sysaux_occupants WHERE              |
|               |          |               |          |               |        |                        |     |         |      | occupant_name = 'SQL_MANAGEMENT_BASE' |
| DONE (ALL     |      22s | b9p45hkcx0pwh | 16777216 |  03/03/2016   |        |                        |     |     38s |  198 | select                                |
| ROWS)         |          |               |          |   11:38:18    |        |                        |     |         |      | dbms_qopatch.get_opatch_lsinventory() |
|               |          |               |          |               |        |                        |     |         |      | from dual                             |
| DONE          |      12s | 4bymnttwnjmw7 | 16777216 |  03/03/2016   | SYS    | DBMS_SCHEDULER/CLEANUP_TRANSIE |     |     12s | 2671 | DECLARE job BINARY_INTEGER := :job;   |
|               |          |               |          |   11:38:01    |        |                        |     |         |      | next_date TIMESTAMP WITH TIME ZONE := |
|               |          |               |          |               |        |                        |     |         |      | :mydate; broken BOOLEAN := FALSE;     |
|               |          |               |          |               |        |                        |     |         |      | job_name VARCHAR2(30) := :job_name;   |
|               |          |               |          |               |        |                        |     |         |      | job_subname VARCHAR2(30) :=           |
|               |          |               |          |               |        |                        |     |         |      | :job_subname; job_owner...            |
| DONE (ALL     |      10s | dhpn35zupm8ck | 16777216 |  03/03/2016   | SYS    | DBMS_SCHEDULER/CLEANUP_TRANSIE |     |    9.9s | 2670 | select o.name, o.owner# from obj$ o,  |
| ROWS)         |          |               |          |   11:38:03    |        |                        |     |         |      | type$ t where o.oid$ = t.tvoid and    |
|               |          |               |          |               |        |                        |     |         |      | bitand(t.properties,8388608) =        |
|               |          |               |          |               |        |                        |     |         |      | 8388608 and (sysdate-o.ctime) >       |
|               |          |               |          |               |        |                        |     |         |      | 0.0007 order by o.stime desc, o.obj#  |
|               |          |               |          |               |        |                        |     |         |      | desc                                  |
| DONE          |     136s |    OPER_1     |    1     |  03/03/2016   | CMAGNO | SQL*Plus/-             |     |   0.06s |    9 |                                       |
|               |          |               |          |   12:13:35    |        |                        |     |         |      |                                       |
================================================================================================================================================================================

8.) Report SQL Detail

SPOOL REPORT_SQL_DETAIL_ACTIVE.html
SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id       => 'ccwwc9ud5a4mg',
  type         => 'ACTIVE',
  report_level => 'ALL') AS rpt
FROM dual;
SPOOL OFF

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 |                                              |
===================================================================================================================================================================================