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