DBA Tools

    The "ANYSQL_OMON_RAW.log" of oramon generated files recorded some performance data for chart type display, but we need to load them into database first. You should create a table with the following structure.

CREATE TABLE DATABASE_PERF_STATISTICS
(
  SID      VARCHAR2(10) NOT NULL,
  day      DATE NOT NULL,
  load    NUMBER(10,2), -- OS 1 Minute Load Average
  cpusys  NUMBER(12),  -- Kernel CPU Percent
  cpuwio  NUMBER(12),  -- IO Wait CPU Percent
  cpuusr  NUMBER(12),  -- User Process CPU Percent
  net      NUMBER(12),  -- Network Traffic (in + out)
  pgio    NUMBER(12),  -- Swap In + Swap Out
  act      NUMBER(12),  -- Active Session
  enq      NUMBER(12),  -- Active Session on Enqueue
  pq      NUMBER(12),  -- Active Parallel Slaves
  log      NUMBER(12),  -- New Session Logon
  ocpu    NUMBER(12),  -- CPU time used by this session
  cget    NUMBER(12),  -- Consistent Gets
  bget    NUMBER(12),  -- db block get
  read    NUMBER(12),  -- Physical Reads
  writ    NUMBER(12),  -- Physical Writes
  exec    NUMBER(12),  -- Execute Count
  cmmt    NUMBER(12),  -- User Commits
  redo    NUMBER(12),  -- Redo Size
  rwrt    NUMBER(12),  -- Redo Write Time
  rswt    NUMBER(12),  -- Redo Space Wait Time
  sent    NUMBER(12),  -- SQL*Bytes send by Network
  sort    NUMBER(12),  -- Sorts (memory)
  read1c  NUMBER(12),  -- sequential read wait count
  read1t  NUMBER(12),  -- sequential read wait time
  readnc  NUMBER(12),  -- scatter read wait count
  readnt  NUMBER(12),  -- scatter read wait time
  fwritc  NUMBER(12),  -- db file parallel write count
  fwritt  NUMBER(12),  -- db file parallel write time
  lwritc  NUMBER(12),  -- log file parallel write count
  lwritt  NUMBER(12)    -- log file parallel write time
);

    If the target database is Oracle, we can use scanomon utility to load the generated data in real time, by running it as a background job.

$ scanomon.bin sid=test user=dbajobs/dbajobs@tooldb \
      data=ANYSQL_OMON_RAW.log lock=scanomon.dat
Continue from offset=0
Reopen performance data file, ANYSQL_OMON_RAW.log
Move forward to offset=168, time=2009-04-15 09:59:48
Move forward to offset=330, time=2009-04-15 09:59:58
Move forward to offset=500, time=2009-04-15 10:00:08
Move forward to offset=669, time=2009-04-15 10:00:18
Move forward to offset=837, time=2009-04-15 10:00:28
......

    If we query the database, you will find performance data loaded in real time.

dbajobs@TOOLDB>select count(*) from DATABASE_PERF_STATISTICS;

  COUNT(*)
----------
      2803

    Now it easy to display the performance data with WebChart utility for further digg.