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.