Oracle 10g 또는 Oracle 11g는 AWR(Automatic Workload Repository)에 의해 상시적으로 성능데이터 수집이 진행되고 있으며, 기본적으로 최근 일주일의 성능데이터를 항상 유지하고 있다.

AWR의 성능데이터는 Oracle 10g부터 신규 추가된 보조 테이블 스페이스인 SYSAUX 테이블 스페이스에 저장된다. 이것은 오라클 데이터베이스 차원에서 성능데이터를 관리하겠다는 것이다.

또한 이 데이터를 활용해 오라클의 자동 공유 메모리 관리(ASMM) 기능과 EM(Enterprise Manager) 등의 특화되고 효율적인 관리 기능을 사용할 수 있게 된다.

그리고 AWR의 성능 데이터를 활용해 담당 DBA는 최근 일주일 사이에 오라클 데이터베이스에서 어떤 현상이 발생했고, 어떠한 SQL 구문이 수행 되었는지 조회해 볼 수 있고 AWR Report를 통해 특정 기간의 성능 보고서를 추출해 분석할 수 있다.

필자는 오라클 데이터베이스의 성능 진단을 수행할 때 AWR을 충분히 활용한다.

AWR 보고서에서는 일정 기간 동안 가장많은 부하를 발생시키는 SQL을 추출해 보여주는데, 이 SQL만 튜닝해 애플리케이션에 반영시켜도 시스템이 상상 이상으로 성능이 향상되는 것을 많이 경험했다.

하지만 Oracle 9i 이하 버전은 AWR이 존재하지 않는 탓에 상시적인 성능 수집이 진행되지 않는다.

하지만 AWR의 모체가 되는 Statspack 성능 툴킷이 존재하고, 이 성능 툴킷을 설치 수행하므로 성능데이터를 추출해 보관할 수 있다.

Statspack의 성능 보고서 또한 AWR과 비슷한 정보를 제공하며, 담당 DBA는 Statspack 보고서를 통해 효과적으로 오라클 데이터베이스의 성능분석을 수행할 수 있게 된다.

하지만, AWR과 Statspack은 다르다.

AWR은 최신 성능데이터를 특정 기간(기본 일주일) 동안 유지할 수 있지만 Statspack은 그렇지 못하다. 이 차이는 실로 크다고 할 수 있다.

AWR을사용하는 Oracle 10g 또는 Oracle 11g는 성능데이터 누적에 대한 스토리지 부담이 없지만 Statspack을 사용하는 Oracle 9i와 그 이하 버전은 계속 쌓이는 성능데이터에 대한 스토리지 부담을 항상 짊어지고 가야 한다.

이유는 Statspack으로는 특정 기간동안의 성능데이터 유지가 불가능하기 때문이다. 다시 말하면 성능데이터 수집을 시작한 시점부터 데이터를 계속해서 누적하기만 하고 삭제되지 않는다.

물론 담당 DBA가 수동적으로 오래된 성능데이터를 임의로 삭제해 주거나 또는 Statspack 툴킷을 제 거하고 다시 생성해 스케줄링한다면 대안이 될 수 있겠다.

하지만 이 수동 작업은 많은 업무를 수행하는 DBA에게 또 하나의 업무를 늘리는 셈이므로 그다지 권장하지는 않는다.

이에 Statspack의 수집 누적되는 성능데이터를 AWR과 같이 최근의 특정 기간 동안 유지할 수 있는 방법을 소개한다.

원리는 간단하다. PL/SQL을 활용해 원하는 날짜 범위 밖에 유효하지 않은 성능데이터를 삭제하면 된다. 이에 필자가 간단히 PL/SQL을 활용해 오라클 Package를 만들어 보았다. 패키지는 헤더와 바디로 구분되는데, 바디의 내용을 Procedure로 만들어도 상관은 없다.

사용법은 간단하다. 다음의 소스를 오라클 데이터베이스 내에컴파일해 저장시키고, 스케줄링(JOBS)해서 오라클 데이터베이스가 매일 가장 한가한 새벽시간에 한번 수행하도록 하는 것이다.

수행 프로시저의 매개변수에는 최근 며칠간의 성능데이터를 유지할 것인지를 입력한다.

예) SP_MGR.PURGE(7) : 최근 7일간의 성능데이터 유지

/* Statspack purge 패키지 헤더 생성
i_num_days 매개변수는 최근 유지할 날짜 입력 */
CREATE OR REPLACE PACKAGE PERFSTAT.SP_MGR AS
  PROCEDURE PURGE(i_num_days IN NUMBER);
END;
/



/* Statspack purge 패키지 바디 생성 */
CREATE OR REPLACE PACKAGE BODY PERFSTAT.SP_MGR AS
  PROCEDURE PURGE(i_num_days IN NUMBER) IS
    lo_snap     NUMBER;
    hi_snap     NUMBER;
    btime       VARCHAR2(25);
    etime       VARCHAR2(25);
    dbid        NUMBER;
    inst_num    NUMBER;
    inst_name   VARCHAR2(20);
    db_name     VARCHAR2(20);
  BEGIN
    SELECT d.dbid dbid
         , d.name db_name
         , i.instance_number inst_num
         , i.instance_name inst_name
      INTO dbid, db_name, inst_num, inst_name
      FROM V$DATABASE d, V$INSTANCE i;
      
    SELECT MIN(SNAP_ID), MAX(SNAP_ID) 
      INTO lo_snap, hi_snap
      FROM STATS$SNAPSHOT
     WHERE SNAP_TIME < TRUNC(SYSDATE-i_num_days);

    SELECT TO_CHAR(snap_time, 'YYYYMMDD HH24:MI:SS') btime 
      INTO btime
      FROM STATS$SNAPSHOT b
     WHERE b.snap_id = lo_snap
       AND b.dbid = dbid
       AND b.instance_number = inst_num;
       
    SELECT TO_CHAR(snap_time, 'YYYYMMDD HH24:MI:SS') etime 
      INTO etime
      FROM STATS$SNAPSHOT e
     WHERE e.snap_id = hi_snap
       AND e.dbid = dbid
       AND e.instance_number = inst_num;


-- Delete all data for the specified ranges
    DELETE FROM STATS$UNDOSTAT us
     WHERE dbid = dbid
       AND instance_number = inst_num
       AND begin_time < to_date(btime,'YYYYMMDD HH24:MI:SS')
       AND end_time > to_date(etime,'YYYYMMDD HH24:MI:SS');
       
/* Delete any dangling database instance rows for that startup time */
	DELETE FROM STATS$DATABASE_INSTANCE di
	 WHERE instance_number = inst_num
	   AND dbid = dbid
	   AND NOT EXISTS 
	     ( SELECT 1 
	         FROM STATS$SNAPSHOT s
	        WHERE s.dbid = di.dbid
	          AND s.instance_number = di.instance_number
	          AND s.startup_time = di.startup_time
	     );
	
/* Delete any dangling statspack parameter rows for the database instance */
	DELETE FROM STATS$STATSPACK_PARAMETER sp
	 WHERE instance_number = inst_num
	   AND dbid = dbid
	   AND NOT EXISTS 
	     ( SELECT 1 
	         FROM STATS$SNAPSHOT s
	        WHERE s.dbid = sp.dbid
              AND s.instance_number = sp.instance_number
         );
    
    COMMIT;
    
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
      WHEN OTHERS THEN RAISE;
  END;
END;
/

이처럼 어쩔 수 없이 9i 이전의 시스템을 운영하는 환경이라면 10g/11g를 부러워만 하지 말고 성능데이터를 관리해 보는 것은 어떨까.