Sort Usage Procedure

Procedure Code

--=============================================================================
--
-- Title       :   monitor_sort_usage.sql
-- Description :   A script to create a procedure which captures 
--                 sort usage of all users in the database.
--                 The output is stored in the table created by this
--                 script.
--
-- Usage/Notes :   (1)  Must run be run by a user that has access to gv$ views
--
-- Author      :   Mark Ramsay
-- Date        :   3 november 2014
--
-- Version     :   1.00
--
-- Amendment   :
-- History
--
-- Copyright   :   www.markramsay.com 2014
--
--=============================================================================

CONNECT &schema_owner

DROP TABLE monitor_sort_usage
/

CREATE TABLE monitor_sort_usage
(
SAMPLE_TIMESTAMP        TIMESTAMP(6) WITH TIME ZONE,
TABLESPACE              VARCHAR2(31),
TEMP_SIZE_MB            NUMBER,
INSTANCE                NUMBER,
SID_SERIAL              VARCHAR2(81 CHAR),
USERNAME                VARCHAR2(30),
PROGRAM                 VARCHAR2(48),
STATUS                  VARCHAR2(8 CHAR),
SQL_ID                  VARCHAR2(13)
)
TABLESPACE USERS
/

CREATE INDEX monitor_sort_usage_ix1
ON monitor_sort_usage(SAMPLE_TIMESTAMP)
TABLESPACE USERS
/

CREATE OR REPLACE PROCEDURE monitor_sort_usage_proc
 ( t_cleardown IN NUMBER := 60 )    AS
BEGIN

INSERT INTO monitor_sort_usage
     SELECT systimestamp as sample_time,
            b.tablespace,
            ROUND(((b.blocks*p.value)/1024/1024),2) AS temp_size_mb,
            a.inst_id as Instance,
            a.sid||','||a.serial# AS sid_serial,
            NVL(a.username, '(oracle)') AS username,
            a.program,
            a.status,
            a.sql_id
     FROM   gv$session a,
            gv$sort_usage b,
            gv$parameter p
     WHERE  p.name  = 'db_block_size'
     AND    a.saddr = b.session_addr
     AND    a.inst_id=b.inst_id
     AND    a.inst_id=p.inst_id;

DELETE FROM monitor_sort_usage
      WHERE TRUNC(SAMPLE_TIMESTAMP) < TRUNC(SYSDATE-t_cleardown);

END;
/