복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle Database 19.31.0.0
방법 : 오라클 19c 펜딩 통계(Pending Statistics)
운영 환경에서 대용량 데이터가 변경(삭제, 대량 적재 등)된 후 통계 정보를 수집하면 실행계획 변경으로 인해 DB 전체에 성능 장애가 발생할 수 있음
오라클 11gR1부터 나온 펜딩 통계(Pending Statistics) 기능을 사용하면 이런 장애를 예방할 수 있음
통계 정보를 수집하되 바로 딕셔너리에 반영(Publish)하지 않고 대기(Pending) 상태로 두는 것임
이후 DBA나 튜닝 담당자가 특정 세션에서만 optimizer_use_pending_statistics=true 파라미터를 설정해서 해당 통계 정보가 적용되었을 때 실행계획이 어떻게 변하는지 안전하게 테스트해 볼 수 있음
테스트 결과 성능에 문제가 없다고 판단되면 그때 전역으로 Publish 하여 전체 시스템에 반영하면 됨
본문에서는 테이블에 대량의 데이터를 넣고 통계를 수집하여 Full Table Scan을 유도한 뒤, 데이터를 지우고 펜딩 통계로 재수집함
이후 특정 세션에서만 펜딩 통계를 활성화해서 인덱스 스캔으로 실행계획이 안전하게 변하는지 확인해봄
테스트
테스트 테이블 및 인덱스 생성
|
1
2
3
4
5
6
7
8
|
SQL>
drop table pending_test purge;
create table pending_test (
id number,
val varchar2(100)
);
create index pending_test_idx on pending_test(id);
|
대량의 데이터(100만 건) Insert 후 통계 정보 수집
|
1
2
3
4
5
6
7
8
9
|
SQL>
insert into pending_test
select level, 'DATA_'||level from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
|
통계정보 수집(기본적으로 publish=true 상태이므로 수집 즉시 반영됨)
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats(ownname=>'IMSI', tabname=>'PENDING_TEST', cascade=>TRUE);
PL/SQL procedure successfully completed.
|
데이터가 100만 건인 상태에서 조회 쿼리 실행 및 플랜 확인(Full Table Scan 유도)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
SQL> alter session set statistics_level=all;
Session altered.
SQL> select /*+ initial_test */ * from pending_test where id > 0;
(결과 출력 생략)
#플랜 확인
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2qdsf9ug7zcd4, child number 0
-------------------------------------
select /*+ initial_test */ * from pending_test where id > 0
Plan hash value: 3483403395
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 828 (100)| | 1000K|00:00:00.24 | 69664 |
|* 1 | TABLE ACCESS FULL| PENDING_TEST | 1 | 828 (2)| 00:00:01 | 1000K|00:00:00.24 | 69664 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / PENDING_TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "PENDING_TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">0)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - initial_test
44 rows selected.
|
현재 TABLE ACCESS FULL으로 풀림, buffer를 약 69664 소모함
데이터 대부분을 삭제
|
1
2
3
4
5
6
7
|
SQL> delete from pending_test where id > 100;
999900 rows deleted.
SQL> commit;
Commit complete.
|
해당 테이블의 통계 정보 수집 방식을 Pending(대기) 상태로 변경
|
1
2
3
|
SQL> exec dbms_stats.set_table_prefs('IMSI', 'PENDING_TEST', 'PUBLISH', 'FALSE');
PL/SQL procedure successfully completed.
|
테이블 통계 수집 방식 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col table_name for a20
col preference_name for a20
col preference_value for a25
select table_name, preference_name, preference_value
from dba_tab_stat_prefs
where table_name = 'PENDING_TEST'
and preference_name = 'PUBLISH';
TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
-------------------- -------------------- -------------------------
PENDING_TEST PUBLISH FALSE
|
PREFERENCE Publish 속성이 False로 되어있음
이제부터 이 테이블에 수집되는 통계는 DB 전체에 바로 적용되지 않고 보류(Pending)됨
현재 통계정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col table_name for a20
select table_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_tables
where table_name = 'PENDING_TEST';
TABLE_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST 1000000 2026/06/17 20:58:29
SQL>
col index_name for a20
select index_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_indexes
where table_name = 'PENDING_TEST';
INDEX_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST_IDX 1000000 2026/06/17 20:58:29
|
현재 통계정보 상에는 데이터가 100만건 있다고 알고있음
펜딩 통계 수집
|
1
2
3
|
SQL> exec dbms_stats.gather_table_stats('IMSI', 'PENDING_TEST', cascade=>TRUE);
PL/SQL procedure successfully completed.
|
현재 통계정보 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col table_name for a20
select table_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_tables
where table_name = 'PENDING_TEST';
TABLE_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST 1000000 2026/06/17 20:58:29
SQL>
col index_name for a20
select index_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_indexes
where table_name = 'PENDING_TEST';
INDEX_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST_IDX 1000000 2026/06/17 20:58:29
|
현재 DB의 기본 설정된 세션들은 아직 100만 건일 때의 기존 통계 정보를 참조하고 있음
현재 펜딩 통계정보 확인(dba_ind_pending_stats)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
select table_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_tab_pending_stats
where table_name = 'PENDING_TEST';
TABLE_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST 100 2026/06/18 10:35:24
SQL>
select index_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_ind_pending_stats
where table_name = 'PENDING_TEST';
INDEX_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST_IDX 100 2026/06/18 10:35:25
|
펜딩 통계정보에는 새로 수집한 날짜(2026/06/18 10:35:25)로 보이고 num_rows도 100건으로 보임
특정 세션에서만 펜딩 통계 활성화 후 플랜 확인(인덱스 스캔 검증)
|
1
2
3
|
SQL> alter session set optimizer_use_pending_statistics=true;
Session altered.
|
펜딩 통계(100건)를 인식하게 되면, HWM까지 FTS를 하는 것보다 인덱스를 타는 것이 훨씬 싸다고 판단함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
SQL> select /*+ pending_test */ * from pending_test where id > 0;
(결과 출력 생략)
#플랜 확인
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry -rows -bytes' ));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gj01xzt58gmny, child number 0
-------------------------------------
select /*+ pending_test */ * from pending_test where id > 0
Plan hash value: 3971101916
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (100)| | 100 |00:00:00.01 | 2016 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PENDING_TEST | 1 | 4 (0)| 00:00:01 | 100 |00:00:00.01 | 2016 |
|* 2 | INDEX RANGE SCAN | PENDING_TEST_IDX | 1 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2008 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / PENDING_TEST@SEL$1
2 - SEL$1 / PENDING_TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "PENDING_TEST"@"SEL$1" ("PENDING_TEST"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "PENDING_TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">0)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - pending_test
47 rows selected.
|
INDEX RANGE SCAN으로 실행계획이 풀림
buffer도 기존 69664개에서 2016개로 적게 소모함
통계 정보 전역 Publish(실제 운영 반영)
|
1
2
3
|
SQL> exec dbms_stats.publish_pending_stats('IMSI', 'PENDING_TEST');
PL/SQL procedure successfully completed.
|
현재 통계정보 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col table_name for a20
select table_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_tables
where table_name = 'PENDING_TEST';
TABLE_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST 100 2026/06/18 10:35:24
SQL>
col index_name for a20
select index_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_indexes
where table_name = 'PENDING_TEST';
INDEX_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- -------------------
PENDING_TEST_IDX 100 2026/06/18 10:35:25
|
통계가 새로 수집한 날짜(2026/06/18 10:35:25)로 보이고 num_rows도 100건으로 변경됨
다시 기본 설정(PUBLISH=TRUE)으로 원복
|
1
2
3
|
SQL> exec dbms_stats.set_table_prefs('IMSI', 'PENDING_TEST', 'PUBLISH', 'TRUE');
PL/SQL procedure successfully completed.
|
테이블 통계 수집 방식 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col table_name for a20
col preference_name for a20
col preference_value for a25
select table_name, preference_name, preference_value
from dba_tab_stat_prefs
where table_name = 'PENDING_TEST'
and preference_name = 'PUBLISH';
TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
-------------------- -------------------- -------------------------
PENDING_TEST PUBLISH TRUE
|
PREFERENCE Publish 속성이 true로 변경됨
이 테이블에 수집되는 통계는 DB 전체에 바로 적용됨
참고1. 펜딩 통계 삭제
펜딩 통계 삭제
|
1
2
3
|
SQL> exec dbms_stats.delete_pending_stats('IMSI', 'PENDING_TEST');
PL/SQL procedure successfully completed.
|
현재 펜딩 통계정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select table_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_tab_pending_stats
where table_name = 'PENDING_TEST';
no rows selected
SQL>
select index_name, num_rows, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal
from dba_ind_pending_stats
where table_name = 'PENDING_TEST';
no rows selected
|
펜딩 통계가 삭제됨
참고2. 스키마 레벨 펜딩 통계 설정
해당 유저의 통계 정보 수집 방식을 Pending(대기) 상태로 변경
|
1
2
3
|
SQL> exec dbms_stats.set_schema_prefs('IMSI', 'PUBLISH', 'FALSE');
PL/SQL procedure successfully completed.
|
추가 테이블 1개 생성
|
1
2
3
|
SQL> create table newtbl (col1 number);
Table created.
|
테이블 통계 수집 방식 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col owner for a10
col table_name for a20
col preference_name for a20
col preference_value for a25
select owner, table_name, preference_name, preference_value
from dba_tab_stat_prefs
where 1=1
and preference_name = 'PUBLISH';
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- -------------------- -------------------- -------------------------
IMSI SALES_PART PUBLISH FALSE
IMSI ACCOUNT PUBLISH FALSE
IMSI BIG_EMP PUBLISH FALSE
IMSI CHANNELS PUBLISH FALSE
IMSI COUNTRIES PUBLISH FALSE
IMSI COUNTRY PUBLISH FALSE
IMSI CUSTOMERS PUBLISH FALSE
IMSI DCN_LOG PUBLISH FALSE
...
|
set_schema_prefs를 수행하는 시점에 해당 유저에 존재하는 모든 테이블에 대해 publish=false가 설정되고
명령어 수행 이후에 생성된 테이블은 영향을 받지 않음
결론 :
통계 정보를 새로 수집하려 할 때 실행계획 변경으로 인한 걱정이 있다면 펜딩 통계 기능을 이용해서 충분한 테스트 후 통계정보 최신화가 가능함
대량 dml 작업 이후 통계 정보를 갱신할 때 본문 시나리오와 같이 펜딩 통계 수집 후
특정 세션에서 optimizer_use_pending_statistics=true 를 설정하고
해당 테이블을 사용하는 모든 쿼리를 다 수행해서 악성 플랜이 발생하지 않는지 확인 후 Publish 하면 성능 장애를 예방할 수 있음
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/controlling-the-use-of-optimizer-statistics.html#GUID-8FF503A2-5E50-4054-AC3A-5672343BDFE3
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-6F9A5D56-9F06-412D-8218-D92CDA07180A
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_STAT_PREFS.html#GUID-32D6CE93-E336-4BA5-A55C-1CACFDEF423E
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_USE_PENDING_STATISTICS.html
'ORACLE > Performance Tuning' 카테고리의 다른 글
| 오라클 19c stale percent 변경 및 자동통계수집 (0) | 2026.06.22 |
|---|---|
| 오라클 19c 병렬 ITAS를 이용한 Clustering Factor 개선 테스트 (0) | 2026.06.17 |
| 오라클 19c AWR 스냅샷의 플랜을 SPM(SQL Plan Management)으로 고정 (0) | 2026.05.24 |
| 오라클 19c SQL Profile 등록된 플랜 SPM으로 변경 자동화 스크립트 (0) | 2026.05.20 |
| 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 (0) | 2026.05.18 |