고민의 흔적

AWR

Oracle 상태가 어떤지 분석할 때 가장 많이 사용하는 방법입니다.

vi 파일 수정(꼭 할 필요는 없음)

vi ~/.zshrc # vi로 열어서 아래의 내용을 붙여 넣는다

#Terminal Prompt
export PS1="%F{cyan}%n@%m%f %F{green}%1~%f %# "

#SQL Developer
export SQLPATH=/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin
alias sqld="cd $SQLPATH"
alias sqlp="$SQLPATH/sqldeveloper &"
alias sqlpe="$SQLPATH/sqldeveloper --AddVMOption=-Duser.language=en &"

#VirtualBox Linux
alias strdb="ssh oracle@192.168.0.111"
alias strdbf="sftp oracle@192.168.0.111"

#Safari
alias safari="open /Applications/Safari.app"

#AWR 환경에 맞는 폴더를 만들어서 입력한다.
alias AWRD="cd /Volumes/EXT1TB/AWR"

# Lang Set
#export LANG=en_US
#export LANG=ko_KR.UTF-8

source ~/.zshrc

AWR 분석에 가장 중요한 기본 분석 주기(DEFUALT 1시간에 8일)

SELECT
         DBID
       , SNAP_INTERVAL
       , RETENTION
       , TOPNSQL
FROM DBA_HIST_WR_CONTROL;

분석 주기 변경

-- 10분 단위 (보관주기 30일)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>30*24*60);

-- 10분 단위 (보관주기 90일)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>90*24*60);

-- 10분 단위 (보관주기 30일)(TOPNSQL 100)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>30*24*60, TOPNSQL=>'100');

-- 10분 단위 (보관주기 90일)(TOPNSQL 100) -> 업계에서 많이 사용
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>10, RETENTION=>90*24*60, TOPNSQL=>'100');

-- 30분 단위 (보관주기 90일)(TOPNSQL 100)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>30, RETENTION=>90*24*60, TOPNSQL=>'100');

-- Default값 변경(60분 단위 8일 보관)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>8*24*60, TOPNSQL=>'DEFAULT');

TOPNSQL Default값 분석

SELECT *
FROM V$PARAMETER
WHERE NAME = 'statistics_level';

만약 TYPICAL이면 값이 30이고 ALL이면 100이다.

AWR 보관 주기 관련해서 가장 중요한 건 SYSAUX 테이블스페이스 공간이 충분이 확보되어야 한다. AWR의 interval을 짧게 주고 retension을 길게 주면 빠른 속도로 데이터가 늘어나기 때문에 꼭 확인해주어야 한다.

SYSAUX TABLESPACE 상태, 사이즈 확인

SELECT 
    TABLESPACE_NAME, 
    FILE_NAME, 
    BYTES/1024/1024 AS "SIZE(GB)" 
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX'
ORDER BY 1, 2;

SNAP_ID 분석

SELECT
    DBID,
    SNAP_ID,
    TO_CHAR(END_INTERVAL_TIME, 'YYYY/MM/DD(DY) HH24:MI') AS "END_INTERVAL_TIME"
FROM 
      DBA_HIST_SNAPSHOT
WHERE END_INTERVAL_TIME >= TRUNC(SYSDATE) -1
AND   END_INTERVAL_TIME <  TRUNC(SYSDATE) +1
ORDER BY SNAP_ID;

AWR 보고서 디렉터리 생성

cd /home/oracle
mkdir AWR

AWR 보고서 생성

-- 리눅스 서버 접속(sqlplus / as ysdba 실행)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@/oracle/app/oracle/product/19.0.0.0/db_1/rdbms/admin/awrrpt.sql

AWR 비교 보고서 생성

-- 리눅스 서버 접속(sqlplus / as ysdba 실행)
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
@/oracle/app/oracle/product/19.0.0.0/db_1/rdbms/admin/awrddrpt.sql

AWR 보고서에서 가장 많이 보는 건 오라클 이벤트를 확인하고 어디에 문제 있는지 분석하고 TOPSQL을 확인하는 게 중요하다. AWR에서 가장 중요한 건 서버에 들어갈 AWR 일자와 시간을 어떻게 뽑느냐가 관건이다. AWR interval을 짧게 주어야 보다 정확하게 분석이 가능하다.

참고

https://www.youtube.com/watch?v=_j8BHtFVRB0