@@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