@@header

/*
*
*  Author        : Vishal Gupta
*  Purpose       : Display Session's from ASH
*  Compatibility : 10.1 and above
*  Parameters    : 1 - SID
*                  2 - INST_ID         (optional, default to 1)
*                  3 - Serial#         (Default %)
*                  4 - Number of hours (Default 24)
*                  5 - SQL_ID          (Default %)
*                  6 - WhereClause     
*                  
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-SEP-14  Vishal Gupta  Removed filter for SQL_EXEC_START IS NOT NULL
*  03-FEB-14  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE SID
UNDEFINE INST_ID
UNDEFINE SERIAL
UNDEFINE HOURS
UNDEFINE SQL_ID
UNDEFINE WHERECLAUSE

DEFINE SID="&&1"
DEFINE INST_ID="&&2"
DEFINE SERIAL="&&3"
DEFINE HOURS="&&4"
DEFINE SQL_ID="&&5"
DEFINE WHERECLAUSE="&&6"

COLUMN  _SID          NEW_VALUE  SID         NOPRINT
COLUMN  _INST_ID      NEW_VALUE  INST_ID     NOPRINT
COLUMN  _SERIAL       NEW_VALUE  SERIAL      NOPRINT
COLUMN  _HOURS        NEW_VALUE  HOURS       NOPRINT
COLUMN  _SQL_ID       NEW_VALUE  SQL_ID       NOPRINT

set term off
SELECT DECODE(UPPER('&&SID'),'','%',UPPER('&&SID'))         "_SID"
     , DECODE(UPPER('&&INST_ID'),'','1',UPPER('&&INST_ID')) "_INST_ID"
     , DECODE(UPPER('&&SERIAL'),'','%',UPPER('&&SERIAL'))   "_SERIAL"
     , DECODE(UPPER('&&HOURS'),'','0.01',UPPER('&&HOURS'))    "_HOURS"
     , DECODE('&&SQL_ID','','%','&&SQL_ID')                 "_SQL_ID"
FROM DUAL;
set term on
/***********************************/


PROMPT ***************************************************
PROMPT * ASH SQL Details
PROMPT *
PROMPT * Input Parameter:
PROMPT *   SID             = "&&SID"
PROMPT *   Instance ID     = "&&INST_ID"
PROMPT *   Serial#         = "&&SERIAL"
PROMPT *   Number of Hours = "&&HOURS"
PROMPT *   SQL Id          = "&&SQL_ID"
PROMPT *   Where Clause    = "&&WHERECLAUSE"
PROMPT ***************************************************

COLUMN session_id                 HEADING "SID"              FORMAT 99999
COLUMN inst_id                    HEADING "I#"               FORMAT 99
COLUMN "session_serial#"          HEADING "Serial#"          FORMAT 999999
COLUMN sample_time_min                                       FORMAT a18
COLUMN sample_time_max                                       FORMAT a18
COLUMN username                   HEADING "DBUser"           FORMAT a20 
COLUMN service_name               HEADING "ServiceName"      FORMAT a22
COLUMN program                    HEADING "Program"          FORMAT a25 TRUNCATE
COLUMN machine                    HEADING "Machine"          FORMAT a25 
COLUMN pga_allocated              HEADING "PGA(MB)"          FORMAT 999,999

-- Get the SQL Statements from ASH
SELECT /*+ parallel(ash, 10) */ 
       --ash.sql_exec_id,
       TO_CHAR(MIN(ash.sample_time),'DD-MON-YY HH24:MI:SS')                           sample_time_min
     , TO_CHAR(max(ash.sample_time) ,'DD-MON-YY HH24:MI:SS')                          sample_time_max
     , NVL(ash.qc_session_id,ash.session_id)                                          session_id 
     , NVL(ash.qc_instance_id,ash.inst_id)                                            inst_id     
     , NVL(ash.qc_session_serial#,ash.session_serial#)                                session_serial#
     , u.username
     , s.name                                                                         service_name
     , NVL(ash_parent.program,ash.program)                                            program
     , SUBSTR(NVL(ash_parent.machine,ash.machine)
               ,1,DECODE(INSTR(NVL(ash_parent.machine,ash.machine),'.')-1,-1,LENGTH(NVL(ash_parent.machine,ash.machine)))
              )                                                                       machine
     , ROUND(MAX(ash.pga_allocated + NVL(ash_parent.pga_allocated,0))/power(1024,2))  pga_allocated
    FROM gv$active_session_history ash
         JOIN dba_users u ON u.user_id = ash.user_id
         JOIN dba_services s ON s.name_hash = ash.service_hash
         LEFT OUTER JOIN gv$active_session_history ash_parent 
                     ON ash_parent.inst_id                   = ash.qc_instance_id
                    AND ash_parent.session_id                = ash.qc_session_id
                    AND ash_parent.session_serial#           = ash.qc_session_serial#
                    AND CAST(ash_parent.sample_time as DATE) = ash.sql_exec_start
   WHERE ash.sql_id                                       IS NOT NULL
     AND NVL(ash.qc_instance_id,ash.inst_id)              LIKE '&&INST_ID'  
     AND NVL(ash.qc_session_id,ash.session_id)            LIKE '&&SID' 
     AND NVL(ash.qc_session_serial#,ash.session_serial#)  LIKE '&&SERIAL'
     AND ash.sql_id                                       LIKE '&&SQL_ID'
     AND ash.sample_time                                  > sysdate - (&&HOURS/24)
     AND (ash_parent.sample_time IS NULL or ash_parent.sample_time > sysdate - (&&HOURS/24))
     --AND ash.sql_exec_start IS NOT NULL
     &&WHERECLAUSE
GROUP BY NVL(ash.qc_session_id,ash.session_id)
       , NVL(ash.qc_instance_id,ash.inst_id)   
       , NVL(ash.qc_session_serial#,ash.session_serial#)
       , u.username
       , s.name
       , NVL(ash_parent.program,ash.program)
       , SUBSTR(NVL(ash_parent.machine,ash.machine),1,DECODE(INSTR(NVL(ash_parent.machine,ash.machine),'.')-1,-1,LENGTH(NVL(ash_parent.machine,ash.machine))))
ORDER BY MIN(ash.sample_time) ASC   
       , max(ash.sample_time) asc
;


@@footer