133 lines
5.9 KiB
SQL
133 lines
5.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Session details from ASH
|
|
* Compatibility : 10.1 and above
|
|
* Parameters : 1 - Number of hours (Default 1 hour)
|
|
* 2 - SQLExec Duration (in Seconds) (Default 60 sec)
|
|
* 3 - DB User Name (Default '%')
|
|
* 4 - Program (Default '%')
|
|
* 5 - Service Name (Default '%')
|
|
* 6 - Where Clause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 20-MAR-15 Vishal Gupta Modified input parameter and renamed script
|
|
* from ash_sid_details.sql to ash_sessions.sql
|
|
* 03-FEB-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE HOURS
|
|
UNDEFINE DURATION
|
|
UNDEFINE USERNAME
|
|
UNDEFINE PROGRAM
|
|
UNDEFINE SERVICE_NAME
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE HOURS="&&1"
|
|
DEFINE DURATION="&&2"
|
|
DEFINE USERNAME="&&3"
|
|
DEFINE PROGRAM="&&4"
|
|
DEFINE SERVICE_NAME="&&5"
|
|
DEFINE WHERECLAUSE="&&6"
|
|
|
|
COLUMN _HOURS NEW_VALUE HOURS NOPRINT
|
|
COLUMN _DURATION NEW_VALUE DURATION NOPRINT
|
|
COLUMN _USERNAME NEW_VALUE USERNAME NOPRINT
|
|
COLUMN _PROGRAM NEW_VALUE PROGRAM NOPRINT
|
|
COLUMN _SERVICE_NAME NEW_VALUE SERVICE_NAME NOPRINT
|
|
COLUMN _SERVICE_NAME NEW_VALUE SERVICE_NAME NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE(UPPER('&&HOURS'),'','1',UPPER('&&HOURS')) "_HOURS"
|
|
, DECODE(UPPER('&&DURATION'),'','60',UPPER(DURATION)) "_DURATION"
|
|
, DECODE(UPPER('&&USERNAME'),'','%',UPPER('&&USERNAME')) "_USERNAME"
|
|
, DECODE(UPPER('&&PROGRAM'),'','%',UPPER('&&PROGRAM')) "_PROGRAM"
|
|
, DECODE(UPPER('&&SERVICE_NAME'),'','%',UPPER('&&SERVICE_NAME')) "_SERVICE_NAME"
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
/***********************************/
|
|
|
|
|
|
PROMPT ***************************************************
|
|
PROMPT * ASH SQL Details
|
|
PROMPT *
|
|
PROMPT * Input Parameter:
|
|
PROMPT * Number of Hours = "&&HOURS"
|
|
PROMPT * SQLExec Duration = "&&DURATION" seconds
|
|
PROMPT * DB UserName = "&&USERNAME"
|
|
PROMPT * Program = "&&PROGRAM"
|
|
PROMPT * ServiceName = "&&SERVICE_NAME"
|
|
PROMPT * WhereClause = "&&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 a18
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a21
|
|
COLUMN program HEADING "Program" FORMAT a25 TRUNCATE
|
|
COLUMN machine HEADING "Machine" FORMAT a25 TRUNCATE
|
|
COLUMN module HEADING "Module" FORMAT a25 TRUNCATE
|
|
|
|
-- Get the SQL Statements from ASH
|
|
SELECT /*+ parallel(ash, 10) */
|
|
--ash.sql_exec_id,
|
|
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#
|
|
, 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
|
|
, u.username
|
|
, s.name service_name
|
|
, NVL(ash_parent.program,ash.program) program
|
|
, ash.machine
|
|
, ash.module
|
|
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.sample_time > sysdate - (&&HOURS/24)
|
|
AND (ash_parent.sample_time IS NULL or ash_parent.sample_time > sysdate - (&&HOURS/24))
|
|
AND ash.sql_exec_id IS NOT NULL
|
|
AND (cast(ash.sample_time as date) - ash.sql_exec_start)* 24*60*60 > '&&DURATION'
|
|
-- AND ash.user_id = (select user_id from dba_users where username LIKE '&&USERNAME')
|
|
-- AND ash.service_hash = (select name_hash from dba_services where name LIKE '&&SERVICE_NAME')
|
|
AND ash.program LIKE '&&PROGRAM'
|
|
&&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)
|
|
, ash.machine
|
|
, ash.module
|
|
ORDER BY MIN(ash.sample_time) ASC
|
|
, max(ash.sample_time) asc
|
|
;
|
|
|
|
|
|
@@footer
|