Oracle VMSTAT을 이용한 CPU 사용량 활용법

 

DB Version : Oracle 11.2.0.2

OS Version : AIX 6.1

 

 

 

 

(1). Table 생성

 - DB User : SYSTEM

 - Table : VMSTAT (Compress BASIC)

 - Tablespace : NEW_TABLESPACE

CREATE TABLE SYSTEM.VMSTAT
(
  HOSTNAME    VARCHAR2(30 BYTE),
  TIMESTAMP   DATE,
  KTHR_R      VARCHAR2(30 BYTE),
  KTHR_B      VARCHAR2(30 BYTE),
  MEMORY_AVM  VARCHAR2(30 BYTE),
  MEMORY_FRE  VARCHAR2(30 BYTE),
  PAGE_RE     VARCHAR2(30 BYTE),
  PAGE_PI     VARCHAR2(30 BYTE),
  PAGE_PO     VARCHAR2(30 BYTE),
  PAGE_FR     VARCHAR2(30 BYTE),
  PAGE_SR     VARCHAR2(30 BYTE),
  PAGE_CY     VARCHAR2(30 BYTE),
  FAULTS_IN   VARCHAR2(30 BYTE),
  FAULTS_SY   VARCHAR2(30 BYTE),
  FAULTS_CS   VARCHAR2(30 BYTE),
  CPU_US      VARCHAR2(30 BYTE),
  CPU_SY      VARCHAR2(30 BYTE),
  CPU_ID      VARCHAR2(30 BYTE),
  CPU_WA      VARCHAR2(30 BYTE)
)
TABLESPACE NEW_TABLESPACE
COMPRESS BASIC;

CREATE INDEX SYSTEM.VMSTAT_N1 ON SYSTEM.VMSTAT
(TIMESTAMP, HOSTNAME)
TABLESPACE NEW_TABLESPACE;

 

 

 

(2). Shell Script 내용 작성

  - Script Directory : /oracle/DBAWORK/DB_JOB/

  - Shell Script Name : insert_vm.sql

###################################################################
#  VMSTAT CPU Usage Insert Script
###################################################################

export ORACLE_SID=ORCL
export ORACLE_UNQNAME=ORCL
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.2
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/ccs/bin:/usr/ucb:$PATH
export ORACLE_OWNER=oracle
export DATE=`date +'%Y%m%d%H%M%S'`
export HOST=`hostname`
export OUTPUT=/oracle/DBAWORK/DB_JOB/insert_vm.sql

echo " insert into system.vmstat values ('${HOST}',sysdate,`vmstat 1 2 | tail -1 | awk '/ /{print $1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14","$15","$16","$17 }' `) ; " > $OUTPUT

sqlplus / as sysdba <<EOF
@/oracle/DBAWORK/DB_JOB/insert_vm.sql
commit;
EOF

 

 

 

(3). OS에 contrab 등록

  - 30s 단위로 저장할수 있게 crontab이 등록

  - chmod 변경

## vmstat information
* * * * * /oracle/DBAWORK/DB_JOB/insert_vm.sh & sleep 30; /oracle/DBAWORK/DB_JOB/insert_vm.sh >> /dev/null

vi /oracle/DBAWORK/DB_JOB/insert_vm.sh
chmod 700 /oracle/DBAWORK/DB_JOB/insert_vm.sh