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