[2025-09-07] Oracle12cR2 환경에서 Adaptive Plan 활성화 시 PLAN_HASH_VALUE가 서로 다르나 FULL_PLAN_HASH_VALUE는 같은 경우 재현
[실습
환경]
OS : Oracle
Linux Server 7.9 (Linux rdb01d 5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production (Version
19.27.0.0.0)
RAC1 : ol7ora19r1(192.168.0.21) : ORA19R1
RAC2 :
ol7ora19r2(192.168.0.22) : ORA19R2
Multiple Plan Hash Values are seen for the Same Execution Plan (Doc ID 2998600.1) 문서 분석 내용
[배경 지식]
PLAN_HASH_VALUE : 실행 시 실제로 선택된 최종 실행계획의 해시값
--> 단한개의 길
FULL_PLAN_HASH_VALUE : 12cR1부터 추가, Adaptive Plan처럼 실행 중 바뀔 수 있는 경우까지 포함해서
전체 플랜 구조에 대한 해시값
-->
여러가지의 길을 대표하는 대표값
[결론]
Adaptive plan이 활성화 되면 특정
sql_id+child_number 기준 여러개의 Plan_hash_value를 가질 수 있고 full_plan_hash_Value는 하나로
관리됨
이런 경우 plan_hash_Value가 다르다고 해도 full_plan_hash_Value가 같다면 그걸 실행계획이 바뀌지 않은
것임
그때그때마다 adaptive하게 잘 돈 것임
하지만
mos문서 : Multiple Plan Hash Values are seen for the Same Execution Plan (Doc
ID 2998600.1)
를 보면 adaptive plan가 작동하지 않은 sql문인데도 불구하고 plan_hash_value가 다르면서
실행계획을 같은 경우가 있음
이런 경우 full_plan_hash_value가 모두 같다면 같은 실행계획으로 판단하면됨
결국 한줄로 결론을 내자면 "PHV가 달라도 FPHV가 동일하다면 실행계획은 바뀐 게 아니다"
1. 테스트 테이블
및 데이터 입력 생성
DROP TABLE
TAB2 PURGE;
DROP SEQUENCE TAB2_SEQ;
DROP TABLE
TAB1 PURGE;
DROP SEQUENCE TAB1_SEQ;
CREATE
TABLE TAB1 (
ID
NUMBER
, CODE
VARCHAR2(5)
, DATA
NUMBER(15)
, CONSTRAINT TAB1_PK PRIMARY KEY
(ID));
CREATE INDEX TAB1_CODE ON
TAB1(CODE);
CREATE SEQUENCE TAB1_SEQ;
--단 5건의 데이터만 있으므로 nl join이 유리함
INSERT INTO TAB1 VALUES (TAB1_SEQ.NEXTVAL, 'ONE', 1);
INSERT INTO TAB1
VALUES (TAB1_SEQ.NEXTVAL, 'TWO', 2);
INSERT INTO TAB1 VALUES
(TAB1_SEQ.NEXTVAL, 'THREE', 3);
INSERT INTO TAB1 VALUES (TAB1_SEQ.NEXTVAL,
'FOUR', 4);
INSERT INTO TAB1 VALUES (TAB1_SEQ.NEXTVAL, 'FIVE',
5);
COMMIT;
CREATE
TABLE TAB2 (
ID
NUMBER
, TAB1_ID NUMBER
, DATA NUMBER(15)
, CONSTRAINT TAB2_PK PRIMARY KEY
(ID)
, CONSTRAINT TAB2_TAB1_FK FOREIGN KEY (TAB1_ID)
REFERENCES TAB1(ID)
);
CREATE
SEQUENCE TAB2_SEQ;
CREATE INDEX TAB2_TAB1_FKI ON
TAB2(TAB1_ID);
INSERT /*+
APPEND */ INTO TAB2
SELECT TAB2_SEQ.NEXTVAL
,
TRUNC(DBMS_RANDOM.VALUE(1,5))
, LEVEL
FROM
DUAL CONNECT BY LEVEL <= 100;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER, tabname => 'TAB1'
);
END;
/
;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER, tabname => 'TAB2'
);
END;
/
;
SELECT
c.column_name
,
c.data_type
, s.histogram
, s.num_distinct
,
s.num_buckets
,
s.density
, TO_CHAR(s.last_analyzed,'YYYY-MM-DD
HH24:MI:SS') AS last_analyzed
,
s.sample_size
FROM user_tab_cols c JOIN user_tab_col_statistics s ON
s.table_name = c.table_name
AND s.column_name =
c.column_name
WHERE c.table_name = 'TAB2'
ORDER BY
c.column_id;
2. NL
조인이 선택하는 것을 확인
ALTER SESSION SET
OPTIMIZER_ADAPTIVE_REPORTING_ONLY = FALSE;
ALTER SESSION SET
OPTIMIZER_ADAPTIVE_PLANS = TRUE;
ALTER SESSION SET
OPTIMIZER_ADAPTIVE_STATISTICS = FALSE;
ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT /*
TEST_1 */ A.DATA AS TAB1_DATA
, B.DATA AS
TAB2_DATA
FROM TAB1 A JOIN TAB2 B ON B.TAB1_ID = A.ID
WHERE
A.CODE = 'ONE'
;
SELECT
* FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL +NOTE
+ADAPTIVE'));
PLAN_TABLE_OUTPUT
SQL_ID 2kj6xq4rd4sbm, child number
0
-------------------------------------
SELECT /* TEST_1 */ A.DATA AS
TAB1_DATA , B.DATA AS
TAB2_DATA
FROM TAB1 A JOIN TAB2 B ON B.TAB1_ID =
A.ID WHERE A.CODE = 'ONE'
Plan hash value:
2672205743
-----------------------------------------------------------------------------------------------------------
|
Id |
Operation
| Name | Rows |
Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------------------
|
0 | SELECT
STATEMENT
|
| |
| 3
(100)| |
|- * 1
| HASH
JOIN
|
| 25 | 425 | 3
(0)| 00:00:01 |
| 2 | NESTED
LOOPS
|
| 25 | 425 | 3
(0)| 00:00:01 |
| 3 | NESTED
LOOPS
|
| 25 | 425 | 3
(0)| 00:00:01 |
|- 4 | STATISTICS
COLLECTOR
|
| |
|
|
|
| 5 | TABLE ACCESS BY
INDEX ROWID BATCHED| TAB1
| 1 | 11 |
2 (0)| 00:00:01 |
| * 6
| INDEX RANGE
SCAN
| TAB1_CODE | 1
| | 1
(0)| 00:00:01 |
| * 7 | INDEX RANGE
SCAN
| TAB2_TAB1_FKI | 25 |
| 0
(0)|
|
| 8 | TABLE ACCESS BY INDEX
ROWID |
TAB2 |
25 | 150 | 1 (0)| 00:00:01
|
|- 9 | TABLE ACCESS
FULL
| TAB2 |
25 | 150 | 1 (0)| 00:00:01
|
-----------------------------------------------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation
id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
5 - SEL$58A6D7F6 / A@SEL$1
6 - SEL$58A6D7F6 / A@SEL$1
7 - SEL$58A6D7F6 / B@SEL$1
8 - SEL$58A6D7F6 / B@SEL$1
9 - SEL$58A6D7F6 / B@SEL$1
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 -
access("A"."CODE"='ONE')
7 -
access("B"."TAB1_ID"="A"."ID")
Column Projection Information
(identified by operation
id):
-----------------------------------------------------------
1 - (#keys=1) "B"."DATA"[NUMBER,22], "A"."DATA"[NUMBER,22],
"B"."DATA"[NUMBER,22]
2 - "A"."ID"[NUMBER,22],
"A"."DATA"[NUMBER,22], "B"."DATA"[NUMBER,22]
3 -
"A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22], "B".ROWID[ROWID,10]
4 - "A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22]
5 -
"A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22]
6 -
"A".ROWID[ROWID,10]
7 - "B".ROWID[ROWID,10]
8 -
"B"."DATA"[NUMBER,22]
9 - "B"."TAB1_ID"[NUMBER,22],
"B"."DATA"[NUMBER,22]
Note
-----
- this is an
adaptive plan (rows marked '-' are inactive)
--plan_hash_Value가 2672205743 임 옵티마이저가 hash join 및
nl join을 염두했고
--그중 nl조인이 채택됨
SELECT
INST_ID
, SQL_ID
,
CHILD_NUMBER
,
PLAN_HASH_VALUE
, FULL_PLAN_HASH_VALUE
, EXECUTIONS
FROM
GV$SQL
WHERE SQL_ID = '2kj6xq4rd4sbm'
ORDER BY INST_ID,
CHILD_NUMBER
;
INST_ID SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE EXECUTIONS
1
2kj6xq4rd4sbm 0
2672205743 2305938367 1
--> FULL_PLAN_HASH_VALUE 는 2305938367
임
SELECT A.SQL_ID
, A.HASH_VALUE
, A.PLAN_HASH_VALUE
,
A.FULL_PLAN_HASH_VALUE
,
A.CHILD_NUMBER
, A.ID
,
LPAD (' ', A."DEPTH" ) || OPERATION OPERATION
,
A.OPTIONS
,
A.OBJECT_NAME
,
A.OPTIMIZER
, A.COST
,
A.ACCESS_PREDICATES
,
A.FILTER_PREDICATES
,
A.PROJECTION
, A.ADDRESS
FROM V$SQL_PLAN A
WHERE A.SQL_ID =
'2kj6xq4rd4sbm'
AND CHILD_NUMBER = 0
ORDER BY A.SQL_ID,
A.HASH_VALUE, A.CHILD_NUMBER, A.ID ;
SQL_ID HASH_VALUE
PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE CHILD_NUMBER ID
OPERATION
OPTIONS OBJECT_NAME OPTIMIZER COST
ACCESS_PREDICATES
FILTER_PREDICATES
PROJECTION
ADDRESS
------------- ---------- --------------- -------------------
------------ -- ----------------------------- --------- -------------- ---------
---- ---------------------------- ---------------- --------------------------
----------------
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0 0
SELECT
STATEMENT
ALL_ROWS
3
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
1 HASH
JOIN
3
"B"."TAB1_ID"="A"."ID"
"B"."DATA","A"."DATA","B"."DATA" 0000000077BD0650
2kj6xq4rd4sbm
785539443 2672205743
2305938367
0
2 NESTED
LOOPS
3
"A"."ID","A"."DATA","B"."DATA" 0000000077BD0650
2kj6xq4rd4sbm
785539443 2672205743
2305938367
0
3 NESTED
LOOPS
3
"A"."ID","A"."DATA","B".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
4 STATISTICS
COLLECTOR
"A"."ID","A"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
5 TABLE ACCESS BY INDEX
ROWID BATCHED
TAB1
2
"A"."ID","A"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
6 INDEX RANGE
SCAN
TAB1_CODE
1
"A"."CODE"='ONE'
"A".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
7 INDEX RANGE
SCAN
TAB2_TAB1_FKI
0
"B"."TAB1_ID"="A"."ID"
"B".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
8 TABLE ACCESS BY INDEX
ROWID
TAB2
1
"B"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
2672205743
2305938367
0
9 TABLE ACCESS
FULL
TAB2
1
"B"."TAB1_ID","B"."DATA"
0000000077BD0650
--이번에는 tab1에 데이터를 1만건 부음 (1만번
loop돌기는 너무 비효율적이니 해시 조인을 잘 태우줄것으로 예측됨)
INSERT /*+ APPEND */ INTO
TAB1
SELECT TAB1_SEQ.NEXTVAL
,
'ONE'
, LEVEL FROM DUAL
CONNECT BY
LEVEL <= 10000;
COMMIT;
--tab2의 데이터는 날림 이러면 확실히 해시 조인으로 가게됨
TRUNCATE TABLE TAB2;
ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT /*
TEST_1 */ A.DATA AS TAB1_DATA
, B.DATA AS
TAB2_DATA
FROM TAB1 A JOIN TAB2 B ON B.TAB1_ID = A.ID
WHERE
A.CODE = 'ONE'
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL +NOTE +ADAPTIVE'));
PLAN_TABLE_OUTPUT
SQL_ID 2kj6xq4rd4sbm, child number
0
-------------------------------------
SELECT /* TEST_1 */ A.DATA AS
TAB1_DATA , B.DATA AS
TAB2_DATA
FROM TAB1 A JOIN TAB2 B ON B.TAB1_ID =
A.ID WHERE A.CODE = 'ONE'
Plan hash value:
1599395313
-----------------------------------------------------------------------------------------------------------
|
Id |
Operation
| Name | Rows |
Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------------------
|
0 | SELECT
STATEMENT
|
| |
| 3
(100)| |
|
* 1 | HASH
JOIN
|
| 25 | 425 | 3
(0)| 00:00:01 |
|- 2 | NESTED
LOOPS
|
| 25 | 425 | 3
(0)| 00:00:01 |
|- 3 | NESTED
LOOPS
|
| 25 | 425 | 3
(0)| 00:00:01 |
|- 4 | STATISTICS
COLLECTOR
|
| |
|
|
|
| 5 | TABLE ACCESS BY
INDEX ROWID BATCHED| TAB1
| 1 | 11 |
2 (0)| 00:00:01 |
| * 6
| INDEX RANGE
SCAN
| TAB1_CODE | 1
| | 1
(0)| 00:00:01 |
|- * 7 | INDEX RANGE
SCAN
| TAB2_TAB1_FKI | 25 |
| 0
(0)|
|
|- 8 | TABLE ACCESS BY INDEX
ROWID |
TAB2 |
25 | 150 | 1 (0)| 00:00:01
|
| 9 | TABLE ACCESS
FULL
| TAB2 |
25 | 150 | 1 (0)| 00:00:01
|
-----------------------------------------------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation
id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
5 - SEL$58A6D7F6 / A@SEL$1
6 - SEL$58A6D7F6 / A@SEL$1
7 - SEL$58A6D7F6 / B@SEL$1
8 - SEL$58A6D7F6 / B@SEL$1
9 - SEL$58A6D7F6 / B@SEL$1
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - access("B"."TAB1_ID"="A"."ID")
6 -
access("A"."CODE"='ONE')
7 -
access("B"."TAB1_ID"="A"."ID")
Column Projection Information
(identified by operation
id):
-----------------------------------------------------------
1 - (#keys=1) "B"."DATA"[NUMBER,22], "A"."DATA"[NUMBER,22],
"B"."DATA"[NUMBER,22]
2 - "A"."ID"[NUMBER,22],
"A"."DATA"[NUMBER,22], "B"."DATA"[NUMBER,22]
3 -
"A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22], "B".ROWID[ROWID,10]
4 - "A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22]
5 -
"A"."ID"[NUMBER,22], "A"."DATA"[NUMBER,22]
6 -
"A".ROWID[ROWID,10]
7 - "B".ROWID[ROWID,10]
8 -
"B"."DATA"[NUMBER,22]
9 - "B"."TAB1_ID"[NUMBER,22],
"B"."DATA"[NUMBER,22]
Note
-----
- this is an
adaptive plan (rows marked '-' are inactive)
--데이터의 변화로 인해 오라클이 동적으로 실행계획을 바꿔준 것임
--PLAN_HASH_VALE가 1599395313로 바뀌었음
SELECT
INST_ID
, SQL_ID
,
CHILD_NUMBER
,
PLAN_HASH_VALUE
, FULL_PLAN_HASH_VALUE
, EXECUTIONS
FROM
GV$SQL
WHERE SQL_ID = '2kj6xq4rd4sbm'
ORDER BY INST_ID,
CHILD_NUMBER
;
INST_ID SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE EXECUTIONS
1 2kj6xq4rd4sbm 0
1599395313 2305938367
1
--> CHILD_NUMBER는 그대로 0임 추가되지 않았음 이게
중요함!
--> PLAN_HASH_VALUE만 기존에 2672205743에서 1599395313로 바뀜
(이렇게 바뀌기 때문에 dba_hist_sqlstat에서 보면 phv가 바뀌는걸로 확인되는 것임, v$의 phv는 갱신 시키는 방식임,
왜냐? 커서를 공유했기 때문에! 하드파싱 안일어나면서
adaptve하게 런터임에 플랜이 변경됐기 때문에!)
--> FULL_PLAN_HASH_VALUE 는 2305938367 로 그전과
동일함!
--결국 FULL_PLAN_HASH_VALUE
2305938367 에는 PLAN_HASH_VALUE 2672205743과 2305938367을 대표하는 대표값임
;
SELECT
A.SQL_ID
, A.HASH_VALUE
, A.PLAN_HASH_VALUE
,
A.FULL_PLAN_HASH_VALUE
,
A.CHILD_NUMBER
, A.ID
,
LPAD (' ', A."DEPTH" ) || OPERATION OPERATION
,
A.OPTIONS
,
A.OBJECT_NAME
,
A.OPTIMIZER
, A.COST
,
A.ACCESS_PREDICATES
,
A.FILTER_PREDICATES
,
A.PROJECTION
, A.ADDRESS
FROM V$SQL_PLAN A
WHERE A.SQL_ID =
'2kj6xq4rd4sbm'
AND CHILD_NUMBER = 0
ORDER BY A.SQL_ID,
A.HASH_VALUE, A.CHILD_NUMBER, A.ID ;
SQL_ID
HASH_VALUE PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE CHILD_NUMBER ID
OPERATION
OPTIONS OBJECT_NAME OPTIMIZER COST
ACCESS_PREDICATES
FILTER_PREDICATES
PROJECTION
ADDRESS
------------- ---------- --------------- -------------------
------------ -- ----------------------------- --------- -------------- ---------
---- ---------------------------- ---------------- --------------------------
----------------
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0 0
SELECT
STATEMENT
ALL_ROWS
3
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
1 HASH
JOIN
3
"B"."TAB1_ID"="A"."ID"
"B"."DATA","A"."DATA","B"."DATA" 0000000077BD0650
2kj6xq4rd4sbm
785539443 1599395313
2305938367
0
2 NESTED
LOOPS
3
"A"."ID","A"."DATA","B"."DATA" 0000000077BD0650
2kj6xq4rd4sbm
785539443 1599395313
2305938367
0
3 NESTED
LOOPS
3
"A"."ID","A"."DATA","B".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
4 STATISTICS
COLLECTOR
"A"."ID","A"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
5 TABLE ACCESS BY INDEX
ROWID BATCHED
TAB1
2
"A"."ID","A"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
6 INDEX RANGE
SCAN
TAB1_CODE
1
"A"."CODE"='ONE'
"A".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
7 INDEX RANGE
SCAN
TAB2_TAB1_FKI
0
"B"."TAB1_ID"="A"."ID"
"B".ROWID
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
8 TABLE ACCESS BY INDEX
ROWID
TAB2
1
"B"."DATA"
0000000077BD0650
2kj6xq4rd4sbm 785539443
1599395313
2305938367
0
9 TABLE ACCESS
FULL
TAB2
1
"B"."TAB1_ID","B"."DATA"
0000000077BD0650
--> 실행 계획은 같음 PLAN_HASH_VALUE만
다름
--> adaptive plan에서 plan_hash_value가 같더라도 full_plan_hash_value가 같다면 같은쿼리라고 판단하면 된다.