REM
/*
 * SQLActivity.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!
 */

undef stmt
undef sqlid
undef child

column STMT format a80 heading "Statement" WORD
column SQL_ID format A15 heading "SQL ID"
column CN print format 999 heading "Child"
set feedback off echo off ver off

SELECT /*+IGNOREME*/ 
       DISTINCT sql.sql_id 
      ,sql.sql_text stmt 
      ,sql.child_number cn
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 flt print format A19 heading "First load time"
column lat print format A20 heading "Last activity time"
column c print format 999,999,999 heading "CPU"
column e print format 99,999 heading "Executes"
column pe print format 99,999 HEADING "CPU/Ex"
column dr print format 9,999,999 heading "DISK READS"
column bg print format 999,999,999 heading "BUFER GETS"
column rp print format 999,999,999 heading "ROWS"
column ped print format 99,999 HEADING "DISK/Ex"
column peb print format 999,999 HEADING "BUFFER/Ex"
column per print format 99,999 HEADING "ROWS/Ex"
set echo off ver off feedback off

SELECT first_load_time flt
      ,last_active_time lat
      ,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
FROM    v$sql 
WHERE   sql_id='&&sqlid' 
AND     child_number=&&child
/
undef sqlstm

set feedback on


/* SUMMARY - Show activity (CPU, buffer gets, rows, disk reads) of a SQL statement.
 *
 * 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, specifically written for Oracle10g (but easily adapted for 9i by replacing
 * SQL_ID with ADDRESS and HASH_VALUE) 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).

Sample output might look like:

SQL> start sqlactivity
Enter value for sqlstm: SELECT first_load_time flt

SQL ID          Statement                                             Child
--------------- ----------------------------------------------------- -----
dn75xaf7t442u   SELECT first_load_time flt,last_active_time lat           0
                ,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 FROM
                v$sql WHERE sql_id='3afk4nb5d5np9' AND child_number=0
Enter value for sqlid: 3afk4nb5d5np9
Enter value for child: 0

First load time     Last activity time  Execs     CPU  CPU/Ex BUFER GETS BUFFER/Ex     ROWS ROWS/Ex DISK READS DISK/Ex
------------------- ------------------- ----- ------- ------- ---------- --------- -------- ------- ---------- -------
2007-06-01 17:16:48 2007-06-01 17:16:51     2  20,308  10,154          0         0        2       1          0       0

 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM