오라클 실행계획과 통계정보 백업

오라클(Oracle) 데이터베이스에서 실행계획(Execution Plan)과 통계정보(Statistics)를 백업하는 방법은 아래와 같이 정리할 수 있습니다.

---

1. 실행계획(Execution Plan) 백업

오라클에서 실행계획을 백업하는 방법은 AUTOTRACE, EXPLAIN PLAN, DBMS_XPLAN 패키지 등을 활용할 수 있습니다.

1-1. EXPLAIN PLAN을 사용한 실행계획 저장

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  • 위 SQL을 실행하면 PLAN_TABLE에 실행계획이 저장됩니다.
  • 실행계획을 백업하려면 PLAN_TABLE 데이터를 저장해야 합니다.

1-2. 실행계획 백업 (PLAN_TABLE 데이터 저장)

CREATE TABLE plan_table_backup AS SELECT * FROM plan_table;

  • 실행계획을 plan_table_backup 테이블에 백업.

1-3. 실행계획을 파일로 저장

SPOOL execution_plan_backup.txt
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SPOOL OFF;

  •  실행계획을 execution_plan_backup.txt 파일로 저장

---

2. 통계정보(Statistics) 백업

오라클에서 테이블, 인덱스 등의 통계정보를 백업하는 방법은 DBMS_STATS 패키지를 사용합니다.

2-1. 전체 스키마 통계 백업

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(ownname => 'SYSTEM', stattab => 'stats_backup');
  DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'HR', stattab => 'stats_backup', statown => 'SYSTEM');
END;
/

   • HR 스키마의 통계정보를 SYSTEM.stats_backup 테이블에 저장.

2-2. 특정 테이블 통계 백업

BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES', stattab => 'stats_backup', statown => 'SYSTEM');
END;
/

  • HR.EMPLOYEES 테이블의 통계를 stats_backup 테이블에 저장.

2-3. 통계정보 파일로 저장 (Data Pump 사용)

expdp system/password directory=DATA_PUMP_DIR dumpfile=stats_backup.dmp tables=stats_backup

  • stats_backup 테이블을 Data Pump로 덤프 파일로 백업.

---

3. 실행계획 및 통계정보 복원 방법

백업한 실행계획과 통계정보를 복원하려면 다음과 같은 방법을 사용합니다.

3-1. 실행계획 복원
• PLAN_TABLE_BACKUP에서 실행계획을 복사

INSERT INTO plan_table SELECT * FROM plan_table_backup;
COMMIT;

3-2. 통계정보 복원
   • 스키마 단위 통계 복원

BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => 'HR', stattab => 'stats_backup', statown => 'SYSTEM');
END;
/

  • 테이블 단위 통계 복원

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES', stattab => 'stats_backup', statown => 'SYSTEM');
END;
/

  • Data Pump를 사용한 복원

---

4. 자동 백업 스케줄링 (Crontab 활용)

4-1. 실행계획 및 통계 백업 스크립트 (backup_stats.sql)

SPOOL /backup/execution_plan_backup.txt
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SPOOL OFF;

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(ownname => 'SYSTEM', stattab => 'stats_backup');
  DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'HR', stattab => 'stats_backup', statown => 'SYSTEM');
END;
/
EXIT;

4-2. 쉘 스크립트 (backup_oracle_stats.sh)

#!/bin/bash
sqlplus system/password@ORCL @/backup/backup_stats.sql
expdp system/password directory=DATA_PUMP_DIR dumpfile=stats_backup_$(date +%Y%m%d).dmp tables=stats_backup

4-3. Crontab 등록 (매일 자정 백업)

0 0 * * * /backup/backup_oracle_stats.sh