프린트 하기

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : 오라클 11gR2 파티션 변경(split, drop, add) 시 실행계획 변화 확인(실행계획 변경)

오라클 11gR2에서 파티션 테이블에 변경(split, drop, add)을 가했을 때 기존 sql의 커서가 무효화된 상태(invalidations)가 되는지 확인하는 테스트

 

 

파티션 split 테스트

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

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
78
79
80
81
82
83
84
85
86
87
88
89
SQL>
-- date 포맷 변경
alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';
 
 
-- 샘플 테이블 생성
drop table ptable4 purge;
create table ptable4
(
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 PTABLE4 values ('202101',1);
insert into PTABLE4 values ('202102',1);
insert into PTABLE4 values ('202102',1);
insert into PTABLE4 values ('202103',1);
insert into PTABLE4 values ('202104',1);
insert into PTABLE4 values ('202105',1);
commit;
 
 
-- 통계정보 수집
exec dbms_stats.gather_table_stats('IMSI', 'PTABLE4', 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='PTABLE4'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZED       GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------------- ---
IMSI     PTABLE4  P202101  '202102'   USERS        8388608      1 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202102  '202103'   USERS        8388608      2 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202103  '202104'   USERS        8388608      1 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202104  '202105'   USERS        8388608      1 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202105  '202106'   USERS        8388608      1 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202106  '202107'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202107  '202108'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202108  '202109'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202109  '202110'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202110  '202111'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202111  '202112'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P202112  '202113'   USERS                 0 2022/07/18 10:28:53 YES
IMSI     PTABLE4  P_MAX    MAXVALUE   USERS                 0 2022/07/18 10:28:53 YES
 
13 rows selected.
 
-- 추가 샘플 테이블 생성
drop table imsitb purge;
create table imsitb (c1 number);
create index idx_imsitb01 on imsitb(c1);
insert into imsitb values (1);
insert into imsitb values (2);
commit;

 

 

샘플 쿼리 실행(3번 실행)

1
2
3
SQL>
select col2, col1 from ptable4 
where col2 in (select c1 from imsitb);

 

 

sql_id 확인

1
2
3
4
5
6
7
8
SQL> 
select sql_id, sql_text 
from v$sql 
where sql_text like '%select col2, col1 from ptable4%';
 
SQL_ID           SQL_TEXT
------------- --------------------------------------- 
3zhcy1fagtrgv     select col2, col1 from ptable4 where col2 in

 

 

실행계획 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
select * from table(dbms_xplan.display_cursor('3zhcy1fagtrgv',null,'ADVANCED ALLSTATS LAST'));
 
Plan hash value: 445751859
 
------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |         |         |      64 (100)|         |         |         |
|   1 |  NESTED LOOPS SEMI   |            |       6 |     138 |      64   (0)| 00:00:01 |         |         |
|   2 |   PARTITION RANGE ALL|            |       6 |      60 |      64   (0)| 00:00:01 |       1 |      13 |
|   3 |    TABLE ACCESS FULL | PTABLE4        |       6 |      60 |      64   (0)| 00:00:01 |       1 |      13 |
|*  4 |   INDEX RANGE SCAN   | IDX_IMSITB01 |       2 |      26 |       0   (0)|         |         |         |
------------------------------------------------------------------------------------------------------

 

 

해당 sql_id의 정보 확인

1
2
3
4
5
6
7
8
SQL>
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='3zhcy1fagtrgv';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000C9875588 2499599867         3        1          1         0        3

현재 excutions가 3이고 loads가 1이고 parse_calls가 3이고 invalidations가 0임

라이브러리 캐시에 1번 적재되었고 3번 실행중 한번은 하드파싱, 2번은 소프트파싱되었고 무효화된적은 없는 쿼리라는 뜻임

parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려고 요청한 횟수

loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
executions : SQL을 수행한 횟수
invalidations : 커서가 무효화된 횟수, 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함

 

 

파티션 split 후 재확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter table PTABLE4 split partition p_max at ('202202') into (partition p202201, partition p_max);
 
Table altered.
 
SQL> 
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='3zhcy1fagtrgv';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000C9875588 2499599867         3        1          1         1        3

현재 다른건 그대로이고 invalidations가 1로 변경됨

 

 

기존 sql 재실행(2번 실행)

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
-- sql 2번 실행
select col2, col1 from ptable4 
where col2 in (select c1 from imsitb);
 
-- 확인
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='3zhcy1fagtrgv';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000F415D6C8 2499599867         2        2          1         1        2

loads가 2로 변했고 invaildations가 1이고 executions과 parse_calls는 초기화된 뒤 다시 2로 카운트됨

커서가 무효화 된 뒤(invaildations : 1) 라이브러리 캐시에 새로 load 되었고(loads : 2)

새로 load 된 이후 두번 실행됨(executions : 2) 그리고 하드파싱1번에 소프트파싱 1번됨(parse_calls) 

 

 

파티션 split 시 결론 

파티션을 split 하면 커서가 무효화되고 라이브러리 캐시에 새로 로드되게 된다.

=> 실행계획도 변경될 가능성이 있다.(새로 라이브러리 캐시에 로드되고 파싱을 새로 하기때문)

 

 

maxvalue 파티션 drop 테스트

샘플 테이블 생성

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
78
79
80
81
82
83
84
85
86
87
88
89
SQL>
-- date 포맷 변경
alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';
 
 
-- 샘플 테이블 생성
drop table ptable5 purge;
create table ptable5
(
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 PTABLE5 values ('202101',1);
insert into PTABLE5 values ('202102',1);
insert into PTABLE5 values ('202102',1);
insert into PTABLE5 values ('202103',1);
insert into PTABLE5 values ('202104',1);
insert into PTABLE5 values ('202105',1);
commit;
 
 
-- 통계정보 수집
exec dbms_stats.gather_table_stats('IMSI', 'PTABLE5', 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='PTABLE5'
and dtp.table_owner='IMSI'
order by 2,3;
 
TABLE_OW TABLE_NA PARTITIO HIGH_VALUE TABLESPACE INITIAL_EXTENT   NUM_ROWS LAST_ANALYZED       GLO
-------- -------- -------- ---------- ---------- -------------- ---------- ------------------- ---
IMSI     PTABLE5  P202101  '202102'   USERS        8388608      1 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202102  '202103'   USERS        8388608      2 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202103  '202104'   USERS        8388608      1 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202104  '202105'   USERS        8388608      1 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202105  '202106'   USERS        8388608      1 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202106  '202107'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202107  '202108'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202108  '202109'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202109  '202110'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202110  '202111'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202111  '202112'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P202112  '202113'   USERS                 0 2022/07/18 10:47:50 YES
IMSI     PTABLE5  P_MAX    MAXVALUE   USERS                 0 2022/07/18 10:47:50 YES
 
13 rows selected.
 
-- 추가 샘플 테이블 생성
drop table imsitb2 purge;
create table imsitb2 (c1 number);
create index idx_imsitb2_01 on imsitb2(c1);
insert into imsitb2 values (1);
insert into imsitb2 values (2);
commit;

 

 

샘플 쿼리 실행(3번 실행)

1
2
3
SQL>
select col2, col1 from ptable5
where col2 in (select c1 from imsitb2);

 

 

sql_id 확인

1
2
3
4
5
6
7
8
SQL> 
select sql_id, sql_text 
from v$sql 
where sql_text like '%select col2, col1 from ptable5%';
 
SQL_ID           SQL_TEXT
------------- --------------------------------------- 
gszz4d8yhq5hq     select col2, col1 from ptable5 where col2 in

 

 

해당 sql_id의 정보 확인

1
2
3
4
5
6
7
8
SQL>
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='gszz4d8yhq5hq';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000BA8AAED8 1024136726         3        1          1         0        3

현재 excutions가 3이고 loads가 1이고 parse_calls가 3이고 invalidations가 0임

라이브러리 캐시에 1번 적재되었고 3번 실행중 한번은 하드파싱, 2번은 소프트파싱되었고 무효화된적은 없는 쿼리라는 뜻임

parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려고 요청한 횟수

loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
executions : SQL을 수행한 횟수
invalidations : 커서가 무효화된 횟수, 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함

 

 

maxvalue 파티션 drop 후 재확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter table PTABLE5 drop partition P_MAX;
 
Table altered.
 
SQL> 
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='gszz4d8yhq5hq';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000BA8AAED8 1024136726         3        1          1         1        3

현재 다른건 그대로이고 invalidations가 1로 변경됨

 

 

기존 sql 재실행(2번 실행)

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
-- sql 2번 실행
select col2, col1 from ptable5
where col2 in (select c1 from imsitb2);
 
-- 확인
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='gszz4d8yhq5hq';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000BA8AAED8 1024136726         2        2          1         1        2

loads가 2로 변했고 invaildations가 1이고 executions과 parse_calls는 초기화된 뒤 다시 2로 카운트됨

커서가 무효화 된 뒤(invaildations : 1) 라이브러리 캐시에 새로 load 되었고(loads : 2)

새로 load 된 이후 두번 실행됨(executions : 2) 그리고 하드파싱1번에 소프트파싱 1번됨(parse_calls) 

 

 

파티션 drop 시 결론 

파티션 split 시와 동일하게

파티션을 drop 하면 커서가 무효화되고 라이브러리 캐시에 새로 로드되게 된다.

=> 실행계획도 변경될 가능성이 있다.(새로 라이브러리 캐시에 로드되고 파싱을 새로 하기때문)

 

 

파티션 add 테스트

파티션 drop 테스트 시 사용한 테이블 계속 사용

 

 

파티션 add 후 재확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter table PTABLE5 add partition p202201 values less than ('202202');
 
Table altered.
 
SQL> 
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='gszz4d8yhq5hq';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000BA8AAED8 1024136726         2        2          1         2        2

현재 다른건 그대로이고 invalidations가 2로 변경됨

 

 

기존 sql 재실행(4번 실행)

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
-- sql 4번 실행
select col2, col1 from ptable5
where col2 in (select c1 from imsitb2);
 
-- 확인
select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
from v$sqlarea 
where sql_id='gszz4d8yhq5hq';
 
ADDRESS      HASH_VALUE EXECUTIONS        LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000000BA8AAED8 1024136726         4        3          1         2        4

loads가 3로 변했고 invaildations가 2이고 executions과 parse_calls는 초기화된 뒤 다시 4로 카운트됨

커서가 무효화 된 뒤(invaildations : 2) 라이브러리 캐시에 새로 load 되었고(loads : 3)

새로 load 된 이후 네번 실행됨(executions : 4) 그리고 하드파싱1번에 소프트파싱 3번됨(parse_calls) 

 

 

파티션 add 시 결론 

파티션 split, drop 시와 동일하게

파티션을 add 하면 커서가 무효화되고 라이브러리 캐시에 새로 로드되게 된다.

=> 실행계획도 변경될 가능성이 있다.(새로 라이브러리 캐시에 로드되고 파싱을 새로 하기때문)

+ 본문에 넣진않았지만 파티션 exchange 도 동일하게 무효화된다.

 

 

참조 : 

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

http://wiki.gurubee.net/pages/viewpage.action?pageId=26741308 

http://www.dba-oracle.com/m_Invalidations.htm

저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

오라클 19c cursor_sharing 테스트  (0) 2022.08.08
오라클 19c 실행계획 변경 시 spm 적용 방법  (0) 2022.08.08
오라클 19c itas 시 full scan과 index scan 속도 비교 테스트  (2) 2022.04.13
오라클 19c 실행계획 비교 DBMS_XPLAN.COMPARE_PLANS  (0) 2022.03.21
sqlp 실기튜닝실습 17-1 PUSH PREDICATE 문제 devDo  (2) 2022.03.06

티스토리툴바