213 lines
11 KiB
SQL
213 lines
11 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display tempspace usage from ASH
|
|
* Compatibility : 10.1 and above
|
|
* Parameters : 1 - Number of hours (Default 24)
|
|
* 2 - SPACE_MB (Default 500MB)
|
|
* 3 - TEMPORARY_TABLESPACE (Default %)
|
|
* 4 - WhereClause
|
|
* 5 - WhereClause2
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-JUL-16 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE HOURS
|
|
UNDEFINE SPACE_MB
|
|
UNDEFINE TEMP_TABLESPACE
|
|
UNDEFINE WHERECLAUSE
|
|
UNDEFINE WHERECLAUSE2
|
|
|
|
DEFINE HOURS="&&1"
|
|
DEFINE SPACE_MB="&&2"
|
|
DEFINE TEMP_TABLESPACE="&&3"
|
|
DEFINE WHERECLAUSE="&&4"
|
|
DEFINE WHERECLAUSE2="&&5"
|
|
|
|
COLUMN _HOURS NEW_VALUE HOURS NOPRINT
|
|
COLUMN _SPACE_MB NEW_VALUE SPACE_MB NOPRINT
|
|
COLUMN _TEMP_TABLESPACE NEW_VALUE TEMP_TABLESPACE NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&HOURS' ,'','24' ,'&&HOURS') "_HOURS"
|
|
, DECODE('&&SPACE_MB' ,'','500','&&SPACE_MB') "_SPACE_MB"
|
|
, DECODE('&&TEMP_TABLESPACE','','%' ,UPPER('&&TEMP_TABLESPACE')) "_TEMP_TABLESPACE"
|
|
FROM DUAL;
|
|
set term on
|
|
/***********************************/
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
DEFINE COUNT_FORMAT=99,999
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="#1000"
|
|
|
|
|
|
DEFINE BYTES_FORMAT=99,999
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024"
|
|
--DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
DEFINE BYTES_HEADING="GB"
|
|
|
|
/************************************/
|
|
|
|
|
|
|
|
PROMPT ***************************************************
|
|
PROMPT * ASH - High Temp space using SQL statements
|
|
PROMPT *
|
|
PROMPT * Input Parameter:
|
|
PROMPT * Number of Hours = "&&HOURS"
|
|
PROMPT * Space Threshold (MB) = "&&SPACE_MB"
|
|
PROMPT * Temporary Tablespace = "&&TEMP_TABLESPACE"
|
|
PROMPT * Where Clause = "&&WHERECLAUSE"
|
|
PROMPT * Where Clause2 = "&&WHERECLAUSE2"
|
|
PROMPT ***************************************************
|
|
|
|
COLUMN session_id HEADING "SID" FORMAT 99999
|
|
COLUMN instance_number HEADING "I#" FORMAT 99
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN "session_serial#" HEADING "Serial#" FORMAT 999999
|
|
COLUMN FORCE_MATCHING_SIGNATURE HEADING "Force|Matching|Signature" FORMAT 99999999999999999999999
|
|
COLUMN sql_plan_hash_value HEADING "Plan|Hash|Value" FORMAT 9999999999
|
|
COLUMN sql_exec_start FORMAT a18
|
|
COLUMN sql_exec_end FORMAT a18
|
|
COLUMN duration FORMAT a12
|
|
COLUMN sql_opname HEADING "SQL|Operation" FORMAT a10 TRUNCATE
|
|
COLUMN sql_child_number HEADING "SQL|Ch#" FORMAT 999
|
|
COLUMN current_dop HEADING "DOP" FORMAT 999
|
|
COLUMN phyread_requests HEADING "Phy|Read|Reqs|(&&COUNT_HEADING)" FORMAT 999999
|
|
COLUMN phywrite_requests HEADING "Phy|Write|Reqs|(&&COUNT_HEADING)" FORMAT 999999
|
|
COLUMN phyread HEADING "Phy|Read|(&&BYTES_HEADING)" FORMAT 9999
|
|
COLUMN phywrite HEADING "Phy|Write|(&&BYTES_HEADING)" FORMAT 9999
|
|
COLUMN interconnect_io HEADING "Inter|Connect|IO|(&&BYTES_HEADING)" FORMAT 9999
|
|
COLUMN pga_allocated HEADING "PGA|(&&BYTES_HEADING)" FORMAT 999.00
|
|
COLUMN temp_space_allocated HEADING "Temp|Space|(&&BYTES_HEADING)" FORMAT 9999.00
|
|
COLUMN temp_tbs HEADING "Temp TBS" FORMAT A30
|
|
|
|
-- Get the SQL Statements from ASH
|
|
SELECT
|
|
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(NVL(ash.sql_exec_start,MIN(ash.sample_time)),'DD-MON-YY HH24:MI:SS') sql_exec_start
|
|
, TO_CHAR(max(ash.sample_time) ,'DD-MON-YY HH24:MI:SS') sql_exec_end
|
|
, SUBSTR(REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'00000000','')
|
|
,1,INSTR(REPLACE(max(ash.sample_time) - NVL(ash.sql_exec_start,MIN(ash.sample_time)),'00000000',''),'.')-1
|
|
) duration
|
|
, (select temporary_tablespace from dba_users where user_id = ash.user_id) temp_tbs
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.temp_space_allocated)/&&BYTES_DIVIDER,2) temp_space_allocated
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.pga_allocated)/&&BYTES_DIVIDER,2) pga_allocated
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_read_io_requests)/&&COUNT_DIVIDER) phyread_requests
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_write_io_requests)/&&COUNT_DIVIDER) phywrite_requests
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_read_io_bytes)/&&BYTES_DIVIDER) phyread
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_write_io_bytes)/&&BYTES_DIVIDER) phywrite
|
|
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(ash.delta_interconnect_io_bytes)/&&BYTES_DIVIDER) interconnect_io
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
|
|
--, ash.sql_exec_id
|
|
, ash.sql_id
|
|
-- , ash.sql_child_number
|
|
-- , ash.sql_plan_hash_value
|
|
--&&_IF_ORA_11202_OR_HIGHER , max(current_dop) current_dop
|
|
-- , ash.force_matching_signature
|
|
-- , ash.top_level_sql_id
|
|
FROM
|
|
(
|
|
SELECT /*+ parallel(ash, 10) LEADING(ash) */
|
|
ash.session_id
|
|
, ash.inst_id
|
|
, ash.session_serial#
|
|
, ash.qc_session_id
|
|
, ash.qc_instance_id
|
|
, ash.qc_session_serial#
|
|
, NVL(ash.sql_exec_start,MIN(ash.sample_time)) sql_exec_start
|
|
, max(ash.sample_time) sample_time
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
|
|
, ash.user_id
|
|
, ash.sql_exec_id
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_plan_hash_value
|
|
&&_IF_ORA_11202_OR_HIGHER , max(trunc(ash.px_flags / 2097152)) current_dop
|
|
, ash.force_matching_signature
|
|
, NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id) top_level_sql_id
|
|
&&_IF_ORA_11gR1_OR_HIGHER , SUM(ash.delta_read_io_requests) delta_read_io_requests
|
|
&&_IF_ORA_11gR1_OR_HIGHER , SUM(ash.delta_write_io_requests) delta_write_io_requests
|
|
&&_IF_ORA_11gR1_OR_HIGHER , SUM(ash.delta_read_io_bytes) delta_read_io_bytes
|
|
&&_IF_ORA_11gR1_OR_HIGHER , SUM(ash.delta_write_io_bytes) delta_write_io_bytes
|
|
&&_IF_ORA_11gR1_OR_HIGHER , SUM(ash.delta_interconnect_io_bytes) delta_interconnect_io_bytes
|
|
&&_IF_ORA_11gR1_OR_HIGHER , MAX(ash.pga_allocated) pga_allocated
|
|
&&_IF_ORA_11gR1_OR_HIGHER , MAX(ash.temp_space_allocated) temp_space_allocated
|
|
FROM gv$active_session_history ash
|
|
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
|
|
AND ash_parent.sample_time > sysdate - (&&HOURS/24)
|
|
WHERE ash.sql_id IS NOT NULL
|
|
AND ash.sample_time > sysdate - (&&HOURS/24)
|
|
AND ('&&TEMP_TABLESPACE' = '%'
|
|
OR ('&&TEMP_TABLESPACE' != '%'
|
|
AND ash.user_id IN (select user_id from dba_users where temporary_tablespace LIKE '&&TEMP_TABLESPACE')
|
|
)
|
|
)
|
|
AND ash.sql_exec_id IS NOT NULL
|
|
&&WHERECLAUSE &&WHERECLAUSE2
|
|
GROUP BY ash.session_id
|
|
, ash.inst_id
|
|
, ash.session_serial#
|
|
, ash.qc_session_id
|
|
, ash.qc_instance_id
|
|
, ash.qc_session_serial#
|
|
, ash.user_id
|
|
, ash.sql_exec_id
|
|
, ash.sql_exec_start
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_plan_hash_value
|
|
, ash.force_matching_signature
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
|
|
, NVL(ash_parent.top_level_sql_id,ash.top_level_sql_id)
|
|
) ash
|
|
WHERE 1=1
|
|
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#)
|
|
, ash.user_id
|
|
, ash.sql_exec_id
|
|
, ash.sql_exec_start
|
|
, ash.sql_id
|
|
, ash.sql_child_number
|
|
, ash.sql_plan_hash_value
|
|
, ash.force_matching_signature
|
|
&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
|
|
, ash.top_level_sql_id
|
|
HAVING MAX(ash.temp_space_allocated)/power(1024,2) > &&SPACE_MB -- Temp Space MBs
|
|
ORDER BY max(ash.sample_time) asc
|
|
, NVL(ash.sql_exec_start,MIN(ash.sample_time)) ASC
|
|
;
|
|
|
|
|
|
@@footer
|