[Oracle] 통계정보를 갱신하는 절차 및 방법
오라클 데이터베이스에서 통계 데이터를 수집해야 하는 가장 근본적인 이유는 CBO(Cost Based Optimizer, 비용 기반 옵티마이저)가 ‘가장 빠른 길’을 찾을 수 있도록 정확한 정보를 제공하기 위해서입니다. 예를 들어, CBO는 쿼리를 실행하기 전, 통계 데이터를 참조하여 인덱스를 탈지 아니면 테이블 전체를 읽을지(Full Scan) 결정합니다.
- 시스템 통계 갱신 (System Statistics)
- 딕셔너리 통계 갱신 (Dictionary Statistics)
- 스키마 통계 갱신 (Schema Statistics)
- 테이블 통계 갱신 (Table Statistics)
- 인덱스 통계 갱신 (Index Statistics)
- 통계정보 갱신 이력 확인
1. 시스템 통계 갱신 (System Statistics)
- 시스템 통계는 매우 신중해야 하는 작업이므로 충분히 검토된 환경에서만 수행하셔야 합니다.
- 데이터베이스 전체 실행 계획에 영향을 미치므로 매우 높은 권한을 요구합니다(
SYS AS SYSDBA계정으로 접속). - 방법 A: 특정 시간 동안 실제 부하를 측정 (Start/Stop 방식), 서버의 실제 피크 타임 성능을 측정할 때 사용.
-- 수집 시작 EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START'); -- (약 30분~1시간 후 서비스 부하가 발생한 뒤) -- 수집 종료 및 반영 EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP'); - 방법 B: 지정된 시간 동안 자동 측정.
-- 60분 동안 서버 성능을 관찰하여 통계 생성 EXEC DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', interval => 60); - 방법 C: 현재 시점의 기본 성능 측정 (NOWORKLOAD). 부하가 없는 상태에서 하드웨어 자체의 기본 성능만 측정(가장 안전).
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD'); - SYS.AUX_STATS$뷰를 통해 시스템 통계가 갱신되었는지 확인합니다.
SQL> SELECT * FROM SYS.AUX_STATS$; SNAME PNAME PVAL1 PVAL2 ------------- ------ ------ ---------------- SYSSTATS_INFO STATUS [NULL] COMPLETED SYSSTATS_INFO DSTART [NULL] 01-16-2026 15:27 SYSSTATS_INFO DSTOP [NULL] 01-16-2026 15:27
2. 딕셔너리 통계 갱신 (Dictionary Statistics)
- SYS, SYSTEM 스키마에 있는 객체들을 대상으로 한 오라클 내부의 메타데이터(테이블 목록, 권한 정보 등)가 저장된 시스템 테이블 자체의 통계입니다.
- 딕셔너리 통계가 너무 낡으면, 우리가 실행하는 DBMS_STATS 명령어 자체가 느려지거나 실행 계획을 세우는 단계(Parsing)에서 시간이 오래 걸릴 수 있습니다.
- 시스템 통계와 마찬가지로, 매우 높은 권한을 요구하기 때문에 반드시
SYS AS SYSDBA계정으로 접속하여 실행해야 합니다.EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
- DBA_TABLES뷰 맟 DBA_INDEXES뷰를 통해 갱신된 정보(LAST_ANALYZED 컬럼)를 확인합니다.
SELECT OWNER, TABLE_NAME, LAST_ANALYZED, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE OWNER = 'SYS' AND TABLE_NAME IN ( -- 객체 및 구조 관리 핵심 테이블 'OBJ$', 'TAB$', 'COL$', 'IND$', -- 저장 공간 및 데이터 배치 관리 'SEG$', 'FET$', 'UET$', 'TS$', -- 사용자 권한 및 보안 관리 'USER$', 'OBJAUTH$', 'SYSAUTH$', -- 옵티마이저 및 성능 관련 'HISTGRM$', 'AUX_STATS$') ORDER BY LAST_ANALYZED DESC; SELECT OWNER, INDEX_NAME, LAST_ANALYZED, DISTINCT_KEYS FROM DBA_INDEXES WHERE OWNER = 'SYS' AND TABLE_NAME IN ( -- 객체 및 구조 관리 핵심 테이블 'OBJ$', 'TAB$', 'COL$', 'IND$', -- 저장 공간 및 데이터 배치 관리 'SEG$', 'FET$', 'UET$', 'TS$', -- 사용자 권한 및 보안 관리 'USER$', 'OBJAUTH$', 'SYSAUTH$', -- 옵티마이저 및 성능 관련 'HISTGRM$', 'AUX_STATS$') ORDER BY LAST_ANALYZED DESC;
3. 스키마 통계 갱신 (Schema Statistics)
- DBMS_STATS.GATHER_SCHEMA_STATS() 옵션 설명
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname => '스키마', options => 'GATHER AUTO', -- 통계가 없는 객체와 낡은(Stale) 객체를 모두 포함, 낡은 것만 골라낼 때는 'GATHER STALE' 사용 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 데이터 분포를 보고 가장 적절한 샘플링 비율을 자동으로 결정 method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 필요한 컬럼에만 히스토그램을 자동으로 생성 cascade => TRUE, -- 테이블에 속한 인덱스 및 컬럼까지 모두 포함 degree => 4, -- 병렬 처리로 속도 향상, CPU Core 수에 맞춰 조절 no_invalidate => DBMS_STATS.AUTO_INVALIDATE -- 기존 실행 계획을 무효화하여 즉각적인 반영을 유도하려면 FALSE 사용 ); - DBA_USERS뷰에서 스키마 통계 갱신이 필요한 사용자를 선택합니다.
SELECT 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''' || USERNAME || ''', ' || 'options => ''GATHER AUTO'', ' || 'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, ' || 'method_opt => ''FOR ALL COLUMNS SIZE AUTO'', ' || 'cascade => TRUE, degree => 4, no_invalidate => DBMS_STATS.AUTO_INVALIDATE);' AS STATS_DDL FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN' -- 활성화된 계정만 AND USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW'); - 이 스크립트는 ACCOUNT_STATUS가 ‘OPEN’인 유저 중 오라클 기본 제공 계정을 제외한 스키마들을 루프(Loop)를 돌며 처리합니다.
BEGIN FOR rs IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN' AND USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW' ) ) LOOP DBMS_OUTPUT.PUT_LINE('Processing Schema: ' || rs.USERNAME); DBMS_STATS.GATHER_SCHEMA_STATS( ownname => rs.USERNAME , options => 'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => 4, no_invalidate => DBMS_STATS.AUTO_INVALIDATE ); END LOOP; END; /
3-1. 테이블 통계 갱신 (Table Statistics)
- 실무에서 가장 권장되는 방식으로, 특정 테이블을 지정하여 갱신하되, 보통
cascade => TRUE옵션을 사용하여 해당 테이블에 연결된 모든 인덱스 및 컬럼 통계도 자동으로 갱신되도록 합니다. - 특정 테이블에 대량의 INSERT/UPDATE/DELETE가 발생한 후, 또는 특정 쿼리 성능이 갑자기 떨어졌을 때 수행합니다.
EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => '스키마', tabname => '테이블명', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 특정 컬럼 버킷 지정 방법 'FOR COLUMNS SIZE 254 컬럼명' cascade => TRUE, -- 인덱스는 제외하고 테이블 본체만 빠르게 수집할 때는 FALSE로 설정합니다. degree => 4, no_invalidate => DBMS_STATS.AUTO_INVALIDATE ); - 오라클은 마지막으로 통계 정보를 수집한 시점 이후, 데이터(행)가 약 10% 이상 변경(INSERT, UPDATE, DELETE)되면 해당 통계 정보를 ‘Stale(낡음)’ 상태로 표시합니다. DBA_TAB_STATISTICS 뷰를 통해 ‘Stale’ 상태의 테이블을 확인할 수 있습니다.
SELECT COUNT(*) AS TOTAL_CNT, SUM(CASE WHEN (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) THEN 1 ELSE 0 END) AS STALE_CNT, ROUND(SUM(CASE WHEN (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) || ' %' AS STALE_RATIO FROM DBA_TAB_STATISTICS WHERE OBJECT_TYPE = 'TABLE' AND OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW'); SELECT OWNER, TABLE_NAME, STALE_STATS, STATTYPE_LOCKED, LAST_ANALYZED, 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || 'ownname => ''' || OWNER || ''', ' || 'tabname => ''' || TABLE_NAME || ''', ' || 'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, ' || 'method_opt => ''FOR ALL COLUMNS SIZE AUTO'', ' || 'cascade => TRUE, ' || 'degree => 4, ' || 'no_invalidate => DBMS_STATS.AUTO_INVALIDATE);' AS STATS_DDL FROM DBA_TAB_STATISTICS WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) -- 낡았거나 없는 대상 AND OBJECT_TYPE = 'TABLE' AND STATTYPE_LOCKED IS NULL AND OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW') ORDER BY OWNER, TABLE_NAME, LAST_ANALYZED DESC;
3-2. 인덱스 통계 갱신 (Index Statistics)
- 테이블은 그대로 두고 오직 인덱스 하나의 정보만 업데이트합니다. 인덱스 리빌드(REBUILD) 작업을 마친 직후, 또는 특정 인덱스의 클러스터링 팩터(Clustering Factor) 정보가 실물과 다를 때 사용합니다.
- 테이블 통계를 내는 것보다 훨씬 빠르지만, 컬럼 히스토그램이나 테이블 건수 정보는 갱신되지 않으므로 주의해야 합니다.
EXEC DBMS_STATS.GATHER_INDEX_STATS( ownname => '스카마', indname => '인덱스명', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 4, no_invalidate => DBMS_STATS.AUTO_INVALIDATE ); - DBA_IND_STATISTICS 뷰를 통해 ‘Stale’ 상태의 인덱스를 확인할 수 있습니다.
SELECT COUNT(*) AS TOTAL_CNT, SUM(CASE WHEN (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) THEN 1 ELSE 0 END) AS STALE_CNT, ROUND(SUM(CASE WHEN (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) || ' %' AS STALE_RATIO FROM DBA_IND_STATISTICS WHERE OBJECT_TYPE = 'INDEX' AND OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW'); SELECT OWNER, INDEX_NAME, TABLE_NAME, STALE_STATS, STATTYPE_LOCKED, LAST_ANALYZED, 'EXEC DBMS_STATS.GATHER_INDEX_STATS(' || 'ownname => ''' || OWNER || ''', ' || 'indname => ''' || INDEX_NAME || ''', ' || 'estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, ' || 'degree => 4, ' || 'no_invalidate => DBMS_STATS.AUTO_INVALIDATE);' AS STATS_DDL FROM DBA_IND_STATISTICS WHERE (STALE_STATS = 'YES' OR LAST_ANALYZED IS NULL) -- 낡았거나 없는 대상 AND OBJECT_TYPE = 'INDEX' AND STATTYPE_LOCKED IS NULL AND OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW') ORDER BY OWNER, INDEX_NAME, LAST_ANALYZED DESC;
4. 통계정보 갱신 이력 확인
- 작업이 끝나면 DBA_OPTSTAT_OPERATIONS 뷰를 통해 통계 수집 작업의 시작/종료 시간과 성공 여부를 확인할 수 있습니다. 기본 31일간 보관되며, 그 이후에는 오라클이 알아서 청소하므로 걱정하지 않으셔도 됩니다.
SELECT OPERATION, TARGET, START_TIME, END_TIME FROM DBA_OPTSTAT_OPERATIONS ORDER BY START_TIME DESC; -- 현재 보존 기간 확인 SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; GET_STATS_HISTORY_RETENTION --------------------------- 31 -- 보존 기간을 60일로 변경할 경우 EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); - 실제로 통계가 언제 반영되었는지 아래 쿼리로 확인할 수 있습니다.
-- 테이블 통계 갱신 여부 확인 SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = '스키마' AND TABLE_NAME = '테이블명'; -- 인덱스 통계 갱신 여부 확인 SELECT OWNER, INDEX_NAME, LAST_ANALYZED, DISTINCT_KEYS FROM DBA_INDEXES WHERE TABLE_NAME = '데이블명';
