쿼리 튜닝이 필요할 때 , 무조건적으로 믿지는 마시고 하나의 방법으로써 알아두시면 튜닝 시에 좀더 다양한 선택지 중 고려해볼 수 있을 것 같습니다.
1.TASK 생성
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => '3xr6wpczjg3mj',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => '3xr6wpczjg3mj_tuning_task',
description => 'Tuning task for statement 3xr6wpczjg3mj.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2.실행
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '3xr6wpczjg3mj_tuning_task');
3.확인
SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='3xr6wpczjg3mj_tuning_task';
OWNER TASK_NAME EXECUTION_START EXECUTION_END STATUS
------------- ---------------------------- ------------------------------ -------------------------- ------------
IBMDBA04 3xr6wpczjg3mj_tuning_task 2023/03/10 10:46:17 2023/03/10 11:15:16 COMPLETED
4.튜닝 리포트 확인(sqlplus에서 확인하는 것이 잘 보임)
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
col RECOMMENT FORMAT a350
SELECT DBMS_SQLTUNE.report_tuning_task('3xr6wpczjg3mj_tuning_task') AS RECOMMENT FROM dual;
전체 내용은 아래의 접은글 참고
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 3xr6wpczjg3mj_tuning_task
Tuning Task Owner : IBMDBA04
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/10/2023 10:46:17
Completed at : 03/10/2023 11:15:16
-------------------------------------------------------------------------------
Schema Name: IBMDBA04
SQL ID : 3xr6wpczjg3mj
SQL Text : WITH PAY_INFO
AS (
SELECT /*+ USE_HASH(A B) PARALLEL(16) FULL(A) FULL(B) */
A.BS_YM
, A.OCC_DT
, A.UPM_IS_DV_CD
, A.UPM_IS_TGT_DV_CD
, A.PN_TGT_AMT
, A.DDC_AMT
, A.RPYN_AMT
, A.PI_RK
/* , B.IS_PLNO
, B.IS_DAW_HIS_ID
, B.RCV_AMT
, B.DAW_OCC_DTL_RSN_CD
, B.DAW_DT */
,B.*
FROM EDWETL.DM_FIP012L_T02 A,
DM_FIP079L B ---DM_FIP지급입출금내역
WHERE 1 = 1
AND A.IS_PLNO = B.IS_PLNO
AND ( A.UPM_IS_TGT_DV_CD IN ('#','4')
OR A.OCC_DT >= :B2 )
AND B.DAW_OCC_DTL_RSN_CD IN
('54110','54111','55010','55011'
,'55000','55001','55011','55012'
,'54112','54113','57003','57004')
AND B.DAW_CNL_DTM = :B1
AND B.PN_AMT > 0
AND to_char(B.DAW_DT,'YYYYMM') = A.BS_YM
-- AND A.UPM_IS_DV_CD = '2'
-- AND A.PI_RK = 1
)
SELECT /*YSBAE0*/ /*+ USE_HASH(A C
D) FULL(A) FULL(C) FULL(D) PARALLEL(16) */
A.BS_YM AS BS_YM
, A.IS_PLNO AS
IS_PLNO
, A.OCC_DT AS OCC_DT
, A.UPM_IS_DV_CD AS
UPM_IS_DV_CD
, A.UPM_IS_TGT_DV_CD AS
UPM_IS_TGT_DV_CD
, max(CASE
WHEN A.DAW_DT <= :B4
THEN A.DAW_DT
ELSE :B3
END) AS PN_DT
, max(D.PN_PRG_DT) AS
PN_PRG_DT
, max(A.PN_TGT_AMT) AS
PN_TGT_AMT
, max(CASE
WHEN A.DAW_DT <= :B4
THEN D.DAW_PCP
ELSE 0
END) AS
ISAM_PN_AMT
, min(CASE
WHEN A.DAW_DT <= :B4
THEN D.RCP_PTH_DV_CD
ELSE '#'
END) AS
RCP_PTH_DV_CD
, min(CASE
WHEN A.DAW_DT <= :B4
THEN A.DAW_OCC_DTL_RSN_CD
ELSE '#'
END) AS
DAW_OCC_DTL_RSN_CD
, max(nvl(D.ATN_AMT,0)) AS
ATN_AMT
, max(CASE
WHEN A.DDC_AMT = 0
THEN A.RCV_AMT
ELSE A.DDC_AMT
END) AS
DDC_AMT
, max(CASE
WHEN A.RPYN_AMT = 0
THEN D.DAW_PCP
ELSE A.RPYN_AMT
END) AS
RPYN_AMT
FROM PAY_INFO A,
DW_UD_UDB4102 C,
---DW_UDB_보험입출금관계
DM_FIP077L D
---DM_FIP지급입출금거래내역
WHERE 1 = 1
AND A.IS_DAW_HIS_ID =
C.IS_DAW_HIS_ID
AND C.IS_DAW_PRG_ID =
D.IS_DAW_PRG_ID
AND D.IS_PN_KD_CD = '02'
AND D.DAW_ECA_DV_CD = '1'
AND A.UPM_IS_DV_CD = '2'
AND D.PN_PRG_DT <> :B3
AND D.PN_PRG_DT = A.OCC_DT
AND D.DAW_CNL_DTM = :B1
AND A.PI_RK = 1
GROUP BY
A.BS_YM
, A.IS_PLNO
, A.OCC_DT
, A.UPM_IS_DV_CD
, A.UPM_IS_TGT_DV_CD
Bind Variables :
1 - (DATE):03/01/2023 00:00:00
2 - (DATE):12/31/9999 23:59:59
4 - (DATE):01/01/0001 00:00:00
8 - (DATE):01/01/0001 00:00:00
9 - (DATE):12/31/9999 23:59:59
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'3xr6wpczjg3mj_tuning_task', task_owner => 'IBMDBA04', replace =>
TRUE);
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 83.65%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EDWOWN.IDX$$_DC6430001 on EDWOWN.DM_FIP079L("DAW_CNL_DTM");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EDWOWN.IDX$$_DC6430002 on
EDWOWN.DM_FIP077L("DAW_CNL_DTM","DAW_ECA_DV_CD","IS_PN_KD_CD");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 30792600
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1015 | 423K (1)| 00:00:17 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 5 | 1015 | 423K (1)| 00:00:17 | Q1,05 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,05 | PCWP | |
| 5 | PX SEND HASH | :TQ10004 | 5 | 1015 | 423K (1)| 00:00:17 | Q1,04 | P->P | HASH |
| 6 | HASH GROUP BY | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,04 | PCWP | |
|* 7 | HASH JOIN | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,04 | PCWP | |
| 8 | JOIN FILTER CREATE | :BF0000 | 2510K| 378M| 422K (1)| 00:00:17 | Q1,04 | PCWP | |
| 9 | PX RECEIVE | | 2510K| 378M| 422K (1)| 00:00:17 | Q1,04 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10003 | 2510K| 378M| 422K (1)| 00:00:17 | Q1,03 | P->P | BROADCAST |
|* 11 | HASH JOIN BUFFERED | | 2510K| 378M| 422K (1)| 00:00:17 | Q1,03 | PCWP | |
| 12 | JOIN FILTER CREATE | :BF0001 | 24M| 2477M| 286K (1)| 00:00:12 | Q1,03 | PCWP | |
| 13 | PX RECEIVE | | 24M| 2477M| 286K (1)| 00:00:12 | Q1,03 | PCWP | |
| 14 | PX SEND HASH | :TQ10001 | 24M| 2477M| 286K (1)| 00:00:12 | Q1,01 | P->P | HASH |
|* 15 | HASH JOIN | | 24M| 2477M| 286K (1)| 00:00:12 | Q1,01 | PCWP | |
| 16 | JOIN FILTER CREATE | :BF0002 | 21M| 1309M| 217K (1)| 00:00:09 | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 21M| 1309M| 217K (1)| 00:00:09 | Q1,01 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ10000 | 21M| 1309M| 217K (1)| 00:00:09 | Q1,00 | P->P | BROADCAST |
| 19 | PX BLOCK ITERATOR | | 21M| 1309M| 217K (1)| 00:00:09 | Q1,00 | PCWC | |
|* 20 | TABLE ACCESS STORAGE FULL| DM_FIP079L | 21M| 1309M| 217K (1)| 00:00:09 | Q1,00 | PCWP | |
| 21 | JOIN FILTER USE | :BF0002 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWC | |
|* 23 | TABLE ACCESS STORAGE FULL | DW_UD_UDB4102 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWP | |
| 24 | PX RECEIVE | | 209M| 10G| 136K (1)| 00:00:06 | Q1,03 | PCWP | |
| 25 | PX SEND HASH | :TQ10002 | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | P->P | HASH |
| 26 | JOIN FILTER USE | :BF0001 | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWP | |
| 27 | PX BLOCK ITERATOR | | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWC | |
|* 28 | TABLE ACCESS STORAGE FULL | DM_FIP077L | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWP | |
| 29 | JOIN FILTER USE | :BF0000 | 724K| 31M| 237 (1)| 00:00:01 | Q1,04 | PCWP | |
| 30 | PX BLOCK ITERATOR | | 724K| 31M| 237 (1)| 00:00:01 | Q1,04 | PCWC | |
|* 31 | TABLE ACCESS STORAGE FULL | DM_FIP012L_T02 | 724K| 31M| 237 (1)| 00:00:01 | Q1,04 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("D"."PN_PRG_DT"="A"."OCC_DT" AND "A"."IS_PLNO"="B"."IS_PLNO" AND
"A"."BS_YM"=TO_CHAR(INTERNAL_FUNCTION("B"."DAW_DT"),'YYYYMM'))
11 - access("C"."IS_DAW_PRG_ID"="D"."IS_DAW_PRG_ID")
15 - access("B"."IS_DAW_HIS_ID"="C"."IS_DAW_HIS_ID")
20 - storage("B"."DAW_CNL_DTM"=:B1 AND ("B"."DAW_OCC_DTL_RSN_CD"='54110' OR "B"."DAW_OCC_DTL_RSN_CD"='54111' OR
"B"."DAW_OCC_DTL_RSN_CD"='54112' OR "B"."DAW_OCC_DTL_RSN_CD"='54113' OR "B"."DAW_OCC_DTL_RSN_CD"='55000' OR
"B"."DAW_OCC_DTL_RSN_CD"='55001' OR "B"."DAW_OCC_DTL_RSN_CD"='55010' OR "B"."DAW_OCC_DTL_RSN_CD"='55011' OR
"B"."DAW_OCC_DTL_RSN_CD"='55012' OR "B"."DAW_OCC_DTL_RSN_CD"='57003' OR "B"."DAW_OCC_DTL_RSN_CD"='57004') AND "B"."PN_AMT">0)
filter("B"."DAW_CNL_DTM"=:B1 AND ("B"."DAW_OCC_DTL_RSN_CD"='54110' OR "B"."DAW_OCC_DTL_RSN_CD"='54111' OR
"B"."DAW_OCC_DTL_RSN_CD"='54112' OR "B"."DAW_OCC_DTL_RSN_CD"='54113' OR "B"."DAW_OCC_DTL_RSN_CD"='55000' OR
"B"."DAW_OCC_DTL_RSN_CD"='55001' OR "B"."DAW_OCC_DTL_RSN_CD"='55010' OR "B"."DAW_OCC_DTL_RSN_CD"='55011' OR
"B"."DAW_OCC_DTL_RSN_CD"='55012' OR "B"."DAW_OCC_DTL_RSN_CD"='57003' OR "B"."DAW_OCC_DTL_RSN_CD"='57004') AND "B"."PN_AMT">0)
23 - storage(SYS_OP_BLOOM_FILTER(:BF0002,"C"."IS_DAW_HIS_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0002,"C"."IS_DAW_HIS_ID"))
28 - storage("D"."DAW_CNL_DTM"=:B1 AND "D"."DAW_ECA_DV_CD"='1' AND "D"."IS_PN_KD_CD"='02' AND "D"."PN_PRG_DT"<>:B3 AND
SYS_OP_BLOOM_FILTER(:BF0001,"D"."IS_DAW_PRG_ID"))
filter("D"."DAW_CNL_DTM"=:B1 AND "D"."DAW_ECA_DV_CD"='1' AND "D"."IS_PN_KD_CD"='02' AND "D"."PN_PRG_DT"<>:B3 AND
SYS_OP_BLOOM_FILTER(:BF0001,"D"."IS_DAW_PRG_ID"))
31 - storage("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."OCC_DT","A"."IS_PLNO","A"."BS_YM"))
filter("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3 AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."OCC_DT","A"."IS_PLNO","A"."BS_YM"))
2- Using SQL Profile
--------------------
Plan hash value: 2237336974
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1015 | 423K (1)| 00:00:17 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 5 | 1015 | 423K (1)| 00:00:17 | Q1,03 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 5 | 1015 | 423K (1)| 00:00:17 | Q1,02 | P->P | HASH |
| 6 | HASH GROUP BY | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 5 | 1015 | 423K (1)| 00:00:17 | Q1,02 | PCWP | |
| 8 | JOIN FILTER CREATE | :BF0000 | 1029K| 148M| 286K (1)| 00:00:12 | Q1,02 | PCWP | |
| 9 | PX RECEIVE | | 1029K| 148M| 286K (1)| 00:00:12 | Q1,02 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10001 | 1029K| 148M| 286K (1)| 00:00:12 | Q1,01 | P->P | BROADCAST |
|* 11 | HASH JOIN | | 1029K| 148M| 286K (1)| 00:00:12 | Q1,01 | PCWP | |
| 12 | JOIN FILTER CREATE | :BF0001 | 901K| 93M| 217K (1)| 00:00:09 | Q1,01 | PCWP | |
| 13 | PX RECEIVE | | 901K| 93M| 217K (1)| 00:00:09 | Q1,01 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10000 | 901K| 93M| 217K (1)| 00:00:09 | Q1,00 | P->P | BROADCAST |
|* 15 | HASH JOIN | | 901K| 93M| 217K (1)| 00:00:09 | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS STORAGE FULL | DM_FIP012L_T02 | 690K| 29M| 237 (1)| 00:00:01 | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 21M| 1309M| 217K (1)| 00:00:09 | Q1,00 | PCWC | |
|* 18 | TABLE ACCESS STORAGE FULL| DM_FIP079L | 21M| 1309M| 217K (1)| 00:00:09 | Q1,00 | PCWP | |
| 19 | JOIN FILTER USE | :BF0001 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWC | |
|* 21 | TABLE ACCESS STORAGE FULL | DW_UD_UDB4102 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,01 | PCWP | |
| 22 | JOIN FILTER USE | :BF0000 | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWP | |
| 23 | PX BLOCK ITERATOR | | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWC | |
|* 24 | TABLE ACCESS STORAGE FULL | DM_FIP077L | 209M| 10G| 136K (1)| 00:00:06 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C"."IS_DAW_PRG_ID"="D"."IS_DAW_PRG_ID" AND "D"."PN_PRG_DT"="A"."OCC_DT")
11 - access("B"."IS_DAW_HIS_ID"="C"."IS_DAW_HIS_ID")
15 - access("A"."IS_PLNO"="B"."IS_PLNO" AND "A"."BS_YM"=TO_CHAR(INTERNAL_FUNCTION("B"."DAW_DT"),'YYYYMM'))
16 - storage("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3)
filter("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3)
18 - storage("B"."DAW_CNL_DTM"=:B1 AND ("B"."DAW_OCC_DTL_RSN_CD"='54110' OR "B"."DAW_OCC_DTL_RSN_CD"='54111' OR
"B"."DAW_OCC_DTL_RSN_CD"='54112' OR "B"."DAW_OCC_DTL_RSN_CD"='54113' OR "B"."DAW_OCC_DTL_RSN_CD"='55000' OR
"B"."DAW_OCC_DTL_RSN_CD"='55001' OR "B"."DAW_OCC_DTL_RSN_CD"='55010' OR "B"."DAW_OCC_DTL_RSN_CD"='55011' OR
"B"."DAW_OCC_DTL_RSN_CD"='55012' OR "B"."DAW_OCC_DTL_RSN_CD"='57003' OR "B"."DAW_OCC_DTL_RSN_CD"='57004') AND "B"."PN_AMT">0)
filter("B"."DAW_CNL_DTM"=:B1 AND ("B"."DAW_OCC_DTL_RSN_CD"='54110' OR "B"."DAW_OCC_DTL_RSN_CD"='54111' OR
"B"."DAW_OCC_DTL_RSN_CD"='54112' OR "B"."DAW_OCC_DTL_RSN_CD"='54113' OR "B"."DAW_OCC_DTL_RSN_CD"='55000' OR
"B"."DAW_OCC_DTL_RSN_CD"='55001' OR "B"."DAW_OCC_DTL_RSN_CD"='55010' OR "B"."DAW_OCC_DTL_RSN_CD"='55011' OR
"B"."DAW_OCC_DTL_RSN_CD"='55012' OR "B"."DAW_OCC_DTL_RSN_CD"='57003' OR "B"."DAW_OCC_DTL_RSN_CD"='57004') AND "B"."PN_AMT">0)
21 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"C"."IS_DAW_HIS_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"C"."IS_DAW_HIS_ID"))
24 - storage("D"."DAW_CNL_DTM"=:B1 AND "D"."DAW_ECA_DV_CD"='1' AND "D"."IS_PN_KD_CD"='02' AND "D"."PN_PRG_DT"<>:B3 AND
SYS_OP_BLOOM_FILTER(:BF0000,"D"."IS_DAW_PRG_ID","D"."PN_PRG_DT"))
filter("D"."DAW_CNL_DTM"=:B1 AND "D"."DAW_ECA_DV_CD"='1' AND "D"."IS_PN_KD_CD"='02' AND "D"."PN_PRG_DT"<>:B3 AND
SYS_OP_BLOOM_FILTER(:BF0000,"D"."IS_DAW_PRG_ID","D"."PN_PRG_DT"))
3- Using New Indices
--------------------
Plan hash value: 3740406368
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1015 | 69191 (3)| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10008 | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,08 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,08 | PCWP | |
| 4 | PX RECEIVE | | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,08 | PCWP | |
| 5 | PX SEND HASH | :TQ10007 | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,07 | P->P | HASH |
| 6 | HASH GROUP BY | | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,07 | PCWP | |
|* 7 | HASH JOIN | | 5 | 1015 | 69191 (3)| 00:00:03 | Q1,07 | PCWP | |
| 8 | PX RECEIVE | | 1029K| 148M| 69145 (3)| 00:00:03 | Q1,07 | PCWP | |
| 9 | PX SEND HASH | :TQ10005 | 1029K| 148M| 69145 (3)| 00:00:03 | Q1,05 | P->P | HASH |
|* 10 | HASH JOIN | | 1029K| 148M| 69145 (3)| 00:00:03 | Q1,05 | PCWP | |
| 11 | JOIN FILTER CREATE | :BF0000 | 901K| 93M| 259 (3)| 00:00:01 | Q1,05 | PCWP | |
| 12 | PX RECEIVE | | 901K| 93M| 259 (3)| 00:00:01 | Q1,05 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ10003 | 901K| 93M| 259 (3)| 00:00:01 | Q1,03 | P->P | BROADCAST |
|* 14 | HASH JOIN BUFFERED | | 901K| 93M| 259 (3)| 00:00:01 | Q1,03 | PCWP | |
| 15 | PX RECEIVE | | 690K| 29M| 237 (1)| 00:00:01 | Q1,03 | PCWP | |
| 16 | PX SEND HASH | :TQ10001 | 690K| 29M| 237 (1)| 00:00:01 | Q1,01 | P->P | HASH |
| 17 | PX BLOCK ITERATOR | | 690K| 29M| 237 (1)| 00:00:01 | Q1,01 | PCWC | |
|* 18 | TABLE ACCESS STORAGE FULL | DM_FIP012L_T02 | 690K| 29M| 237 (1)| 00:00:01 | Q1,01 | PCWP | |
| 19 | PX RECEIVE | | 21M| 1309M| 18 (0)| 00:00:01 | Q1,03 | PCWP | |
| 20 | PX SEND HASH | :TQ10002 | 21M| 1309M| 18 (0)| 00:00:01 | Q1,02 | P->P | HASH |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED| DM_FIP079L | 21M| 1309M| 18 (0)| 00:00:01 | Q1,02 | PCWP | |
| 22 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 23 | PX RECEIVE | | 247 | | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 24 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 247 | | 4 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 25 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 26 | INDEX RANGE SCAN | IDX$$_DC6430001 | 247 | | 4 (0)| 00:00:01 | Q1,00 | SCWP | |
| 27 | JOIN FILTER USE | :BF0000 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,05 | PCWP | |
| 28 | PX BLOCK ITERATOR | | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,05 | PCWC | |
|* 29 | TABLE ACCESS STORAGE FULL | DW_UD_UDB4102 | 3273M| 128G| 68300 (2)| 00:00:03 | Q1,05 | PCWP | |
| 30 | PX RECEIVE | | 209M| 10G| 8 (0)| 00:00:01 | Q1,07 | PCWP | |
| 31 | PX SEND HASH | :TQ10006 | 209M| 10G| 8 (0)| 00:00:01 | Q1,06 | P->P | HASH |
|* 32 | TABLE ACCESS BY INDEX ROWID BATCHED | DM_FIP077L | 209M| 10G| 8 (0)| 00:00:01 | Q1,06 | PCWP | |
| 33 | BUFFER SORT | | | | | | Q1,06 | PCWC | |
| 34 | PX RECEIVE | | 42 | | 4 (0)| 00:00:01 | Q1,06 | PCWP | |
| 35 | PX SEND HASH (BLOCK ADDRESS) | :TQ10004 | 42 | | 4 (0)| 00:00:01 | Q1,04 | S->P | HASH (BLOCK|
| 36 | PX SELECTOR | | | | | | Q1,04 | SCWC | |
|* 37 | INDEX RANGE SCAN | IDX$$_DC6430002 | 42 | | 4 (0)| 00:00:01 | Q1,04 | SCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C"."IS_DAW_PRG_ID"="D"."IS_DAW_PRG_ID" AND "D"."PN_PRG_DT"="A"."OCC_DT")
10 - access("B"."IS_DAW_HIS_ID"="C"."IS_DAW_HIS_ID")
14 - access("A"."IS_PLNO"="B"."IS_PLNO" AND "A"."BS_YM"=TO_CHAR(INTERNAL_FUNCTION("B"."DAW_DT"),'YYYYMM'))
18 - storage("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3)
filter("A"."PI_RK"=1 AND "A"."UPM_IS_DV_CD"='2' AND (("A"."UPM_IS_TGT_DV_CD"='#' OR "A"."UPM_IS_TGT_DV_CD"='4') OR
"A"."OCC_DT">=:B2) AND "A"."OCC_DT"<>:B3)
21 - filter(("B"."DAW_OCC_DTL_RSN_CD"='54110' OR "B"."DAW_OCC_DTL_RSN_CD"='54111' OR "B"."DAW_OCC_DTL_RSN_CD"='54112' OR
"B"."DAW_OCC_DTL_RSN_CD"='54113' OR "B"."DAW_OCC_DTL_RSN_CD"='55000' OR "B"."DAW_OCC_DTL_RSN_CD"='55001' OR
"B"."DAW_OCC_DTL_RSN_CD"='55010' OR "B"."DAW_OCC_DTL_RSN_CD"='55011' OR "B"."DAW_OCC_DTL_RSN_CD"='55012' OR
"B"."DAW_OCC_DTL_RSN_CD"='57003' OR "B"."DAW_OCC_DTL_RSN_CD"='57004') AND "B"."PN_AMT">0)
26 - access("B"."DAW_CNL_DTM"=:B1)
29 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"C"."IS_DAW_HIS_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"C"."IS_DAW_HIS_ID"))
32 - filter("D"."PN_PRG_DT"<>:B3)
37 - access("D"."DAW_CNL_DTM"=:B1 AND "D"."DAW_ECA_DV_CD"='1' AND "D"."IS_PN_KD_CD"='02')
-------------------------------------------------------------------------------
내용 중 아래에서 튜닝포인트를 권고해줍니다.
저의 경우에는 SQL profile을 고정하는것과 인덱스의 생성을 권고해주네요
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'3xr6wpczjg3mj_tuning_task', task_owner => 'IBMDBA04', replace =>
TRUE);
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 83.65%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EDWOWN.IDX$$_DC6430001 on EDWOWN.DM_FIP079L("DAW_CNL_DTM");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index EDWOWN.IDX$$_DC6430002 on
EDWOWN.DM_FIP077L("DAW_CNL_DTM","DAW_ECA_DV_CD","IS_PN_KD_CD");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
5.TASK 삭제
--## 삭제전 내용 확인
SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='3xr6wpczjg3mj_tuning_task';
OWNER TASK_NAME EXECUTION_START EXECUTION_END STATUS
------------- ---------------------------- ------------------------------ -------------------------- ------------
IBMDBA04 3xr6wpczjg3mj_tuning_task 2023/03/10 10:46:17 2023/03/10 11:15:16 COMPLETED
--## 삭제 하기
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => '3xr6wpczjg3mj_tuning_task');
END;
/
'Oracle > 튜닝' 카테고리의 다른 글
| 플랜 고정해주는 sql profile disable/drop (1) | 2023.12.18 |
|---|---|
| 공간관리와 Direct Path Load (0) | 2023.05.10 |
| [DBMS_SQLTUNE] Profile로 SQL변경없이 실행계획 변경해보기 (0) | 2023.01.20 |
| Dynamic Sampling에 대한 개념 및 테스트 (0) | 2022.12.27 |
| Join Factorization (0) | 2022.10.28 |
댓글