Oracle 데이터베이스 통계 복원 완벽 가이드: dbms

이 글에서는 Oracle® 데이터베이스에서 통계를 복원해야 하는 시점과 구체적인 복원 방법을 살펴봅니다.

소개

데이터베이스 관리자(DBA)라면 새로 통계를 수집한 뒤 옵티마이저가 오히려 비효율적인 실행 계획을 수립하는 경우를 자주 접하게 됩니다. 이럴 때 성능이 양호했던 과거 시점의 통계로 되돌리고 싶어질 수 있습니다.

다만 Oracle 데이터베이스 버전에 따라 통계 처리 방식에 약간의 차이가 있습니다.

Oracle 10g: 통계를 자동으로 보존하기 시작해 손쉽게 복원할 수 있게 되었습니다.
11.1 이상: 더 나은 방식이 도입되어 통계 게시(publishing)를 지연(defer)할 수 있습니다.

통계 수집 후 성능이 저하되는 원인

옵티마이저가 최적의 실행 계획을 선택하도록 하려면 통계 수집이 필요합니다. 그러나 통계를 수집하면 SQL 문장의 파싱된 결과가 무효화(invalidate)됩니다. 또한 통계 수집 후 문장을 재파싱하는 과정에서 옵티마이저가 기존 계획과 다르고 최적화 수준이 낮은 실행 계획을 선택할 수 있습니다.

Oracle 10g 이상에서는 dbms_stats 패키지를 사용해 통계를 복원할 수 있으며, 이 패키지는 통계 복원(restoration)과 통계 내보내기(export) 기능을 모두 제공합니다.

기본적으로 Oracle은 과거 통계를 31일간 보존하며, 이 보존 기간은 변경할 수 있습니다.

다음 이미지는 현재 보존 기간을 확인하는 SQL 명령입니다.

Oracle 데이터베이스 통계 복원 완벽 가이드: dbms_stats 패키지 활용법

보존 기간을 변경하려면 아래 명령을 실행합니다. 여기서 xx는 설정하고자 하는 일수입니다.

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

다음 쿼리를 실행하면 어떤 시점의 과거 통계를 복원할 수 있는지 파악할 수 있습니다.

참고: 이 예제는 앞서 언급한 날짜 이후의 통계를 보여줍니다.

테이블 통계 복원

다음 예제는 특정 과거 시점의 테이블 통계를 복원하는 방법을 보여줍니다.

먼저 다음 명령을 실행해 어떤 통계가 저장되어 있는지 확인합니다.

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name like 'MY_TABLE' and owner='MYSELF' order by 2;

TABLE_NAME      STATS_UPDATE_TIME
--------------- --------------------------------------
MY_TABLE        20-DEC-19 05.32.26.887184 AM -05:00
MY_TABLE        20-DEC-19 10.10.19.361091 PM -05:00
MY_TABLE        21-DEC-19 05.32.14.475934 AM -05:00
MY_TABLE        21-DEC-19 10.10.18.725917 PM -05:00
MY_TABLE        22-DEC-19 10.10.17.841143 PM -05:00
MY_TABLE        23-DEC-19 05.32.56.168779 AM -05:00
MY_TABLE        23-DEC-19 10.10.23.633939 PM -05:00
MY_TABLE        24-DEC-19 05.32.14.082730 AM -05:00
MY_TABLE        24-DEC-19 10.10.21.712948 PM -05:00
MY_TABLE        25-DEC-19 05.32.13.710159 AM -05:00
MY_TABLE        25-DEC-19 10.10.17.836929 PM -05:00
MY_TABLE        26-DEC-19 05.32.14.545533 AM -05:00
MY_TABLE        26-DEC-19 10.10.12.808687 PM -05:00
MY_TABLE        27-DEC-19 05.32.13.779967 AM -05:00

출력 결과를 보면 MY_TABLE이 지난 며칠 동안 여러 차례 분석된 것을 알 수 있습니다. 21-DEC-19 10.10.18.725917 PM -05:00에 수집된 통계로 복원하려면 다음 명령을 실행합니다.

SQL> execute dbms_stats.restore_table_stats('MYSELF','MY_TABLE','21-DEC-19 10.10.18.725917 PM -05:00');

PL/SQL procedure successfully completed.

스키마 통계 복원

다음 예제는 특정 과거 시점의 스키마 통계를 복원하는 방법입니다.

21-DEC-19 10.10.18.725917 PM -05:00에 수집된 통계로 복원하려면 다음 명령을 실행합니다.

SQL> exec dbms_stats.restore_schema_stats(ownname=>'MYSELF', AS_OF_TIMESTAMP=>'21-DEC-19 10.10.18.725917 PM -05:00');

AS_OF_TIMESTAMP에 지정할 수 있는 스키마 통계 시점을 확인하려면 다음 명령을 실행한 후 복원할 적절한 날짜를 선택합니다.

select count(*), stats_update_time from dba_tab_stats_history where owner='MYSELF' group by stats_update_time;

그 외 복원 가능한 통계

지금까지 테이블과 스키마 통계를 복원하는 방법을 살펴봤습니다. 다음 목록은 과거 통계를 복원할 수 있는 모든 개체(entity)를 보여줍니다.

TABLE_STATS
SCHEMA_STATS
DATABASE_STATS
DICTIONARY_STATS
FIXED_OBJECTS_STATS
SYSTEM_STATS

보존할 통계 내보내기

저장해 두고 싶은 통계를 내보내거나, 변경 작업을 진행하기 전에 현재 통계를 백업 차원에서 내보낼 수도 있습니다. 다음 단계를 따릅니다.

아래와 유사한 명령으로 통계 테이블을 생성합니다.
```
Exec dbms_stats.create_stat_table(ownname => 'MYSELF', stattab => 'MYSELF_STATS_<DATE>', tblspace => '<Tablespace Name>');
```
ownname: 소유자(owner) 이름
stattab: MYSELF 사용자 아래에 생성할 테이블 이름
tblspace: 해당 테이블을 생성할 테이블스페이스

앞서 생성한 테이블에 통계를 내보냅니다.

exec dbms_stats.export_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);

예를 들어, 데이터베이스 전체 통계를 내보내려면 다음과 같이 실행합니다.

Exec dbms_stats.export_database_stats(statown => 'MYSELF', stattab => 'MYSELF_STATS');

결론

이 글에서 소개한 정보와 쿼리를 활용하면 모든 유형의 데이터베이스 통계(테이블, 데이터베이스, 스키마, Fixed_Object, System, Dictionary)를 합리적인 과거 시점으로 복원해 데이터베이스 성능을 안정적으로 유지할 수 있습니다.

궁금한 점이나 의견이 있다면 피드백 탭을 통해 남겨 주세요. 지금 바로 채팅으로 대화를 시작할 수도 있습니다.

데이터베이스 서비스에 대해 더 자세히 알아보세요.

Oracle 데이터베이스 통계 복원 완벽 가이드: dbms_stats 패키지 활용법

소개

통계 수집 후 성능이 저하되는 원인

테이블 통계 복원

스키마 통계 복원

그 외 복원 가능한 통계

보존할 통계 내보내기

결론