Tracking TEMP usage throughout the day
I really wanted to do this via AWR, but I’ve not been able to find out if this kind of information is stored and, if it is, how I’d get access to it…maybe someone else knows…hint hint?!
I have various queries I run whilst I’m online and processes are running, but what I really wanted, was to know the usage profile throughout the day…so, given that I couldn’t find an AWR way of tracking our use of TEMP on a database, I figured I’d use a more klunky method…
DROP TABLE mgmt_t_temp_use_history PURGE
/
CREATE TABLE temp_use_history(snap_date DATE NOT NULL
,sid NUMBER NOT NULL
,segtype VARCHAR2(9) NOT NULL
,qcsid NUMBER NULL
,username VARCHAR2(30) NULL
,osuser VARCHAR2(30) NULL
,contents VARCHAR2(9) NULL
,sqlhash NUMBER NULL
,sql_id VARCHAR2(13) NULL
,blocks NUMBER NULL
)
PCTFREE 0
COMPRESS
NOLOGGING
/
CREATE UNIQUE INDEX tuh_pk ON
temp_use_history(snap_date,sid,segtype)
PCTFREE 0
COMPRESS
NOLOGGING
/
ALTER TABLE temp_use_history ADD CONSTRAINT tuh_pk PRIMARY
KEY(snap_date,sid,segtype)
USING INDEX
/
DECLARE
l_program_action VARCHAR2(2000);
l_27477 EXCEPTION;
PRAGMA EXCEPTION_INIT(l_27477,-27477); BEGIN
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'MINUTELY_5M'
,start_date => SYSDATE
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'
,comments =>'Daily schedule to run a job every five minutes.'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the schedule exists
END;
l_program_action := 'DECLARE';
l_program_action := l_program_action||' l_date DATE := SYSDATE; ';
l_program_action := l_program_action||'BEGIN';
l_program_action := l_program_action||' INSERT /*+ APPEND */ INTO temp_use_history(snap_date,sid,qcsid,username,osuser,contents,seg
type,sqlhash,sql_id,blocks)';
l_program_action := l_program_action||' SELECT l_date,s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqlh
ash,su.sql_id,sum(su.blocks)';
l_program_action := l_program_action||' FROM v$sort_usage su';
l_program_action := l_program_action||' , v$session s';
l_program_action := l_program_action||' , v$px_session ps';
l_program_action := l_program_action||' WHERE s.sid=ps.sid(+)';
l_program_action := l_program_action||' AND s.saddr =
su.session_addr';
l_program_action := l_program_action||' AND s.serial# =
su.session_num';
l_program_action := l_program_action||' GROUP BY s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqladdr,su.
sqlhash,su.sql_id;';
l_program_action := l_program_action||' COMMIT; ';
l_program_action := l_program_action||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'SNAP_TEMP_USAGE'
Prior to the bill going into effect, the standard industry practice was to hike canada viagra rates on consumers immediately after an infraction, such as a late payment. If you think that only medicine can improve the quality and prolong life of the individual depend upon understanding the situation and insistent and persistent actions to prevent deteriorations'. canadian pharmacy tadalafil djpaulkom.tv This is absolutely buying viagra on line unfounded since this pill is a treatment for erectile dysfunction. Treatment for male sensual problems: Erectile dysfunction is not something that online viagra most people like to talk about in the open. ,program_type => 'PLSQL_BLOCK'
,program_action => l_program_action
,enabled => TRUE
,comments => 'Program to snap the temp usage into TEMP_USE_HISTORY'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the program exists
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_SNAP_TEMP_USAGE'
,program_name => 'SNAP_TEMP_USAGE'
,schedule_name => 'MINUTELY_5M'
,enabled => TRUE
,auto_drop => FALSE
,comments => 'Job to snap the temp usage into TEMP_USE_HISTORY'
);
EXCEPTION
WHEN l_27477 THEN
NULL; -- Ignore if the job exists
END;
END;
/
I can now run queries against the TEMP_USE_HISTORY table to show how much TEMP has been used, when, by whom and for what use, e.g.
SQL> ed Wrote file afiedt.buf 1 select snap_date,round(sum(blocks)*32/1024/1024) gb 2 from temp_use_history 3 where snap_date > sysdate-1 4 group by snap_date 5 having round(sum(blocks)*32/1024/1024) > 50 6* order by 1 SQL> / SNAP_DATE GB -------------------- ---------- 26-FEB-2008 16:12:25 57 26-FEB-2008 16:17:25 65 26-FEB-2008 16:22:25 74 26-FEB-2008 16:27:25 86 26-FEB-2008 16:32:25 95 26-FEB-2008 16:37:25 107 26-FEB-2008 16:42:25 121 26-FEB-2008 16:47:25 127 26-FEB-2008 16:52:25 147 26-FEB-2008 16:57:25 160 26-FEB-2008 17:02:25 162 26-FEB-2008 17:07:25 179 26-FEB-2008 17:12:25 196 26-FEB-2008 17:17:25 208 26-FEB-2008 17:22:25 217 26-FEB-2008 17:27:25 233 26-FEB-2008 17:32:25 241 26-FEB-2008 17:37:25 251 26-FEB-2008 17:42:25 257 26-FEB-2008 17:47:25 262 26-FEB-2008 17:52:25 264 26-FEB-2008 17:57:25 267 26-FEB-2008 18:02:25 201 27-FEB-2008 00:27:25 59 27-FEB-2008 00:32:25 60 27-FEB-2008 00:37:25 69 27-FEB-2008 01:12:25 57 27-FEB-2008 02:22:25 53 27-FEB-2008 09:57:25 51 29 rows selected.
From the above, I can see that, during the last twenty four hours on the database in question, there was reasonably heavy use of the TEMP area between 4pm and 6pm yesterday, and that the load peaked at approximately 267Gb.