support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/awr_temp_usage.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

155 lines
8.6 KiB
SQL
Raw Permalink Blame History

@@header
/*
*
* Author : Vishal Gupta
* Purpose : Display tempspace usage from AWR
* 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 by copying query from awr_sid_sqlids.sql
*
*/
/************************************
* 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
/***********************************/
PROMPT ***************************************************
PROMPT * AWR - 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|(#1000)" FORMAT 999999
COLUMN phywrite_requests HEADING "Phy|Write|Reqs|(#1000)" FORMAT 999999
COLUMN phyread HEADING "Phy|Read|(GB)" FORMAT 9999
COLUMN phywrite HEADING "Phy|Write|(GB)" FORMAT 9999
COLUMN interconnect_io HEADING "Inter|Connect|IO|(GB)" FORMAT 9999
COLUMN pga_allocated HEADING "PGA|(GB)" FORMAT 999.00
COLUMN temp_space_allocated HEADING "Temp|Space|(GB)" FORMAT 9999.00
COLUMN temp_tbs HEADING "Temp TBS" FORMAT A30
-- Get the SQL Statements from ASH
SELECT /*+ parallel(ash,8) parallel(ash_parent,8) */
NVL(ash.qc_session_id, ash.session_id) session_id
, NVL(ash.qc_instance_id, ash.instance_number) instance_number
, NVL(ash.qc_session_serial# , ash.session_serial# ) session_serial#
--, ash.sql_exec_id
&&_IF_ORA_11gR1_OR_HIGHER , TO_CHAR(NVL(ash_parent.sql_exec_start,ash.sql_exec_start),'DD-MON-YY HH24:MI:SS') sql_exec_start
, TO_CHAR(max(NVL(ash_parent.sample_time,ash.sample_time)) ,'DD-MON-YY HH24:MI:SS') sql_exec_end
, SUBSTR(REPLACE( max(NVL(ash_parent.sample_time,ash.sample_time) - NVL(ash_parent.sql_exec_start,ash.sql_exec_start) ) ,'+00000000','+')
,1,INSTR( REPLACE( max(NVL(ash_parent.sample_time,ash.sample_time) - NVL(ash_parent.sql_exec_start,ash.sql_exec_start) ) ,'+00000000','+')
,'.')-1
) duration
, (select temporary_tablespace from dba_users where user_id = ash.user_id) temp_tbs
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(NVL(ash_parent.temp_space_allocated,0) + NVL(ash.temp_space_allocated,0) )/power(1024,3),2) temp_space_allocated
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(MAX(NVL(ash_parent.pga_allocated,0) + NVL(ash.pga_allocated,0) )/power(1024,3),2) pga_allocated
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash_parent.delta_read_io_requests,0) + NVL(ash.delta_read_io_requests,0) )/power(1000,1)) phyread_requests
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash_parent.delta_write_io_requests,0) + NVL(ash.delta_write_io_requests,0) )/power(1000,1)) phywrite_requests
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash_parent.delta_read_io_bytes,0) + NVL(ash.delta_read_io_bytes,0) )/power(1024,3)) phyread
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash_parent.delta_write_io_bytes,0) + NVL(ash.delta_write_io_bytes,0) )/power(1024,3)) phywrite
&&_IF_ORA_11gR1_OR_HIGHER , ROUND(SUM(NVL(ash_parent.delta_interconnect_io_bytes,0) + NVL(ash.delta_interconnect_io_bytes,0) )/power(1024,3)) interconnect_io
--&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
, ash.sql_id
-- , ash.sql_child_number
-- , ash.sql_plan_hash_value
--&&_IF_ORA_11202_OR_HIGHER , max(trunc(NVL(ash_child.px_flags,ash.px_flags) / 2097152)) current_dop
-- , ash.force_matching_signature
--&&_IF_ORA_11gR1_OR_HIGHER , NVL(ash.top_level_sql_id,ash_child.top_level_sql_id) top_level_sql_id
FROM v$database d
JOIN sys.wrh$_active_session_history ash
ON ash.dbid = d.dbid
AND ash.sample_time > sysdate - (&&HOURS/24)
&&WHERECLAUSE
LEFT OUTER JOIN sys.wrh$_active_session_history ash_parent
ON ash.dbid = ash_parent.dbid
AND ash.qc_instance_id = ash_parent.instance_number
AND ash.qc_session_id = ash_parent.session_id
AND ash.qc_session_serial# = ash_parent.session_serial#
AND ash_parent.qc_session_id <> 0
&&_IF_ORA_11gR1_OR_HIGHER AND ash.sql_exec_start = ash_parent.sql_exec_start
AND ash_parent.sample_time > sysdate - (&&HOURS/24)
&&WHERECLAUSE2
WHERE 1=1
AND ('&&TEMP_TABLESPACE' = '%'
OR ('&&TEMP_TABLESPACE' != '%'
AND ash.user_id IN (select user_id from dba_users where temporary_tablespace LIKE '&&TEMP_TABLESPACE')
)
)
GROUP BY NVL(ash.qc_session_id, ash.session_id)
, NVL(ash.qc_instance_id, ash.instance_number)
, NVL(ash.qc_session_serial#, ash.session_serial#)
, ash.user_id
&&_IF_ORA_11gR1_OR_HIGHER , ash.sql_exec_id
&&_IF_ORA_11gR1_OR_HIGHER , NVL(ash_parent.sql_exec_start,ash.sql_exec_start)
--&&_IF_ORA_11gR2_OR_HIGHER , ash.sql_opname
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, ash.force_matching_signature
&&_IF_ORA_11gR1_OR_HIGHER , NVL(ash_parent.top_level_sql_id,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
&&_IF_ORA_11gR1_OR_HIGHER , NVL(ash_parent.sql_exec_start,ash.sql_exec_start) ASC
;
@@footer
Reference in New Issue View Git Blame Copy Permalink