Script to Monitor the RMAN JOBs in Detail

Script to monitor the RMAN running jobs in detail.


$ cat > rman.sql


*********************************************************************
alter session set nls_date_format='dd-mon-rr hh24:mi:ss';

set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc  format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10
select sysdate from dual;
REM gv$session_longops (channel level)
prompt
prompt Channel progress - gv$session_longops:
prompt
select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
                    round(sofar/totalwork*100,2) "% Complete"
     FROM gv$session_longops o, gv$session s
     WHERE opname LIKE 'RMAN%'
     AND opname NOT LIKE '%aggregate%'
     AND o.sid=s.sid
     AND totalwork != 0
     AND sofar <> totalwork;
REM Check wait events (RMAN sessions) - this is for CURRENT waits only
REM use the following for 11G+
prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt
select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;
REM gv$backup_async_io
prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece
prompt if backup_tape_io_slaves=TRUE:
prompt
select s.inst_id, a.sid, CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7;
REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
col FILENAME for a50
set line 400 pagesize 100
select s.inst_id, a.sid, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,
open_time open, io_count
from gv$backup_sync_io a, gv$session s
where
a.sid=s.sid and
open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;

*******************************************************************************


O/p of the script.


SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'D

------------------

14-jul-14 15:19:57

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

------------------

14-jul-14 15:21:58

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

---------- ---------- -------------------- ---------- ---------- ---------- ----------

1 54 rman channel=ORA_DIS 1 142524 236800 60.19

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

---------- ---------- -------------------- ---------- ---------------------------------------- ------------------- ----------

1 42 1407 SQL*Net message from client WAITING 34.21

1 54 rman channel=ORA_DIS 2654 RMAN backup & recovery I/O WAITING .02

K_1

　

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

---------- ---------- -------------------- ----------- ------------------ ---------- ---------- ---------- ---------- --------- -----------------------------------------------------------------

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 580 554 47.76 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 710 554 39.01 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:24 462.99 464 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> /

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

------------------

14-jul-14 15:22:42

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

---------- ---------- -------------------- ---------- ---------- ---------- ----------

1 54 rman channel=ORA_DIS 1 236158 236800 99.73

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

---------- ---------- -------------------- ---------- ---------------------------------------- ------------------- ----------

1 42 1407 SQL*Net message from client WAITING 77.77

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

---------- ---------- -------------------- ----------- ------------------ ---------- ---------- ---------- ---------- --------- -----------------------------------------------------------------

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 536 580 1074 92.41 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 704.5 710 1411 99.23 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:24 1077.8 1078 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected




References. OTN DOC


SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'D

------------------

14-jul-14 15:19:57

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

------------------

14-jul-14 15:21:58

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

---------- ---------- -------------------- ---------- ---------- ---------- ----------

1 54 rman channel=ORA_DIS 1 142524 236800 60.19

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

---------- ---------- -------------------- ---------- ---------------------------------------- ------------------- ----------

1 42 1407 SQL*Net message from client WAITING 34.21

1 54 rman channel=ORA_DIS 2654 RMAN backup & recovery I/O WAITING .02

K_1

　

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

---------- ---------- -------------------- ----------- ------------------ ---------- ---------- ---------- ---------- --------- -----------------------------------------------------------------

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 580 554 47.76 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:25 276.99 710 554 39.01 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS IN PROGRESS 14-jul-14 15:21:24 462.99 464 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> /

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

SQL> @rman.sql '14-jul-14 15:19:57'

Session altered.

　

SYSDATE

------------------

14-jul-14 15:22:42

　

Channel progress - gv$session_longops:

　

INST_ID SID CH CONTEXT SOFAR TOTALWORK % Complete

---------- ---------- -------------------- ---------- ---------- ---------- ----------

1 54 rman channel=ORA_DIS 1 236158 236800 99.73

K_1

　

　

Session progess - CURRENT wait events and time in wait so far:

　

INST_ID SID CH SEQ# EVENT STATE SECONDS

---------- ---------- -------------------- ---------- ---------------------------------------- ------------------- ----------

1 42 1407 SQL*Net message from client WAITING 77.77

　

Disk (file and backuppiece) progress - includes tape backuppiece

if backup_tape_io_slaves=TRUE:

old 7: and a.sid=s.sid and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') order by 2,7

new 7: and a.sid=s.sid and open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss') order by 2,7

INST_ID SID CH STATUS OPEN_TIME SOFAR Mb TOTMB IO_COUNT % Complete TYPE FILENAME

---------- ---------- -------------------- ----------- ------------------ ---------- ---------- ---------- ---------- --------- -----------------------------------------------------------------

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 1.5 5 5 30.00 INPUT /u01/app/oracle/oradata/db1/users01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 55 55 112 100.00 INPUT /u01/app/oracle/oradata/db1/undotbs01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 10 500 22 2.00 INPUT /u01/app/oracle/oradata/db1/test01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 536 580 1074 92.41 INPUT /u01/app/oracle/oradata/db1/sysaux01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:25 704.5 710 1411 99.23 INPUT /u01/app/oracle/oradata/db1/system01.dbf

K_1

1 54 rman channel=ORA_DIS FINISHED 14-jul-14 15:21:24 1077.8 1078 OUTPUT/u01/app/oracle/fast_recovery_area/DB1/backupset/2014_07_14/o1_mf

K_1 _nnndf_TAG20140714T152124_9w7b0wwj_.bkp

　

6 rows selected.

　

Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):

old 6: open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')

new 6: open_time > to_date('14-jul-14 15:19:57', 'dd-mon-rr hh24:mi:ss')

no rows selected

References. OTN DOC ID.1487262.1

SQL Performance Report

A sql script, which is written by Marcel Jank for gathering the sql performance report, I found it really helpful to gather stat.

The script will ask sql_id and will generate an HTML report for that sql.

Below is the sql script written by Marcel. 

------------------------------------------------------------------------------------------------------------------------------------------------------
--      Retrieve a lot of information about query performance, based on sql_id
--
--      Script      sqlperf.sql (formerly known as sql_sql_id_html.sql)
--      Run as      DBA
--
--      Purpose     This script will retrieve a lot of info about a query, based on sql_id and create a HTML report
--
--      Input       sql_id, child_number
--
--      Author      M. Krijgsman
--
--      Remarks     !! A Diagnostics Pack license is required for this script !!
--
--      Version When        Who            What?
--      ------- ----------- -------------- ----------------------------------------------------------------------------------------------
--      1.0     10 apr 2013 M. Krijgsman   First version, based on sql_sql_id.sql version 1.9
--      1.1     21 mei 2013 M. Krijgsman   Better graph, query statistics from AWR, neater representation of v$sql_shared_cursor.
--      1.2     24 jun 2013 M. Krijgsman   Query statistics from AWR. sqlplus settings saved in /tmp
--      1.3     25 okt 2013 M. Krijgsman   Table and index statistics for those that are found in the execution plan. Easier way
--                                         to find what data you look for with a HTML menu
--      1.4     19 nov 2013 M. Krijgsman   Bugfix: forgot the execution plan in the menu. Shorter scriptname.
--                                         Bugfix: in bind variable data timestamp data wasn't shown. 
--      1.5     09 jan 2014 M. Krijgsman   When searching for stats history, execution plans in AWR are now also taken into account.
--      1.6     28 jan 2014 M. Krijgsman   Bind info from AWR. Yes! Also added other info from dba_hist_sqlstat in query history, amongst 
--                                         which parallellism.
------------------------------------------------------------------------------------------------------------------------------------------------------

column v_datetime    new_value datetime       noprint
select to_char(sysdate, 'YYYYMMDDHH24MISS') v_datetime from dual;

store set /tmp/your_sqlplus_env_&datetime..sql REPLACE

set linesize 3000
set feedback off
set verify off
set pause off
set timing off
set echo off
set heading on
set pages 999
set trimspool on
set newpage none
set define on

column vl_dbname     new_value l_dbname       noprint

select lower(name) vl_dbname from v$database;


prompt =============================================
prompt =                                           =
prompt =              sqlperf.sql                  =
prompt =  This script will retrieve all kinds of   =
prompt =  information about SQLs based on sql_id   =
prompt =                                           =
prompt =          (Version 10g or higher)          =
prompt =                                           =
prompt =============================================
prompt


accept sql_id    default '' -
  prompt 'Please provide the sql_id: '

prompt This instance.
prompt -----------------------------------

col instance_name for a16
col status for a16

select instance_name, instance_number, status 
from v$instance;


prompt
prompt

prompt Different versions of the query.
prompt -----------------------------------

prompt (This should return only a handful of rows, or no binds have been used)
prompt

column vl_inst_id     new_value l_inst_id       noprint
select instance_number vl_inst_id from v$instance;

col sql_id for a20
col last_active_time for a16
col last_load_time for a20
col instance for a16

select a.sql_id, a.child_number
, case 
   when a.inst_id = &l_inst_id then 'THIS ONE'
   else (select instance_name 
         from gv$instance 
         where instance_number=a.inst_id) 
   end instance, a.last_load_time
, to_char(a.last_active_time, 'DD-MON HH24:MI:SS') last_active_time
,      a.loaded_versions, a.open_versions, a.users_opening
from gv$sql a
where sql_id='&sql_id';

prompt
prompt

prompt When the query has been runned from a different instance, run this script there.
prompt

prompt

accept childnr    default '' -
prompt 'To what child_number is this case related?: '

prompt




spool sqlperf_&l_dbname._&sql_id._&datetime..html

/* head '-
  <title>SQL report for &sql_id on &l_dbname</title> -
  <style type="text/css"> -
    body              {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    p                 {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    tr,td             {font:9pt Courier New, Courier; color:Black; background:#EEEEEE;} -
    table             {font:9pt Courier New, Courier; color:Black; background:#EEEEEE;} -
    th                {font:bold 9pt  Arial,Helvetica,sans-serif; color:#314299; background:#befdfd;} -
    h1                {font:bold 12pt Arial,Helvetica,sans-serif; color:#003399; background-color:White;} -
    h2                {font:bold 10pt Arial,Helvetica,sans-serif; color:#FF9933; background-color:White;} -
    a                 {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.link            {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLink          {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkBlue      {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkRed       {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkRed   {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkGreen     {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  </style>' */

set heading off

prompt  <TITLE>SQL report for &sql_id on &l_dbname</TITLE>
prompt  <STYLE TYPE="text/css">
prompt    body              {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}
prompt    p                 {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}
prompt    tr,td             {font:9pt Courier New, Courier; color:Black; background:#EEEEEE;}
prompt    table             {font:9pt Courier New, Courier; color:Black; background:#EEEEEE;}
prompt    th                {font:bold 9pt  Arial,Helvetica,sans-serif; color:#314299; background:#befdfd;}
prompt    h1                {font:bold 12pt Arial,Helvetica,sans-serif; color:#003399; background-color:White;}
prompt    h2                {font:bold 10pt Arial,Helvetica,sans-serif; color:#FF9933; background-color:White;}
prompt    h4                {font:bold 9pt Arial,Helvetica,sans-serif; color:Grey; background-color:White;}
prompt    a                 {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.link            {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLink          {font:9pt Arial,Helvetica,sans-serif; color:#0F0066; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkBlue      {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkRed       {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkDarkRed   {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkGreen     {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt    a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt  </STYLE>

prompt    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
prompt    <script type="text/javascript">
prompt    google.load("visualization", "1", {packages:["corechart"]})
prompt    google.setOnLoadCallback(drawChart)
prompt    function drawChart() {
prompt    var data = new google.visualization.DataTable();
prompt        data.addColumn('datetime', 'Date');
prompt        data.addColumn('number', 'Executions');
prompt        data.addColumn('number', 'ResponseTime');
prompt        data.addRows([

col rownr for a1

select
   decode(rownum,1,'',',') rownr,
   '[new Date('||
    to_char(ADD_MONTHS(BEGIN_INTERVAL_TIME,-1), 'YYYY,MM,DD,HH24,MI,SS')
   ||'),'||
    EXECUTIONS
   ||','||
    RESPONSETIME
   ||']' rowofdata
   from (select trunc(a.BEGIN_INTERVAL_TIME, 'HH24') BEGIN_INTERVAL_TIME
    , sum(b.executions_delta) executions
    , round(sum(b.ELAPSED_TIME_DELTA)/decode(sum(b.EXECUTIONS_DELTA),0,1,sum(b.EXECUTIONS_DELTA))/1000000,2) responsetime 
   from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
   where  a.snap_id=b.snap_id 
   and    a.instance_number=b.instance_number
   and    b.sql_id='&sql_id' 
   group by trunc(a.BEGIN_INTERVAL_TIME, 'HH24')
   order by trunc(a.BEGIN_INTERVAL_TIME, 'HH24'));

prompt        ]);
prompt

prompt var options = {
prompt     curveType: "function",
prompt     width: 1300,
prompt     titleTextStyle: {color: 'black'},
prompt     isStacked: true,
prompt     vAxes: {
prompt                     0: {logScale: false, title: 'Executions', viewWindow:{min: 0} },
prompt                     1: {logScale: false, title: 'ResponseTime', textStyle:{color: 'red'}, viewWindow:{min: 0} } 
prompt                 },
prompt     series:{
prompt                     0:{targetAxisIndex:0},
prompt                     1:{targetAxisIndex:1}
prompt                 },
prompt     title: 'Response time',
prompt     hAxis: {title: 'Date', gridlines: {color: 'green', count: 2}, minorGridlines: {color: 'blue', count: 2}, format: 'dd-MM hh:mm' }
prompt };
prompt
prompt var chart = new google.visualization.LineChart(document.getElementById('chart_div'))
prompt chart.draw(data, options)
prompt }

-- /*prompt var options = {
-- prompt title: 'Response time',
-- prompt chxt: 'x,y,r',
-- prompt width: 1600,
-- prompt titleTextStyle: {color: 'black'},
-- prompt isStacked: true
-- prompt              }
-- prompt
-- prompt        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
-- prompt        chart.draw(data, options);
-- prompt      }
-- prompt

prompt    </script>
prompt  </head>


prompt <body text="#000000" bgcolor="#FFFFFF" link="#0000FF"
prompt    vlink="#000080" alink="#FF0000">

set markup html on spool on preformat off entmap on

--body   'BGCOLOR="#C5CDC5"' table 'WIDTH="90%" BORDER="1"' 


set    markup html on entmap off
set    head off

set markup HTML ON ENTMAP OFF
prompt <h1>SQL report, based on sql_id.</h1>
prompt <p>This file was created with:
prompt sqlperf.sql
prompt version 1.6 (2014)
prompt 
prompt dbname: &l_dbname
prompt SQL_ID: &sql_id
prompt date:   &datetime
prompt </p>
set markup HTML OFF ENTMAP OFF


prompt <center>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#314299"><b>Report Index</b></font>
prompt <hr align="center" width="250">
prompt
prompt
prompt <table width="90%" border="1">  
prompt <tr><th colspan="4">Query and execution plan</th></tr>  
prompt <tr>
prompt <td nowrap align="center" width="25%"><a class="link" href="#qversions">Different versions of the query</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#childs">Childs and hash values</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#sqltext">Full text of the query</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#sqltextawr">SQL text from AWR memory</a></td>  
prompt </tr>
prompt <tr>
prompt <td nowrap align="center" width="25%"><a class="link" href="#execplan">Execution plan (memory)</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#execplanawr">Execution plans (from AWR)</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#blineallplans">Execution plans (from baselines)</a></td>
prompt <td nowrap align="center" width="25%"></td>
prompt </tr>
prompt  <tr><th colspan="4">SQL plan baselines and SQL profiles</th>
prompt  </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#baselines">SQL and SQL plan baselines</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#blineinfo">SQL plan baseline info</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#sqlprofile">SQL profiles</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#sqlprofmeta">SQL profile metadata</a></td>  
prompt </tr>  
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#profsnblines">SQL, profiles and baselines</a></td>  
prompt <td nowrap align="center" width="25%"></td>  
prompt <td nowrap align="center" width="25%"></td>  
prompt <td nowrap align="center" width="25%"></td>  
prompt </tr>
prompt  <tr><th colspan="4">Run statistics</th>
prompt  </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#execsnrows">Executions, number of rows</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#respcpuwait">Response time, cpu time and wait time</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#memndisk">Memory and disk reads</a></td>
prompt <td nowrap align="center" width="25%"></td>  
prompt </tr>
prompt  <tr><th colspan="4">Binds</th>
prompt  </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#bindaware">Bind awareness</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#adapcursh">Adaptive cursor sharing</a></td> 
prompt <td nowrap align="center" width="25%"><a class="link" href="#bindmism">Bind mismatches</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#bindcontent">Content of bind variabeles</a></td>  
prompt </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#bindsqlplus">Bind variables as SQL*Plus commands</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#bindsothers">Values of bind variables of other childs</a></td> 
prompt <td nowrap align="center" width="25%"><a class="link" href="#blinedrop">Dropping associated SQL plan baselines</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#blinedrop">Purge the SQL statement from memory</a></td>  
prompt </tr>
prompt  <tr><th colspan="4">Object statistics</th>
prompt  </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#tablestats">Table statistics</a></td>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#tablestathis">Table statistics history</a></td> 
prompt <td nowrap align="center" width="25%"><a class="link" href="#tabparts">Table partitions</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#indexstats">Index statistics</a></td>
prompt </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#indparts">Index partitions</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#indstathis">Index statistics history</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#indcolstats">Indexed column statistics</a></td>
prompt <td nowrap align="center" width="25%"></td>
prompt </tr>
prompt  <tr><th colspan="4">Response time history</th>
prompt  </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#rtimehis">Response time history</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#rtimehisdet">Query history - details</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#rtimehisbind">Query history - binds</a></td>
prompt <td nowrap align="center" width="25%"><a class="link" href="#rtimehisgraph">Response time history graph</a></td>
prompt </tr>
prompt <tr>  
prompt <td nowrap align="center" width="25%"><a class="link" href="#rtimestats">AWR responsetime statistics</a></td>
prompt <td nowrap align="center" width="25%"></td>
prompt <td nowrap align="center" width="25%"></td>
prompt <td nowrap align="center" width="25%"></td>
prompt </tr>
prompt </table>
prompt </center>  
prompt 


set heading on
set markup HTML ON ENTMAP OFF

prompt
prompt
prompt
prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <h2>This instance.</h2>
set markup HTML ON ENTMAP ON

col instance_name for a20
col status for a20

select instance_name, instance_number, status 
from v$instance;


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <h2>Selected sql_id and child_number.</h2>
set markup HTML ON ENTMAP ON

select instance_name, '&sql_id' sql_id, '&childnr' childnr
from v$instance;


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="qversions"></A><h2>Different versions of the query.</h2>
prompt <p>(This should return only a handful of rows, or no binds have been used)</p>

set markup HTML ON ENTMAP ON

col sql_id for a20
col last_active_time for a16
col last_load_time for a20
col instance for a16

select a.sql_id, a.child_number
, case 
   when a.inst_id = &l_inst_id then 'THIS ONE'
   else (select instance_name 
         from gv$instance 
         where instance_number=a.inst_id) 
   end instance, a.last_load_time
, to_char(a.last_active_time, 'DD-MON HH24:MI:SS') last_active_time
,      a.loaded_versions, a.open_versions, a.users_opening
from gv$sql a
where sql_id='&sql_id';


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="childs"></A><h2>Childs and hash values.</h2>
set markup HTML ON ENTMAP ON

col last_active_time for a16
col last_load_time for a20

select a.sql_id, a.child_number
, (select instance_name 
         from gv$instance 
         where instance_number=a.inst_id) instance_name
,      a.hash_value, a.old_hash_value, a.plan_hash_value
from gv$sql a
where a.sql_id='&sql_id';

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="sqltext"></A><h2>Full text of the query (up to 50000 characters).</h2>
set markup HTML ON ENTMAP ON

set long 50000
col sql_fulltext for a4000

select sql_fulltext
from v$sql
where sql_id='&sql_id'
and child_number=&childnr;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="sqltextawr"></A><h2>SQL text from AWR memory (just in case it is not in memory anymore.)</h2>
set markup HTML ON ENTMAP ON

col sql_text for a4000

select *
from sys.WRH$_SQLTEXT 
where sql_id='&sql_id';

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="baselines"></A><h2>SQL vs. SQL Plan Baselines.</h2>
set markup HTML ON ENTMAP ON

col sql_id for a20
col plan_name for a35
col created for a30
col FORCE_MATCHING_SIGNATURE for 9999999999999999999

select sql.sql_id, sql.child_number, sql.inst_id, sql.force_matching_signature, sql.plan_hash_value, bl.plan_name, bl.enabled, bl.accepted, bl.fixed, bl.optimizer_cost
from gv$sql sql
,    dba_sql_plan_baselines bl
where sql.sql_id='&sql_id'
and   sql.child_number=&childnr
and   sql.force_matching_signature=bl.SIGNATURE;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="blineinfo"></A><h2>SQL Plan Baseline information.</h2>
set markup HTML ON ENTMAP ON

col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30

select sql_handle, plan_name, origin, created, last_modified, last_verified
from dba_sql_plan_baselines
where signature in (select force_matching_signature
                    from v$sql
where sql_id='&sql_id'
and   child_number=&childnr);

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="sqlprofile"></A><h2>SQL Profile information.</h2>
set markup HTML ON ENTMAP ON

col name for a30
col task_exec_name for a16
col category for a10

select sql.sql_id, prof.name, prof.category, prof.created, prof.task_exec_name, prof.status
from DBA_SQL_PROFILES prof
,    gv$sql sql
where sql.sql_id='&sql_id'
and   sql.child_number=&childnr
and   sql.force_matching_signature=prof.SIGNATURE;


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="sqlprofmeta"></A><h2>SQL Profile metadata.</h2>
set markup HTML ON ENTMAP ON

set heading off
col outline_hints for a132

select extractvalue(value(d), '/hint') as outline_hints
from xmltable('/*/outline_data/hint' passing 
(select xmltype(other_xml) as xmlval
   from v$sql_plan
where sql_id='&sql_id'
   and child_number=&childnr
   and other_xml is not null
  )
) d;


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="profsnblines"></A><h2>SQL, profiles and baselines.</h2>
set markup HTML ON ENTMAP ON

set heading on

col sql_profile for a30
col sql_patch for a30
col sql_plan_baseline for a35

select sql_id, child_number, inst_id, sql_profile, sql_plan_baseline, sql_patch
from gv$sql
where sql_id='&sql_id'
and   child_number=&childnr
order by sql_id, inst_id, child_number;


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="execsnrows"></A><h2>Executions, number of rows.</h2>
set markup HTML ON ENTMAP ON

select executions, parse_calls, loads, rows_processed, sorts
from v$sql
where sql_id='&sql_id'
and child_number=&childnr;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="respcpuwait"></A><h2>Response time, cpu time and wait time (in seconds).</h2>
set markup HTML ON ENTMAP ON

select trunc(elapsed_time/1000000,1) elapsed_time, trunc(application_wait_time/1000000,1) applic_wait_time, trunc(cpu_time/1000000,1) cpu_time
, trunc(user_io_wait_time/1000000,1) user_io_wait_time, trunc(concurrency_wait_time/1000000,1) concurr_time
from v$sql
where sql_id='&sql_id'
and child_number=&childnr;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="memndisk"></A><h2>Memory and disk reads.</h2>
set markup HTML ON ENTMAP ON

select buffer_gets, disk_reads, (sharable_mem+persistent_mem+runtime_mem) sql_area_used
from v$sql
where sql_id='&sql_id'
and child_number=&childnr;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <h2>Who ran the query?</h2>
set markup HTML ON ENTMAP ON

col username for a30
col PARSING_SCHEMA_NAME for a30
col module for a40
col action for a30
col service for a30
select u.username, s.PARSING_SCHEMA_NAME, s.SERVICE, s.MODULE, s.ACTION
from v$sql s
,    dba_users u
where s.sql_id='&sql_id'
and s.child_number=&childnr
and u.user_id=s.PARSING_USER_ID;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="bindaware"></A><h2>Bind aware, sharable?</h2>
set markup HTML ON ENTMAP ON

col IS_OBSOLETE for a11
col IS_BIND_SENSITIVE for a17
col IS_BIND_AWARE for a13
col IS_SHAREABLE for a12

select IS_OBSOLETE, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE     
from v$sql
where sql_id='&sql_id'
and child_number=&childnr;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="adapcursh"></A><h2>Adaptive cursor sharing.</h2>
set markup HTML ON ENTMAP ON

col PREDICATE for a30

select inst_id, sql_id, child_number, predicate,range_id, low, high
from GV$SQL_CS_SELECTIVITY 
where sql_id = '&sql_id'
order by inst_id, child_number;

prompt
prompt

select * from GV$SQL_CS_HISTOGRAM
where sql_id = '&sql_id'
order by inst_id, child_number;



prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="bindmism"></A><h2>Bind mismatches.</h2>
set markup HTML ON ENTMAP ON

select *
from
  xmltable( 'for $a at $i in /ROWSET/ROW
            ,$r in $a/*
              return element ROW{
                   element ROW_NUM{$i}
                  ,element COL_NAME{$r/name()}
                  ,element COL_VALUE{$r/text()}
              }'
            passing xmltype(cursor( select * 
                                    from   v$sql_shared_cursor 
                                    where  sql_id='&sql_id'
                                    and    child_number=&childnr
            ))
            columns
              row_num   int
             ,col_name  varchar2(30)
             ,col_value varchar2(100)
  );


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="bindcontent"></A><h2>Content of bind variabeles (use as example).</h2>
set markup HTML ON ENTMAP ON

col name for a10
col value_string for a50
col datatype_string for a50

select child_number, name, position
, case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end value_string
, datatype_string
from v$sql_bind_capture
where sql_id='&sql_id'
and child_number=&childnr
order by position;

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="bindsqlplus"></A><h2>Bind variables as SQL*Plus commands.</h2>
set markup HTML ON ENTMAP ON

set heading off

select 'variable '||replace(name, ':', 'BIND_')||' '||decode(datatype_string, 'TIMESTAMP', 'VARCHAR2(128)', datatype_string) datatype_string
from v$sql_bind_capture
where sql_id='&sql_id'
and child_number=&childnr
order by position;

prompt

select 'exec '||replace(name, ':', ':BIND_')||' := '||
case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end value_string
from v$sql_bind_capture
where sql_id='&sql_id'
and child_number=&childnr
and datatype_string NOT LIKE '%CHAR%'
and datatype_string NOT IN ('DATE', 'CLOB', 'TIMESTAMP')
order by position;

select 'exec '||replace(name, ':', ':BIND_')||' := '''||value_string||''''
from (
select name
, case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end value_string
, position
from v$sql_bind_capture
where sql_id='&sql_id'
and child_number=&childnr
and (datatype_string LIKE '%CHAR%'
     OR datatype_string IN ('DATE', 'CLOB', 'TIMESTAMP'))
)
order by position;



prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="bindsothers"></A><h2>Values of bind variables of other childs.</h2>
set markup HTML ON ENTMAP ON

set heading on
col name for a10
col value_string for a50
col datatype_string for a50

select child_number, name, position
, case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end value_string
, datatype_string
from v$sql_bind_capture
where sql_id='&sql_id'
and child_number <> &childnr
order by child_number, position;

set heading off

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="execplan"></A><h2>Execution plan of the query.</h2>

set markup HTML OFF ENTMAP OFF

prompt <pre xml:space="preserve" class="oac_no_warn">
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'ALL'));
prompt </pre>


prompt
prompt
prompt

set markup HTML ON ENTMAP OFF


prompt <A NAME="execplanawr"></A><h2>Execution plan of the query from AWR.</h2>
set markup HTML OFF ENTMAP OFF

prompt <pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'));
prompt </pre>

prompt
prompt

set markup HTML ON ENTMAP OFF

prompt
prompt <A NAME="blineallplans"></A><h2>All execution plans in sql plan baselines.</h2>
prompt <p>Run as SYS to see this.</p>
set markup HTML ON ENTMAP ON

column v_sql_handle     new_value l_sql_handle       noprint

set markup HTML OFF ENTMAP OFF

prompt <pre xml:space="preserve" class="oac_no_warn">
select distinct plan.SQL_HANDLE v_sql_handle
from dba_sql_plan_baselines plan
,    gv$sql sql
where sql.sql_id='&sql_id'
and   sql.child_number=&childnr
and   sql.force_matching_signature=plan.SIGNATURE;
prompt </pre>

prompt <pre xml:space="preserve" class="oac_no_warn">
SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(sql_handle=>'&l_sql_handle'));
prompt </pre>


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="blinedrop"></A><h2>Dropping associated SQL plan baselines.</h2>
prompt <p>(Based on SQL_HANDLE gives an error, but works.</p>


select distinct 'select sys.dbms_spm.DROP_SQL_PLAN_BASELINE('''||sql_handle||''') from dual;' "Based on SQL_HANDLE"
from dba_sql_plan_baselines
where signature in (select force_matching_signature
                    from v$sql
where sql_id='&sql_id'
and   child_number=&childnr);

prompt

select 'declare   v_pls PLS_INTEGER; BEGIN   v_pls :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'''||sql_handle||''', plan_name=>'''||plan_name||'''); END;'
from dba_sql_plan_baselines
where signature in (select force_matching_signature
                    from v$sql
where sql_id='&sql_id'
and   child_number=&childnr);


prompt
prompt
prompt
prompt <A NAME="planpurge"></A><h2>Generated statement to purge your SQL statement from the shared pool.</h2>

select 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from v$sql
where sql_id = '&sql_id'
and child_number=&childnr;

prompt
prompt


prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="tablestats"></A><h2>Tables accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

set heading on

SELECT owner, table_name, last_analyzed, sample_size, num_rows, avg_row_len, blocks, partitioned, global_stats
FROM dba_tables
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%TABLE ACCESS%'
  )
ORDER BY owner, table_name
/

prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="tablestathis"></A><h2>Statistics history for tables accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type, to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime, rowcnt, blkcnt, avgrln ,samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE object_type in ('TABLE')
AND   object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%TABLE ACCESS%'
  )
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;

prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="tabparts"></A><h2>Partitions of tables accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT table_owner, table_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows, avg_row_len
FROM dba_tab_partitions
WHERE table_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%TABLE ACCESS%'
  )
ORDER BY table_owner, table_name, partition_name
/

prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="indexstats"></A><h2>Indexes accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT owner, index_name, table_name, last_analyzed, sample_size, num_rows, partitioned, global_stats
FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%INDEX%'
  )
ORDER BY owner, table_name, index_name
/

prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="indparts"></A><h2>Partitions of indexes accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT index_owner, index_name, partition_name, subpartition_count, last_analyzed, sample_size, num_rows
FROM dba_ind_partitions
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%INDEX%'
  )
ORDER BY index_owner, index_name, partition_name
/

prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="indstathis"></A><h2>Statistics history for indexes accessed in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT ob.owner, ob.object_name, ob.subobject_name, ob.object_type,to_char(savtime, 'DD-MON-YY HH24:MI:SS') savtime
     , rowcnt, BLEVEL , LEAFCNT, DISTKEY, CLUFAC, samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_IND_HISTORY, dba_objects ob
WHERE object_type in ('INDEX')
AND object_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%INDEX%'
  )
and object_id=obj#
order by ob.owner, ob.object_name, analyzetime asc
;



prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <A NAME="indcolstats"></A><h2>Statistics indexed columns for indexes used in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT ic.index_owner, ic.index_name, ic.table_name, ic.column_name, ic.column_position col_pos, tc.last_analyzed, tc. sample_size, tc.num_distinct, tc.num_nulls, tc.density, tc.histogram, tc.num_buckets
FROM dba_ind_columns ic
,    dba_tab_columns tc
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%INDEX%'
  )
AND ic.table_owner=tc.owner
AND ic.table_name=tc.table_name
AND ic.column_name=tc.column_name
ORDER BY ic.table_owner, ic.table_name, ic.index_name, ic.column_position
/

/* Query is too slow (~20 seconds) 
prompt
prompt
prompt

set markup HTML ON ENTMAP OFF
prompt <h2>Statistics history for indexed columns for indexes used in the execution plan.</h2>
set markup HTML ON ENTMAP ON

SELECT ob.owner, ob.object_name,  col.COLUMN_NAME, to_char(his.savtime, 'DD-MON-YY HH24:MI:SS') savtime
, his.NULL_CNT, his.DISTCNT, his.DENSITY, his.SAMPLE_DISTCNT, his.sample_size,  his.TIMESTAMP#
FROM sys.WRI$_OPTSTAT_HISTHEAD_HISTORY his
, dba_objects ob
, dba_tab_columns col
, dba_ind_columns ic
WHERE ic.index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &childnr, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null,  'ALL'))
  )
where plan_table_output like '%INDEX%'
  )
AND ic.table_owner=col.owner
AND ic.table_name=col.table_name
AND ic.column_name=col.column_name
and ob.object_type in ('TABLE')
and ob.object_id=his.obj#
and col.COLUMN_ID=his.INTCOL#
and ob.object_name=col.TABLE_NAME
and ob.owner=col.owner
order by col.owner, col.table_name, ic.column_position, col.column_name, savtime asc
;
*/

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="rtimehis"></A><h2>History of query response time.</h2>
set markup HTML ON ENTMAP ON

alter session set NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF';

set headin on

col BEGIN_INTERVAL_TIME for a40
col FORCE_MATCHING_SIGNATURE for 9999999999999999999999
select a.BEGIN_INTERVAL_TIME
,  a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE F_MATCHING_SIGN
, b.SQL_PROFILE
, b.executions_delta EXEC_DELTA
, round(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000,2) "Elapsed time (sec.)" 
, b.PX_SERVERS_EXECS_TOTAL PX_SERV_TOT
, b.PX_SERVERS_EXECS_DELTA PX_SERV_DELTA
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
order by a.BEGIN_INTERVAL_TIME
/

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="rtimehisdet"></A><h2>History of query details.</h2>
set markup HTML ON ENTMAP ON

select a.BEGIN_INTERVAL_TIME
,  a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.OPTIMIZER_COST
, b.LOADED_VERSIONS
, b.VERSION_COUNT
, b.MODULE
, b.ACTION
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
order by a.BEGIN_INTERVAL_TIME
/

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="rtimehisbind"></A><h2>History of query - binds.</h2>
set markup HTML ON ENTMAP ON

col DATATYPE_STRING for a30
col VALUE_STRING for a30

select a.BEGIN_INTERVAL_TIME
,  a.INSTANCE_NUMBER
, b.PLAN_HASH_VALUE
, b.INVALIDATIONS_TOTAL
, c.POSITION
, c.DATATYPE_STRING
, case c.datatype
            when 180 then to_char(anydata.accesstimestamp(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(c.value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else c.value_string
  end value_string
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b,
       table(dbms_sqltune.extract_binds(b.bind_data)) c
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id'
and    b.bind_data is not null
order by a.BEGIN_INTERVAL_TIME, c.POSITION
/

prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="rtimehisgraph"></A><h2>Query response time graph.</h2>


prompt <div id="chart_div" style="width: 700px; height: 500px;"></div>


prompt
prompt
prompt
set markup HTML ON ENTMAP OFF
prompt <A NAME="rtimestats"></A><h2>Query statistics (AWR).</h2>

prompt
prompt
set heading on

prompt
prompt <h4>Response time statistics.</h4>

select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, round(max(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MAX (sec)" 
, round(min(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "MIN (sec)" 
, round(avg(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "AVG (sec)" 
, round(stddev(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "STDDEV"
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/

prompt
prompt <h4>Cost.</h4>




select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, max(OPTIMIZER_COST) "MAX COST"
, min(OPTIMIZER_COST) "MIN COST"
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/

prompt
prompt <h4>Modules and actions.</h4>


select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, b.MODULE
, b.ACTION
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE, b.MODULE, b.ACTION
/

prompt
prompt <h4>Response time, broken up.</h4>


select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, round(sum(b.ELAPSED_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "ELAPSED (sec)" 
, round(sum(b.CPU_TIME_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CPU (sec)" 
, round(sum(b.IOWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "IO WAIT (sec)" 
, round(sum(b.APWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "APP WAIT (sec)" 
, round(sum(b.CLWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CLUSTER WAIT (sec)" 
, round(sum(b.CCWAIT_DELTA/decode(b.EXECUTIONS_DELTA,0,1,b.EXECUTIONS_DELTA)/1000000),2) "CONCUR WAIT (sec)" 
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/

prompt
prompt <h4>SQL Profiles, sharable memory, parses and executes.</h4>


select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, b.FORCE_MATCHING_SIGNATURE
, b.SQL_PROFILE
, sum(b.SHARABLE_MEM) "SHARABLE MEM"
, sum(b.PARSE_CALLS_DELTA) "TOT PARSE"
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE, b.FORCE_MATCHING_SIGNATURE, b.SQL_PROFILE
order by a.INSTANCE_NUMBER
/

prompt
prompt <h4>Buffer gets, disk reads, direct writes.</h4>



select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.BUFFER_GETS_DELTA) "BUFFER GETS" 
, sum(b.DISK_READS_DELTA) "DISK READS"
, sum(b.DIRECT_WRITES_DELTA) "DIRECT WRITES"
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
/

prompt
prompt <h4>And the rest ;).</h4>

select a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
, sum(b.ROWS_PROCESSED_DELTA) "ROWS_PROCESSED"
, sum(b.SORTS_DELTA) "SORTS"
, sum(b.INVALIDATIONS_DELTA) "INVALIDATIONS"
, sum(b.PX_SERVERS_EXECS_DELTA) "PX_SERVERS_EXECS"
, sum(b.executions_delta) "TOT EXECS"
from   dba_hist_snapshot a, 
       dba_hist_sqlstat b 
where  a.snap_id=b.snap_id 
and    a.instance_number=b.instance_number
and    b.sql_id='&sql_id' 
group by a.INSTANCE_NUMBER, b.PLAN_HASH_VALUE
order by a.INSTANCE_NUMBER
/


spool off

@/tmp/your_sqlplus_env_&datetime..sql

References.

http://mjsoracleblog.wordpress.com/2013/02/06/table-and-index-statistics-history/