------------------------------------------------------------------------------------------------------------
-- 상황 : SQL 의 PLAN 변경으로 긴급 이슈 발생 + 우선 GOOD PLAN 으로 고정 , 이후 GOOD PLAN 으로 소스 변경 및 APPLICATION 수정
------------------------------------------------------------------------------------------------------------
-- 참고 : SPB(SQL PLAN BASELINE)은 12C Enterprise Edition 에서 사용 가능 , 18c 부터는 SE2 도 사용 가능 ( PLAN 하나만 가능, EVOLVE 불가 )
------------------------------------------------------------------------------------------------------------
-- 요약
-- 1. BAD PLAN SQL 등록 ( FROM CACHE WITH ENABLED=NO )
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
, 'VAR CNT NUMBER' AS FIRST_ROWS_LOAD_PLAN
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''');' AS REGISTER_SPM_FROM_CACHE
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''',PLAN_HASH_VALUE=>'''||PLAN_HASH_VALUE||''',ENABLED=>''NO'' );' AS REGISTER_SPM_FROM_CACHE_PLAN_HASH_VALUE_ENABLED_NO
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
-- 2. 힌트를 통해 GOOD PLAN 튜닝
-- 3. GOOD PLAN 등록 ( FROM CACHE IWTH ENABLED=YES )
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
, 'VAR CNT NUMBER' AS FIRST_ROWS_LOAD_PLAN
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''');' AS REGISTER_SPM_FROM_CACHE
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''',PLAN_HASH_VALUE=>'''||PLAN_HASH_VALUE||''',ENABLED=>''YES'' );' AS REGISTER_SPM_FROM_CACHE_PLAN_HASH_VALUE_ENABLED_NO
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
------------------------------------------------------------------------------------------------------------
-- 1. 테스트 테이블 생성
------------------------------------------------------------------------------------------------------------
CREATE TABLE SPB01 ( ID NUMBER, NAME VARCHAR2(100)) ;
--
CREATE TABLE SPB02 ( VID NUMBER, ID NUMBER, NAME VARCHAR2(100)) ;
DECLARE
I NUMBER := 1 ;
BEGIN
FOR I IN 1..100
LOOP
INSERT INTO SPB01 VALUES ( I, I||' INSERTED ');
END LOOP ;
COMMIT;
END ;
DECLARE
I NUMBER := 1 ;
J NUMBER := 1 ;
BEGIN
FOR J IN 1..10000 LOOP
I := ROUND(DBMS_RANDOM.VALUE(1,100)) ;
INSERT INTO SPB02 VALUES ( J, I, I||' INSERTED ');
END LOOP ;
COMMIT;
END ;
CREATE INDEX XPK_SPB01 ON SPB01 ( ID ) ;
ALTER TABLE SPB01 ADD CONSTRAINT XPK_SPB01 PRIMARY KEY ( ID ) ;
CREATE INDEX XPK_SPB02 ON SPB02 ( VID ) ;
CREATE INDEX XAK01_SPB02 ON SPB02 ( ID ) ;
ALTER TABLE SPB02 ADD CONSTRAINT XPK_SPB02 PRIMARY KEY ( VID ) ;
exec dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname=> 'SPB01');
exec dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname=> 'SPB02');
------------------------------------------------------------------------------------------------------------
-- 2. ORG(BAD) PLAN 등록
------------------------------------------------------------------------------------------------------------
-- ORG PLAN
SELECT A.ID, COUNT(*)
FROM SPB01 A, SPB02 B
WHERE A.ID = B.ID
AND A.ID = 1
GROUP BY A.ID
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT GROUP BY NOSORT (cr=3 pr=4 pw=0 str=1 time=299 us cost=1 size=600 card=100)
49 NESTED LOOPS (cr=3 pr=4 pw=0 str=1 time=329 us cost=1 size=600 card=100)
1 INDEX RANGE SCAN XPK_SPB01 (cr=1 pr=0 pw=0 str=1 time=10 us cost=0 size=3 card=1)(Object ID 25181)
49 INDEX RANGE SCAN XAK01_SPB02 (cr=2 pr=4 pw=0 str=1 time=316 us cost=1 size=300 card=100)(Object ID 25190)
-- TUNING(수정) PLAN
SELECT /*+ ORDERED USE_MERGE(B) */ A.ID, COUNT(*)
FROM SPB01 A, SPB02 B
WHERE A.ID = B.ID
AND A.ID = 1
GROUP BY A.ID
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 str=1 time=57 us cost=1 size=600 card=100)
49 MERGE JOIN CARTESIAN (cr=3 pr=0 pw=0 str=1 time=37 us cost=1 size=600 card=100)
1 INDEX RANGE SCAN XPK_SPB01 (cr=1 pr=0 pw=0 str=1 time=10 us cost=0 size=3 card=1)(Object ID 25181)
49 BUFFER SORT (cr=2 pr=0 pw=0 str=1 time=23 us cost=1 size=300 card=100)
49 INDEX RANGE SCAN XAK01_SPB02 (cr=2 pr=0 pw=0 str=1 time=7 us cost=1 size=300 card=100)(Object ID 25190)
set autotrace on explain
SELECT /*+ ORG_SQL */ A.ID, COUNT(*)
FROM SPB01 A, SPB02 B
WHERE A.ID = B.ID
AND A.ID = 1
GROUP BY A.ID ;
ID COUNT(*)
---------- ----------
1 49
Execution Plan
----------------------------------------------------------
Plan hash value: 2071717713
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 1 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 100 | 600 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 100 | 600 | 1 (0)| 00:00:01 | <<< NL 확인
|* 3 | INDEX RANGE SCAN | XPK_SPB01 | 1 | 3 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | XAK01_SPB02 | 100 | 300 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1)
4 - access("B"."ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - ORG_SQL
set autotrace off
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
SQL_ID PLAN_HASH_VALUE SQL_INFO
------------- --------------- ------------------------------------------------------------------------------------------------------------------------
7ck4atqcg485x 2071717713 SELECT /*+ ORG_SQL */ A.ID, C
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
, 'VAR CNT NUMBER' AS FIRST_ROWS_LOAD_PLAN
-- , 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''');' AS REGISTER_SPM_FROM_CACHE
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''',PLAN_HASH_VALUE=>'''||PLAN_HASH_VALUE||''',ENABLED=>''NO'' );' AS REGISTER_SPM_FROM_CACHE_PLAN_HASH_VALUE_ENABLED_NO
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
VAR CNT NUMBER
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'7ck4atqcg485x',PLAN_HASH_VALUE=>'2071717713',ENABLED=>'NO' );
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, OPTIMIZER_COST
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST
------------------------------ ---------------------------------------- ------------------------------ --- --- --- --------------
SQL_e7c63e111e8dca8f SQL_PLAN_fgjjy24g8vkng088ea972 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO 1
-- ENABLED = NO !!!!
-- SQL_HANDLE ='SQL_e7c63e111e8dca8f' Keep
set autotrace on explain
------------------------------------------------------------------------------------------------------------
-- 3. GOOD(TUNING)
------------------------------------------------------------------------------------------------------------
SELECT /*+ ORDERED USE_MERGE(B) TUNING_SQL */ A.ID, COUNT(*)
FROM SPB01 A, SPB02 B
WHERE A.ID = B.ID
AND A.ID = 1
GROUP BY A.ID ;
ID COUNT(*)
---------- ----------
1 49
Execution Plan
----------------------------------------------------------
Plan hash value: 3643076765
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 1 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 100 | 600 | 1 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 100 | 600 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | XPK_SPB01 | 1 | 3 | 0 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 100 | 300 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XAK01_SPB02 | 100 | 300 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1)
5 - access("B"."ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - TUNING_SQL
5 - SEL$1 / B@SEL$1
U - USE_MERGE(B)
set autotrace off
------------------------------------------------------------------------------------------------------------
-- 4. GOOD(TUNING) 등록 FROM CACHE
------------------------------------------------------------------------------------------------------------
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
from v$sql
where sql_text like 'SELECT /*+ ORDERED USE_MERGE(B) TUNING_SQL */%' ;
SQL_ID PLAN_HASH_VALUE SQL_INFO
------------- --------------- ------------------------------------------------------------------------------------------------------------------------
1vc5rsuwbtt75 3643076765 SELECT /*+ ORDERED USE_MERGE(B -- 코드 짤려서 주석으로 막기.. */
select sql_id, plan_hash_value, substr(sql_text,1,30) as sql_info
, 'VAR CNT NUMBER' AS FIRST_ROWS_LOAD_PLAN
-- , 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''');' AS REGISTER_SPM_FROM_CACHE
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'''||sql_id||''',PLAN_HASH_VALUE=>'''||PLAN_HASH_VALUE||''',ENABLED=>''YES'' );' AS REGISTER_SPM_FROM_CACHE_PLAN_HASH_VALUE_ENABLED_NO
from v$sql
where sql_text like 'SELECT /*+ ORDERED USE_MERGE(B) TUNING_SQL */%' ;
VAR CNT NUMBER
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'1vc5rsuwbtt75',PLAN_HASH_VALUE=>'3643076765',ENABLED=>'YES' );
-- (기존 등록한) SQL_HANDLE=>'SQL_e7c63e111e8dca8f' 추가
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'1vc5rsuwbtt75',PLAN_HASH_VALUE=>'3643076765',SQL_HANDLE=>'SQL_e7c63e111e8dca8f',ENABLED=>'YES' );
-- 등록 확인
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, OPTIMIZER_COST
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST
------------------------------ ---------------------------------------- ------------------------------ --- --- --- --------------
SQL_e7c63e111e8dca8f SQL_PLAN_fgjjy24g8vkng088ea972 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO 1
SQL_e7c63e111e8dca8f SQL_PLAN_fgjjy24g8vkng9647415a MANUAL-LOAD-FROM-CURSOR-CACHE YES YES NO 1
ENABLED = YES 로 확인됨
------------------------------------------------------------------------------------------------------------
-- 4. GOOD(TUNING) 등록 FROM AWR
------------------------------------------------------------------------------------------------------------
-- 동일 쿼리는 COLORED SQL 로 등록 ( AWR 등록을 위해서 )
execute dbms_workload_repository.add_colored_sql('1vc5rsuwbtt75')
-- snap_shot 찍기
select dbms_workload_repository.create_snapshot() AS Snap_id from dual ;
-- 해당 쿼리 반복 수행 ( 3회 )
-- snap_shot 찍기
select dbms_workload_repository.create_snapshot() AS Snap_id from dual ;
-- awr ( dba_hist_sqlstat 등록 확인 )
SELECT SNAP_ID, SQL_ID, PLAN_HASH_VALUE
FROM DBA_HIST_SQLSTAT WHERE SQL_ID ='1vc5rsuwbtt75';
SNAP_ID SQL_ID PLAN_HASH_VALUE
---------- ------------- ---------------
50 1vc5rsuwbtt75 3643076765
-- snap_shot 찍기
select dbms_workload_repository.create_snapshot() AS Snap_id from dual ;
SELECT T.*
,'VAR CNT NUMBER ' AS FIRST_ROWS_LOAD_FROM_AWR
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>'||MIN_SNAP_ID||',END_SNAP=>'||MAX_SNAP_ID||', BASIC_FILTER=>q''# SQL_ID='''||sql_id||''' AND PLAN_HASH_VALUE ='||PLAN_HASH_VALUE||' #'');' AS LOAD_FROM_AWR
, 'EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>'||MIN_SNAP_ID||',END_SNAP=>'||MAX_SNAP_ID||', BASIC_FILTER=>q''# SQL_ID='''||sql_id||''' AND PLAN_HASH_VALUE ='||PLAN_HASH_VALUE||' #'', ENABLED=>''NO'');' AS LOAD_FROM_AWR
FROM ( SELECT SQL_ID, PLAN_HASH_VALUE, MIN(SNAP_ID) AS MIN_SNAP_ID, MAX(SNAP_ID) AS MAX_SNAP_ID
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID ='1vc5rsuwbtt75'
GROUP BY SQL_ID, PLAN_HASH_VALUE ) T ;
VAR CNT NUMBER
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>50,END_SNAP=>53, BASIC_FILTER=>q'# SQL_ID='1vc5rsuwbtt75' AND PLAN_HASH_VALUE =3643076765 #');
EXEC :CNT := DBMS_SPM.LOAD_PLANS_FROM_AWR(BEGIN_SNAP=>50,END_SNAP=>53, BASIC_FILTER=>q'# SQL_ID='1vc5rsuwbtt75' AND PLAN_HASH_VALUE =3643076765 #', ENABLED=>'NO');
--등록 확인
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
col sql_plan_baseline for a40
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, OPTIMIZER_COST ,CREATED
FROM DBA_SQL_PLAN_BASELINES ;
-->
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST
------------------------------ ---------------------------------------- ------------------------------ --- --- --- --------------
CREATED
---------------------------------------------------------------------------
SQL_418148b2a312f00b SQL_PLAN_430a8qajj5w0b9647415a MANUAL-LOAD-FROM-AWR NO YES NO 1
21-APR-22 06.03.13.619183 PM
------------------------------------------------------------------------------------------------------------
-- 5. GOOD(TUNING) 작동 확인 <<< 별도로 PURGE 하지 않았음, 바로 적용되었음 ( 좀더 확인 필요 )
------------------------------------------------------------------------------------------------------------
-- 원래 쿼리 수행으로 반영 확인
set autotrace on explain
SELECT /*+ ORG_SQL */ A.ID, COUNT(*)
FROM SPB01 A, SPB02 B
WHERE A.ID = B.ID
AND A.ID = 1
GROUP BY A.ID ;
ID COUNT(*)
---------- ----------
1 49
Execution Plan
----------------------------------------------------------
Plan hash value: 3643076765
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 1 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 100 | 600 | 1 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 100 | 600 | 1 (0)| 00:00:01 | <<< MERGE JOIN 확인됨
|* 3 | INDEX RANGE SCAN | XPK_SPB01 | 1 | 3 | 0 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 100 | 300 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XAK01_SPB02 | 100 | 300 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1)
5 - access("B"."ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - ORG_SQL
Note
-----
- SQL plan baseline "SQL_PLAN_fgjjy24g8vkng9647415a" used for this statement <<< NOTE 로 확인됨
SCOTT@ORACLE19>
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
col sql_plan_baseline for a40
select sql_id, plan_hash_value, SQL_PLAN_BASELINE, substr(sql_text,1,20) as sql_info
from v$sql
where sql_text like 'SELECT /*+ ORG_SQL */%' ;
SQL_ID PLAN_HASH_VALUE SQL_PLAN_BASELINE SQL_INFO
------------- --------------- ---------------------------------------- --------------------------------------------------------------------------------
7ck4atqcg485x 2071717713 SELECT /*+ ORG_SQL * 티스토리 코드 짤림 주석 막기 */
7ck4atqcg485x 3643076765 SQL_PLAN_fgjjy24g8vkng9647415a SELECT /*+ ORG_SQL * 티스토리 코드 짤림 주석 막기 */
------------------------------------------------------------------------------------------------------------
-- 기타1 . SPB PLAN 보기
------------------------------------------------------------------------------------------------------------
SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic') ) t
WHERE s.EXACT_MATCHING_SIGNATURE = b.SIGNATURE
AND b.PLAN_NAME = s.SQL_PLAN_BASELINE
AND b.sql_handle ='SQL_e7c63e111e8dca8f'
AND s.SQL_TEXT NOT LIKE '%DBA_SQL_PLAN_BASELINES%';
-- AND s.SQL_ID='31d96zzzpcys9';
-- AND s.SQL_TEXT LIKE 'select job_title%'
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_e7c63e111e8dca8f
SQL text: SELECT /*+ ORG_SQL */ A.ID, COUNT(*) FROM SPB01 A, SPB02 B WHERE
A.ID = B.ID AND A.ID = 1 GROUP BY A.ID
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_fgjjy24g8vkng9647415a Plan id: 2521252186
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 3643076765
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY NOSORT | |
| 2 | MERGE JOIN CARTESIAN| |
| 3 | INDEX RANGE SCAN | XPK_SPB01 |
| 4 | BUFFER SORT | |
| 5 | INDEX RANGE SCAN | XAK01_SPB02 |
---------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_e7c63e111e8dca8f
SQL text: SELECT /*+ ORG_SQL */ A.ID, COUNT(*) FROM SPB01 A, SPB02 B WHERE
A.ID = B.ID AND A.ID = 1 GROUP BY A.ID
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_fgjjy24g8vkng9647415a Plan id: 2521252186
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 3643076765
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY NOSORT | |
| 2 | MERGE JOIN CARTESIAN| |
| 3 | INDEX RANGE SCAN | XPK_SPB01 |
| 4 | BUFFER SORT | |
| 5 | INDEX RANGE SCAN | XAK01_SPB02 |
---------------------------------------------
50 rows selected.
SCOTT@ORACLE19>SCOTT@ORACLE19>
------------------------------------------------------------------------------------------------------------
-- 기타2 . SPB 수정 및 삭제
------------------------------------------------------------------------------------------------------------
-- ALTER SPB
DECLARE
x number;
BEGIN
x := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SQL_e7c63e111e8dca8f',
'SQL_PLAN_fgjjy24g8vkng9647415a',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
END;
/
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST
------------------------------ ---------------------------------------- ------------------------------ --- --- --- --------------
SQL_e7c63e111e8dca8f SQL_PLAN_fgjjy24g8vkng088ea972 MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO 1
SQL_e7c63e111e8dca8f SQL_PLAN_fgjjy24g8vkng9647415a MANUAL-LOAD-FROM-CURSOR-CACHE NO YES NO 1
set linesize 180
col SQL_HANDLE for a30
col PLAN_NAME for a40
col ORIGIN for a30
-- DROP SPB
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_e7c63e111e8dca8f',plan_name=>null);
END;
/
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, OPTIMIZER_COST
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
-- NO ROWS
------------------------------------------------------------------------------------------------------------
-- 기타2 . SQL_ID 혹은 PLAN 으로 SQL PURGE
------------------------------------------------------------------------------------------------------------
-- RAC 면 노드 별로 수행해야함
--- 특정 SQL 전체(SQL_TEXT 및 SQL_PLAN) PURGE 하기
Select 'execute sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64)' AS PURGE_BY_SQL_ID
From v$sqlarea
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;
--- 특정 SQL의 PLAN만 PURGE 하기
Select 'execute sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64)'
From v$sqlarea
WHERE SQL_TEXT LIKE 'SELECT /*+ ORG_SQL */%' ;