REM
/*
 * SQLActivity91.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

col STMT format a80 heading "Statement" WORD
col SQL_ID format A15 heading "SQL ID"
col ADDR format a20 word heading "Address"
col bg format 999,999,999 heading "Buffer|Gets"
col dr format 999,999,999 heading "Disk|Reads"
set feedback off echo off ver off
select /*+IGNOREME*/ DISTINCT sql.address||'/'||sql.hash_value addr
,    SQL.SQL_TEXT     STMT
      ,sql.child_number
  from v$sql sql
 where UPPER(sql.sql_text) LIKE UPPER('%&&sqlstm%')
AND sql.sql_text NOT LIKE '%/*+IGNOREME*/%'
order by 1
/
SET LINES 256
column a format a10 print
column c print format 999,999,999,999 heading "CPU"
column e print format 999,999,999 heading "Executes"
column pe print format 999,999,999 HEADING "CPU/Ex"
column dr print format 999,999,999 heading "DISK READS"
column bg print format 999,999,999,999 heading "BUFER GETS"
column RP print format 999,999,999 heading "ROWS" NEW_VALUE rp
column ped print format 999,999 HEADING "DISK/Ex"
column peb print format 99,999,999 HEADING "BUFFER/Ex"
column per print format 99,999 HEADING "ROWS/Ex"
column d print format a30 heading "Addr/hash[child]"
COLUMN m print FORMAT 9,999 HEADING "Mins"
set echo off ver off
select first_load_time,sysdate "Time now"
,executions e
,cpu_time c
,CASE WHEN executions>0 THEN round(cpu_time/executions) ELSE 0 END pe
,BUFFER_GETS   bg
,CASE WHEN executions>0 THEN round(BUFFER_GETS/executions) ELSE 0 END peb
,ROWS_PROCESSED rp
,CASE WHEN executions>0 THEN round(ROWS_PROCESSED/executions) ELSE 0 END per
,DISK_READS     dr
,CASE WHEN executions>0 THEN round(DISK_READS/executions) ELSE 0 END ped--,address||'/'||hash_value||'['||child_number||']' d
,round(24*60*(sysdate-TO_DATE(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))) m
from v$sql
WHERE address=SUBSTR('&&addr',1,INSTR('&&addr','/')-1)
AND     hash_value=SUBSTR('&&addr',INSTR('&&addr','/')+1)
AND     child_number=&&child;

undef sqlstm
set feedback on

/* SUMMARY - Show activity (CPU, buffer gets, rows, disk reads) of a SQL statement - Oracle9i and above.
 *
 * DETAIL - It can be useful to get a rough idea of what a given SQL statement does in terms of system activity. This can be to determine
 * things such as: how often the statement is run; when it was last run; how much CPU on average it consumes; statement IO consumption; how many rows
 * are returned. This can give an indication of whether the statment requires tuning (or if you have multiple children of the same statement whether
 * one is more expensive that the others). It can also be used to determine the progress of very large SELECTS (by monitoring the rows values).
 *
 * All the data is held in a single table - V$SQL - and this script can be used to firstly identify the statement and then by using the SQL_IDand CHILD_NUMBER display the
 * statement's activity detail.
 *
 * Once you have the SQL activity details shown you can "monitor" progres of the selected statement if it is still running by simply entering a "/"
 * to have SQL*Plus re-run the current statment. This can be useful to see how fast things are running or, if you know roughly how many rows are to be
 * fetched, how close to completion the SELECT is (this can be useful when the SQL operation is not one that Oracle automatically places in V$SESSION_LONGOPS).
 *
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM