오라클 19c 파티션 테이블 통계정보 이관, 복제

2022. 7. 17. 23:27 내맘대로긍정

프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : Oracle Database 19.10.0.0

방법 : 오라클 19c 파티션 테이블 통계정보 이관, 복제

오라클 19c에서 파티션 테이블에 새로운 파티션을 추가할 때 기존 파티션의 통계정보를 신규 파티션으로 복제해주는 방법을 설명함

신규 파티션 테이블 생성 및 데이터 삽입, 통계정보 수집 후 확인

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

SQL> 
-- 테이블 생성
drop table ptable3 purge;
create table ptable3
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113'),
partition p_max values less than (maxvalue)
)
;
 
 
-- 샘플 데이터 삽입
insert into PTABLE3 values ('202101',1);
insert into PTABLE3 values ('202102',1);
insert into PTABLE3 values ('202102',1);
insert into PTABLE3 values ('202103',1);
insert into PTABLE3 values ('202104',1);
insert into PTABLE3 values ('202105',1);
commit;
 
 
-- 통계정보 수집
exec dbms_stats.gather_table_stats('IMSI', 'PTABLE3', cascade=>TRUE);
 
 
-- 파티션 테이블 확인
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE3'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE3  P202101  '202102'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202102  '202103'   USERS        8388608      2 17-JUL-22    YES
IMSI     PTABLE3  P202103  '202104'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202104  '202105'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202105  '202106'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202106  '202107'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202107  '202108'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202108  '202109'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202109  '202110'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202110  '202111'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202111  '202112'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202112  '202113'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P_MAX    MAXVALUE   USERS                 0 17-JUL-22    YES
 
13 rows selected.

새로운 파티션 추가(split)

2022년도 월별 파티션

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL>
alter table PTABLE3 split partition p_max at ('202202') into (partition p202201, partition p_max);
alter table PTABLE3 split partition p_max at ('202203') into (partition p202202, partition p_max);
alter table PTABLE3 split partition p_max at ('202204') into (partition p202203, partition p_max);
alter table PTABLE3 split partition p_max at ('202205') into (partition p202204, partition p_max);
alter table PTABLE3 split partition p_max at ('202206') into (partition p202205, partition p_max);
alter table PTABLE3 split partition p_max at ('202207') into (partition p202206, partition p_max);
alter table PTABLE3 split partition p_max at ('202208') into (partition p202207, partition p_max);
alter table PTABLE3 split partition p_max at ('202209') into (partition p202208, partition p_max);
alter table PTABLE3 split partition p_max at ('202210') into (partition p202209, partition p_max);
alter table PTABLE3 split partition p_max at ('202211') into (partition p202210, partition p_max);
alter table PTABLE3 split partition p_max at ('202212') into (partition p202211, partition p_max);
alter table PTABLE3 split partition p_max at ('202213') into (partition p202212, partition p_max);

파티션 확인

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

SQL>
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE3'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE3  P202101  '202102'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202102  '202103'   USERS        8388608      2 17-JUL-22    YES
IMSI     PTABLE3  P202103  '202104'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202104  '202105'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202105  '202106'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202106  '202107'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202107  '202108'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202108  '202109'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202109  '202110'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202110  '202111'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202111  '202112'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202112  '202113'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202201  '202202'   USERS                        NO
IMSI     PTABLE3  P202202  '202203'   USERS                        NO
IMSI     PTABLE3  P202203  '202204'   USERS                        NO
IMSI     PTABLE3  P202204  '202205'   USERS                        NO
IMSI     PTABLE3  P202205  '202206'   USERS                        NO
IMSI     PTABLE3  P202206  '202207'   USERS                        NO
IMSI     PTABLE3  P202207  '202208'   USERS                        NO
IMSI     PTABLE3  P202208  '202209'   USERS                        NO
IMSI     PTABLE3  P202209  '202210'   USERS                        NO
IMSI     PTABLE3  P202210  '202211'   USERS                        NO
IMSI     PTABLE3  P202211  '202212'   USERS                        NO
IMSI     PTABLE3  P202212  '202213'   USERS                        NO
IMSI     PTABLE3  P_MAX    MAXVALUE   USERS                        NO
 
25 rows selected.

신규 파티션이 추가되었고 통계정보는 없는 상태

기존 통계정보 복사

22년 01월~06월 파티션까지는 21년02월 파티션의 통계정보를 복사(row 2건)

22년 07월~max 파티션까지는 21년12월 파티션의 통계정보를 복사(row 0건)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL>
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202201');
--exec dbms_lock.sleep(5);
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202202');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202203');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202204');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202205');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202102','p202206');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202207');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202208');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202209');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202210');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202211');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p202212');
exec dbms_stats.copy_table_stats('IMSI','PTABLE3','P202112','p_max');

통계정보 복제시 지나친 lock을 방지하기위해 중간에 sleep를 추가할수도있음

파티션 테이블 확인

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

SQL>
set lines 200 pages 1000
col table_owner for a8
col table_name for a8
col partition_name for a8
col high_value for a10
col tablespace_name for a10
select dtp.table_owner, dtp.table_name, dtp.partition_name, dtp.high_value
, dtp.tablespace_name, dtp.initial_extent, dtp.num_rows, dtp.last_analyzed, dtp.global_stats
from dba_tab_partitions dtp, dba_segments ds 
where 1=1
and dtp.partition_name = ds.partition_name(+)
and dtp.table_name = ds.segment_name(+)
and dtp.tablespace_name = ds.tablespace_name(+)
and dtp.table_name='PTABLE3'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZE GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------ ---
IMSI     PTABLE3  P202101  '202102'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202102  '202103'   USERS        8388608      2 17-JUL-22    YES
IMSI     PTABLE3  P202103  '202104'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202104  '202105'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202105  '202106'   USERS        8388608      1 17-JUL-22    YES
IMSI     PTABLE3  P202106  '202107'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202107  '202108'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202108  '202109'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202109  '202110'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202110  '202111'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202111  '202112'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202112  '202113'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202201  '202202'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202202  '202203'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202203  '202204'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202204  '202205'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202205  '202206'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202206  '202207'   USERS                 2 17-JUL-22    YES
IMSI     PTABLE3  P202207  '202208'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202208  '202209'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202209  '202210'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202210  '202211'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202211  '202212'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P202212  '202213'   USERS                 0 17-JUL-22    YES
IMSI     PTABLE3  P_MAX    MAXVALUE   USERS                 0 17-JUL-22    YES
 
25 rows selected.

정상적으로 통계정보가 복사됨

22년 01월~06월 파티션까지는 21년02월 파티션의 통계정보가 복사되었고(row 2건)

22년 07월~max 파티션까지는 21년12월 파티션의 통계정보가 복사됨(row 0건)

참조 :

https://positivemh.tistory.com/839

100299.1, 727306.1
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2098.htm#i1591118
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-create-tables-indexes.html#GUID-66D19C36-C512-48DE-88B4-AFC6CCACDFEB
https://minimalistic-oracle.blogspot.com/2016/09/sql-script-to-display-partitions-and.html
http://www.oracle-wiki.net/premium:startdocsreallifeinvalidatingaplan
http://oracledbakg.blogspot.com/2009/04/invalidating-execution-plan-of-sql.html

https://hrjeong.tistory.com/322

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 19c 테이블 move 동일 테이블스페이스 테스트 (0)	2022.12.21
오라클 11gR2 ddl wait (ddl_lock_timeout 파라미터) (0)	2022.08.22
오라클 19c 파티션 테이블 생성, 파티션 add, split (0)	2022.07.17
오라클 19c dba_hist_active_sess_history에 있는 sql이 dba_hist_sqlstat에 없는 이유 (0)	2022.07.08
오라클 19c awr 데이터 export 방법 (0)	2022.07.04

내맘대로긍정

박영민 OracleDBA@kakao.com

Category

Menu

Comment

Link