자동 통계 수집은 STALE_PERCENT 설정과 관련이 있습니다.
테스트를 위해 아래와 같이 100개의 로우를 가진 t1 테이블을 생성하고, DBMS_STATS.GATHER_TABLE_STATS 프로시저로 t1 테이블의 통계를 수집하겠습니다.
-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100');
-- 1-2
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1')
PL/SQL 처리가 정상적으로 완료되었습니다.
DBMS_STATS.GET_PREFS 함수로 통계의 기본 설정(preferences)을 조회할 수 있습니다. 아래 쿼리는 t1 테이블의 STALE_PERCENT 설정 값을 조회합니다. STALE_PERCENT은 자동 통계 수집을 위해 변경되어야 할 테이블 로우의 비율입니다. 기본값은 10으로 테이블 로우의 10%가 변경되면 통계가 오래된 것으로 간주합니다.
-- 2
SELECT DBMS_STATS.GET_PREFS ('STALE_PERCENT', 'TUNA', 'T1') AS stale_percent
FROM DUAL;
STALE_PERCENT
-------------
10
1개의 행이 선택되었습니다.
*_TAB_MODIFICATIONS 뷰에서 테이블의 변경 정보를 조회할 수 있습니다. *_TAB_STATISTICS 뷰의 stale_stats 칼럼은 테이블 통계가 오래되었는지 여부를 나타냅니다. 통계 수집 후 데이터를 변경하지 않았으므로 *_TAB_MODIFICATIONS 뷰의 조회 결과가 없고, *_TAB_STATISTICS 뷰의 stale_stats 값은 NO입니다.
-- 3-1
SELECT inserts, updates, deletes
FROM user_tab_modifications
WHERE table_name = 'T1';
선택된 레코드가 없습니다.
-- 3-2
SELECT num_rows, stale_stats
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS STALE_STATS
-------- -----------
100 NO
1개의 행이 선택되었습니다.
t1 테이블에 10개의 행을 입력하고 모니터링 정보를 저장하기 위해 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 프로시저를 수행한 후 *_TAB_MODIFICATIONS 뷰와 *_TAB_STATISTICS 뷰를 조회합니다. *_TAB_MODIFICATIONS 뷰는 결과가 반환되지만 *_TAB_STATISTICS 뷰의 stale_stats 값은 여전히 NO입니다.
-- 4-1
INSERT INTO t1 SELECT c1 FROM XMLTABLE ('101 to 110' COLUMNS c1 NUMBER PATH '.');
10 행이 생성되었습니다.
-- 4-2
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 4-3
SELECT inserts, updates, deletes
FROM user_tab_modifications
WHERE table_name = 'T1';
INSERTS UPDATES DELETES
------- ------- -------
10 0 0
1개의 행이 선택되었습니다.
-- 4-4
SELECT num_rows, stale_stats
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS STALE_STATS
-------- -----------
100 NO
1개의 행이 선택되었습니다.
t1 테이블의 1개 행을 삭제하고 앞선 테스트와 동일한 작업을 수행합니다. *_TAB_STATISTICS 뷰의 stale_stats 값이 YES로 변경되었습니다. 변경된 로우는 11건(= 10 + 0 + 1)이고 비율은 11%(= 11 ÷ 100)입니다. 변경된 로우의 비율이 STALE_PERCENT(10%) 값을 초과해야 stale_stats 값이 YES로 변경된다는 것을 알 수 있습니다.
-- 5-1
DELETE FROM t1 WHERE c1 = 1;
1 행이 삭제되었습니다.
-- 5-2
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 5-3
SELECT inserts, updates, deletes
FROM user_tab_modifications
WHERE table_name = 'T1';
INSERTS UPDATES DELETES
------- ------- -------
10 0 1
1개의 행이 선택되었습니다.
-- 5-4
SELECT num_rows, stale_stats
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS STALE_STATS
-------- -----------
100 YES
1개의 행이 선택되었습니다.
t1 테이블의 통계를 수집하고 앞선 테스트와 동일한 작업을 수행합니다. *_TAB_MODIFICATIONS 뷰의 결과가 반환되지 않고 *_TAB_STATISTICS 뷰의 stale_stats 값이 NO로 변경됩니다.
-- 6-1
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 6-2
SELECT inserts, updates, deletes
FROM user_tab_modifications
WHERE table_name = 'T1';
선택된 레코드가 없습니다.
-- 6-3
SELECT num_rows, stale_stats
FROM user_tab_statistics
WHERE table_name = 'T1';
NUM_ROWS STALE_STATS
-------- -----------
109 NO
1개의 행이 선택되었습니다.
아래 쿼리로 테이블의 변경 비율을 조회할 수 있습니다.
SELECT a.owner
, a.table_name
, a.partition_name
, a.subpartition_name
, a.object_type
, a.num_rows
, a.last_analyzed
, a.stale_stats
, b.inserts
, b.updates
, b.deletes
, b.timestamp
, ROUND ((b.inserts + b.updates + b.deletes) / NULLIF (a.num_rows, 0) * 100, 2) AS mod_pct
, ROUND ((b.inserts + b.updates + b.deletes) / GREATEST (b.timestamp - a.last_analyzed, 1), 2) AS mod_per_day
FROM dba_tab_statistics a
, dba_tab_modifications b
WHERE b.table_owner = a.owner
AND b.table_name = a.table_name
AND SYS_OP_MAP_NONNULL (b.partition_name) = SYS_OP_MAP_NONNULL (a.partition_name)
AND SYS_OP_MAP_NONNULL (b.subpartition_name) = SYS_OP_MAP_NONNULL (a.subpartition_name)
ORDER BY a.stale_stats DESC NULLS LAST
, a.last_analyzed;