Methodology for gathering time series data of Oracle Instance throughput

I was looking around the Web for a simple way to measure throughput at the instance  level, and by the time I was all Googled out I had not find anything suitable yet. I thus decided to roll my own.

It consists of using the difference in bytes shifted between to points in time to calculate the throughput during the interval so delineated.

Script to create the required tables:

--
-- IO_SAMPLES  (Table) 
--
CREATE TABLE XY90260.IO_SAMPLES
(
  ID                          NUMBER                NULL,
  IO_TIMESTAMP                DATE                  NULL,
  PHYSICAL_READ_IO_REQUESTS   NUMBER                NULL,
  PHYSICAL_WRITE_IO_REQUESTS  NUMBER                NULL,
  PHYSICAL_READ_TOTAL_MBR     NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_MBR    NUMBER                NULL,
  PHYSICAL_READ_TOTAL_BYTES   NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_BYTES  NUMBER                NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_PK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_PK ON XY90260.IO_SAMPLES
(ID)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_UK ON XY90260.IO_SAMPLES
(IO_TIMESTAMP)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLES
--
ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_PK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_UK
 UNIQUE (IO_TIMESTAMP)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID),
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

-
-- IO_SAMPLE_DELTAS  (Table)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE TABLE XY90260.IO_SAMPLE_DELTAS
(
  ID_FROM       NUMBER                              NULL,
  ID_TO         NUMBER                              NULL,
  IO_SPEED_MBS  NUMBER                              NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLE_DELTAS_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLE_DELTAS (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLE_DELTAS_UK ON XY90260.IO_SAMPLE_DELTAS
(ID_FROM, ID_TO)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_UK
 UNIQUE (ID_FROM, ID_TO)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

--
-- Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

The script to take a sample:

CREATE OR REPLACE procedure XY90260.take_io_sample as

  i       number;
  mx      number;
  s       number;
begin

select max(id) into mx from io_samples;

select nvl(max(id),1)+1 into i from io_samples;

INSERT INTO io_samples (
 id
 ,io_timestamp
,physical_read_io_requests
,physical_write_io_requests
,physical_read_total_mbr
,physical_write_total_mbr
,physical_read_total_bytes
,physical_write_total_bytes)
SELECT
i
,
sysdate
,
sum(decode(name,'physical read total IO requests',value,0)
  - decode(name,'physical read total multi block requests',value,0))
  ,
sum(decode(name,'physical write total IO requests',value,0)
  - decode(name,'physical write total multi block requests',value,0)) sw2
  ,
sum(decode(name,'physical read total multi block requests',value,0)) lr2
, sum(decode(name,'physical write total multi block requests',value,0)) lw2
, sum(decode(name,'physical read total bytes',value,0)) tbr2
, sum(decode(name,'physical write total bytes',value,0)) tbw2
FROM v$sysstat;

-- automatically calculate delta with most recent other sample

calc_io_sample_delta(mx,i,s);

dbms_output.put_line(s);

end;
/

The script to do the required scheduling:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'XY90260.TAKE_IO_SAMPLE_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2009/12/20 14:12:19.340308 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=hourly; byminute=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN take_io_sample; END;'
      ,comments        => 'Instance level IO sampling.'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'XY90260.TAKE_IO_SAMPLE_JOB');
END;
/

From which it obviously follows that the following query will give you instance throughput:

select
  to_char(n.io_timestamp,'YYYY-MM-DD:HH24:MI:SS') AS TS, IO_SPEED_MBS
  ,n.PHYSICAL_READ_IO_REQUESTS-prev.PHYSICAL_READ_IO_REQUESTS+n.PHYSICAL_WRITE_IO_REQUESTS-prev.PHYSICAL_WRITE_IO_REQUESTS
    as total_io_requests  
from
  io_samples       n,
  io_samples       prev, 
  io_sample_deltas d
where
  n.id = d.ID_TO
  AND
  prev.id = (select max(id) from io_samples where id < n.id)
order by
  n.io_timestamp;

With a bit of Excel magic you can produce a daily load profile such as: