Thursday 3 May 2012

symstat output to oracle table

This post is for my own records. A couple of scripts I developed to load symstat output to oracle table for analyzis. Tested on DMX4 with the latest symcli.



symstat -i 60 -type DISK -sid 0990 > symdisks_stat.log

egrep -v "DISK|READ|Total" symdisks_stat.log > src_symdisks_stat.lst



CREATE TABLE DS
(
DISK VARCHAR2(20 BYTE),
IR NUMBER,
IW NUMBER,
KR NUMBER,
KW NUMBER,
TIMESTRING VARCHAR2(25 BYTE)
)


fsymdisks_stat.ctl

LOAD DATA
INFILE 'src_symdisks_stat.lst'
INTO TABLE ds
( TIMESTRING POSITION(1:9) CHAR,
DISK POSITION(10:16) CHAR,
IR POSITION(32:37) char ,
IW POSITION(39:44) CHAR ,
KR POSITION(46:52) CHAR ,
KW POSITION(54:60) CHAR )

--update timestamp
DECLARE
  t  VARCHAR2(15);
BEGIN
  FOR i IN (SELECT ROWID, timestring FROM ds) LOOP
    IF i.timestring IS NULL THEN
      UPDATE ds
         SET timestring = t
       WHERE ROWID = i.ROWID;
    ELSE
      t := i.timestring;
    END IF;
  END LOOP;
END;


WITH Q AS
(SELECT TRUNC(AVG(IW)) AS AVG_IW, SUM(IW) AS TOTAL_IW, TRUNC(AVG(IR)) AS AVG_IR, SUM(IR) AS TOTAL_IR, DISK FROM DS GROUP BY DISK ORDER BY 1 DESC)
SELECT * FROM Q WHERE AVG_IW > 100;

--50000 IS ENOUGH FOR ~7H WORKLOAD
WITH Q AS
(SELECT TRUNC(AVG(IW)) AS AVG_IW, SUM(IW) AS TOTAL_IW, TRUNC(AVG(IR)) AS AVG_IR, SUM(IR) AS TOTAL_IR, DISK FROM DS GROUP BY DISK ORDER BY 1 DESC)
SELECT SUM(TOTAL_IW+TOTAL_IR)||' '||'TOTAL' AS TOTAL_IOS, COUNT(*) AS CNT FROM Q
UNION
SELECT SUM(TOTAL_IW+TOTAL_IR)||' '||'BUSIEST', COUNT(*) FROM Q WHERE TOTAL_IW+TOTAL_IR > 50000;


No comments:

Post a Comment