====== PC_MC_TRD_AUTH 테이블 실행 계획 변경 이슈 분석 (10053 trace 사용) ========
#################### 정리
//** 발생 이슈 : 2018년 1월 2일 PN_MC_TRD_AUTH관련 쿼리의 실행계획이 변경됨 (2018/01/02 04시경, 2018/01/02/22시경)
- Auth_dt 컬럼이 포함된 인덱스인 IDX_PN_MC_TRD_AUTH_01를 사용하도록 플랜이 변경되었으며 이로 인해 성능 이슈 발생
- IDX_PN_MC_TRD_AUTH_01를 사용한 쿼리의 조건절은 "auth_dt IN" 또는 "auth_dt >=" 사용
//** 조치 사항 : Profile 적용하여 변경 전 실행계획으로 고정 (2018/01/02 23:30경)
//** 원인 분석 : 해당 쿼리에 대해 10053 Trace를 생성하여 Cost 계산하는 부분 분석
- auth_dt에 히스토그램이 생성되어 있음 (High Value : 20160113)
- 2016년 1월 13일 이후 Auth_dt 포함된 인덱스의 Cost는 "out-of-range" 메시지가 보이며 prorated density로 계산됨
- prorate = 할당하다, density = 특정 컬럼의 농도 (1/NDV)
- prorated density : 2016년 1월 "0.001689" >> 2017년 1월 "0.000824" >> 2017년 12월 "0.000729" >> 2018년 1월 "0"
- 2018년이 된 후 auth_dt의 proated density가 "0"이 됨으로써 포함 인덱스의 Cost가 낮아져 옵티마이저가 해당 인덱스 선택
//** 2018년에 갑자기 prorated density가 낮아진 이유 : Auth_dt는 날짜로 만들었지만 Column Type이 Varchar이기 때문
- 20180101 - 20171231 = 8,870 (1이 아님)
- Auth_dt에 히스토그램이 생성된 이유도 0101 ~ 1231에 데이터가 몰려있고
나머지 8,870에는 데이터가 없기 때문에 데이터가 Skew(편향) 되어 있음
//** 대응 방안 : 파티션에 Between으로 조건 부여 시 Histogram 사용하지 않음
명확히 사용할 인덱스가 정해진 경우에는 Hint 사용 고려
날짜 컬럼에는 Histogram 사용하지 않음. 기존에 생성된 경우는 삭제 고려
기타 날짜를 Varchar Type으로 사용하는 주요 쿼리 및 테이블 조사 필요
#################### 트레이스 생성 ( 1번째 쿼리, 2017년 12월 조회 )
------------------ 실행 명령어
variable B0 VARCHAR2(32)
variable B1 VARCHAR2(20)
variable B2 VARCHAR2(50)
variable B3 NUMBER
begin -- 바인드 변수가 개인정보라 제거함
:B0 := '; -카드번호
:B1 := ''; -핸드폰번호
:B2 := ''; -ㅁㅁㅁㅁ
:B3 := ; -ㅁㅁㅁㅁ
end;
/
alter session set events '10053 trace name context forever, level 1';
SELECT /*+ pn_mc_trd_auth_vf001 */
substr(auth_tmp, 1, 14) auth_dtm,
trim(substr(auth_tmp, 15, 20)) appr_trid,
substr(auth_tmp, 35, 6) auth_no,
substr(sign_tmp, 1, 14) sign_time,
trim(substr(sign_tmp, 15, 20)) sign_trid
FROM
(SELECT
( ( ( t1.auth_dt
||t1.auth_tm )
||rpad(t1.m_trd_no, 20) )
||t1.auth_no ) auth_tmp,
( ( t2.trd_dt
||t2.trd_tm )
||t2.m_trd_no ) sign_tmp
FROM
motrd.pn_mc_trd_auth t1,
motrd.pn_m_trd t2
WHERE ( ( ( ( ( ( ( t1.auth_dt IN ( to_char(( SYSDATE - 20 ), 'yyyymmdd'), to_char(SYSDATE-19, 'yyyymmdd') )
AND t1.m_trd_no = t2.m_trd_no(+) )
AND t1.auth_dt <= t2.trd_dt(+) )
AND t1.req_trd_dtm > ( SYSDATE -21 - ( + 30 / ( 24 * 60 ) ) ) )
AND trim(t1.svc_id) = :b0 )
AND t1.phone_no = :b1 )
AND rtrim(t1.mrct_trd_no) = :b2 )
AND ( ( ( t1.trd_amt + t1.dc_amt ) + t1.ken_dc_amt ) + t1.ec_amt ) = :b3 )
ORDER BY auth_tmp DESC)
WHERE ROWNUM < 6;
alter session set events '10053 trace name context off';
PPMT_ora_5872.trc
------------------ 실행 계획
## plan 긁어서 메모장이나... db tool 에 붙여넣으면 보임... tistory 에서 plan 이쁘게 정렬 어찌함 ..?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 448 (100)| | | | 1 |00:00:00.01 | 23 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | | 1 |00:00:00.01 | 23 | | | |
| 2 | VIEW | | 1 | 1 | 51 | 448 (1)| 00:00:06 | | | 1 |00:00:00.01 | 23 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1 | 158 | 448 (1)| 00:00:06 | | | 1 |00:00:00.01 | 23 | 2048 | 2048 | 2048 (0)|
| 4 | NESTED LOOPS OUTER | | 1 | 1 | 158 | 447 (0)| 00:00:06 | | | 1 |00:00:00.01 | 23 | | | |
| 5 | PARTITION RANGE INLIST | | 1 | 1 | 125 | 230 (0)| 00:00:03 |KEY(I) |KEY(I) | 1 |00:00:00.01 | 16 | | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| PN_MC_TRD_AUTH | 1 | 1 | 125 | 230 (0)| 00:00:03 |KEY(I) |KEY(I) | 1 |00:00:00.01 | 16 | | | |
|* 7 | INDEX RANGE SCAN | IDX_PN_MC_TRD_AUTH_02 | 1 | 13 | | 217 (0)| 00:00:03 |KEY(I) |KEY(I) | 13 |00:00:00.01 | 3 | | | |
| 8 | PARTITION RANGE ITERATOR | | 1 | 1 | 33 | 217 (0)| 00:00:03 | KEY | 108 | 0 |00:00:00.01 | 7 | | | |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| PN_M_TRD | 13 | 1 | 33 | 217 (0)| 00:00:03 | KEY | 108 | 0 |00:00:00.01 | 7 | | | |
|* 10 | INDEX RANGE SCAN | PK_PN_M_TRD | 2 | 1 | | 216 (0)| 00:00:03 | KEY | 108 | 0 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#################### 트레이스 생성 ( 2번째 쿼리, 2018년 1월 조회 )
------------------ 실행 명령어
variable B0 VARCHAR2(32)
variable B1 VARCHAR2(20)
variable B2 VARCHAR2(50)
variable B3 NUMBER
begin
end;
/
alter session set events '10053 trace name context forever, level 1';
SELECT /*+ pn_mc_trd_auth_vf001 */
substr(auth_tmp, 1, 14) auth_dtm,
trim(substr(auth_tmp, 15, 20)) appr_trid,
substr(auth_tmp, 35, 6) auth_no,
substr(sign_tmp, 1, 14) sign_time,
trim(substr(sign_tmp, 15, 20)) sign_trid
FROM
(SELECT
( ( ( t1.auth_dt
||t1.auth_tm )
||rpad(t1.m_trd_no, 20) )
||t1.auth_no ) auth_tmp,
( ( t2.trd_dt
||t2.trd_tm )
||t2.m_trd_no ) sign_tmp
FROM
motrd.pn_mc_trd_auth t1,
motrd.pn_m_trd t2
WHERE ( ( ( ( ( ( ( t1.auth_dt IN ( to_char(( SYSDATE - 2 ), 'yyyymmdd'), to_char(SYSDATE-1, 'yyyymmdd') )
AND t1.m_trd_no = t2.m_trd_no(+) )
AND t1.auth_dt <= t2.trd_dt(+) )
AND t1.req_trd_dtm > ( SYSDATE -3 - ( 30 / ( 24 * 60 ) ) ) )
AND trim(t1.svc_id) = :b0 )
AND t1.phone_no = :b1 )
AND rtrim(t1.mrct_trd_no) = :b2 )
AND ( ( ( t1.trd_amt + t1.dc_amt ) + t1.ken_dc_amt ) + t1.ec_amt ) = :b3 )
ORDER BY auth_tmp DESC)
WHERE ROWNUM < 6;
alter session set events '10053 trace name context off';
PPMT_ora_6653.trc
------------------ 실행 계획
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 63 (100)| | | | 2 |00:00:00.86 | 40463 | 2 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | | 2 |00:00:00.86 | 40463 | 2 | | | |
| 2 | VIEW | | 1 | 2 | 102 | 63 (2)| 00:00:01 | | | 2 |00:00:00.86 | 40463 | 2 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 2 | 316 | 63 (2)| 00:00:01 | | | 2 |00:00:00.86 | 40463 | 2 | 2048 | 2048 | 2048 (0)|
| 4 | CONCATENATION | | 1 | | | | | | | 2 |00:00:00.43 | 40463 | 2 | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 1 | 158 | 31 (0)| 00:00:01 | | | 1 |00:00:00.43 | 20231 | 1 | | | |
| 6 | INLIST ITERATOR | | 1 | | | | | | | 1 |00:00:00.43 | 20228 | 0 | | | |
| 7 | PARTITION RANGE ITERATOR | | 2 | 1 | 125 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) | 1 |00:00:00.43 | 20228 | 0 | | | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| PN_MC_TRD_AUTH | 2 | 1 | 125 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) | 1 |00:00:00.43 | 20228 | 0 | | | |
|* 9 | INDEX RANGE SCAN | IDX_PN_MC_TRD_AUTH_01 | 2 | 1 | | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 6398 |00:00:00.01 | 13875 | 0 | | | |
| 10 | PARTITION RANGE AND | | 1 | 1 | 33 | 25 (0)| 00:00:01 |KEY(AP)| 108 | 0 |00:00:00.01 | 3 | 1 | | | |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | PN_M_TRD | 12 | 1 | 33 | 25 (0)| 00:00:01 |KEY(AP)| 108 | 0 |00:00:00.01 | 3 | 1 | | | |
|* 12 | INDEX RANGE SCAN | PK_PN_M_TRD | 1 | 1 | | 24 (0)| 00:00:01 |KEY(AP)| 108 | 0 |00:00:00.01 | 3 | 1 | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 1 | 158 | 31 (0)| 00:00:01 | | | 1 |00:00:00.43 | 20232 | 1 | | | |
| 14 | INLIST ITERATOR | | 1 | | | | | | | 1 |00:00:00.43 | 20228 | 0 | | | |
| 15 | PARTITION RANGE ITERATOR | | 2 | 1 | 125 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) | 1 |00:00:00.43 | 20228 | 0 | | | |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID| PN_MC_TRD_AUTH | 2 | 1 | 125 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) | 1 |00:00:00.43 | 20228 | 0 | | | |
|* 17 | INDEX RANGE SCAN | IDX_PN_MC_TRD_AUTH_01 | 2 | 1 | | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 6398 |00:00:00.01 | 13875 | 0 | | | |
| 18 | PARTITION RANGE AND | | 1 | 1 | 33 | 25 (0)| 00:00:01 |KEY(AP)| 108 | 1 |00:00:00.01 | 4 | 1 | | | |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID | PN_M_TRD | 12 | 1 | 33 | 25 (0)| 00:00:01 |KEY(AP)| 108 | 1 |00:00:00.01 | 4 | 1 | | | |
|* 20 | INDEX RANGE SCAN | PK_PN_M_TRD | 1 | 1 | | 24 (0)| 00:00:01 |KEY(AP)| 108 | 1 |00:00:00.01 | 3 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#################### 트레이스 분석 ( 1번째 쿼리, 2017년 12월 조회 )
------------------ 테이블 ACCESS Cost 부분
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PN_MC_TRD_AUTH[T1]
Column (#2):
NewDensity:0.001689, OldDensity:0.001835 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:592
Column (#2): AUTH_DT(
AvgLen: 9 NDV: 592 Nulls: 0 Density: 0.001689
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#13): PHONE_NO(
AvgLen: 12 NDV: 15360000 Nulls: 0 Density: 0.000000
===> 테이블에 대한 카디널리티는 컬럼 통계정보의 NDV, DENSITY와 동일
===> 해당 NDV,Density로 테이블 통계 정보 ( 글로벌 )의 Row를 나누면 예상 Row수와 동일한 결과 추출됨
* Phone_no 조건은 Bind변수를 사용했기 때문에 파티션 통계정보 사용 불가, 글로벌 통계정보 사용함
-- PHONE_NO 컬럼
SELECT 206367138 * 0.0000000651041666666667 FROM DUAL; -- 13.4353605468750068789046
------------------ 인덱스 ACCESS Cost 부분
Using prorated density: 0.000728 of col #2 as selectvity of out-of-range/non-existent value pred
Using prorated density: 0.000728 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: IDX_PN_MC_TRD_AUTH_01
resc_io: 5257.00 resc_cpu: 102124537
ix_sel: 0.001455 ix_sel_with_filters: 0.000015
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 300.00
***** End Logdef Adjustment ******
Cost: 5265.49 Resp: 5265.49 Degree: 1
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 230.00 resc_cpu: 1667959
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 230.07 Resp: 230.07 Degree: 1
===> IDX_PN_MC_TRD_AUTH_01 인덱스에 대한 코스트는 "out-of-range"로 인하여 "prorated density: 0.000728" 를 사용하였음
===> COST가 낮은 IDX_PN_MC_TRD_AUTH_02를 최종 선택
#################### 트레이스 분석 ( 2번째 쿼리, 2018년 1월 조회 )
------------------ 테이블 ACCESS Cost 부분
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PN_MC_TRD_AUTH[T1]
Column (#2):
NewDensity:0.001689, OldDensity:0.001835 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:592
Column (#2): AUTH_DT(
AvgLen: 9 NDV: 592 Nulls: 0 Density: 0.001689
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Column (#13): PHONE_NO(
AvgLen: 12 NDV: 15360000 Nulls: 0 Density: 0.000000
===> 2017년 확인한 트레이스와 동일한 NDV, DENSITY를 보임
------------------ 인덱스 ACCESS Cost 부분
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred
Using prorated density: 0.000000 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (RangeScan)
Index: IDX_PN_MC_TRD_AUTH_01
resc_io: 6.00 resc_cpu: 66043
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 300.00
***** End Logdef Adjustment ******
Cost: 6.00 Resp: 6.00 Degree: 1
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 230.00 resc_cpu: 1667959
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 230.07 Resp: 230.07 Degree: 1
===> IDX_PN_MC_TRD_AUTH_01 인덱스에 대한 코스트는 "out-of-range"로 인하여 "prorated density: 0.000000" 를 사용하였음
===> COST가 가장 낮은 IDX_PN_MC_TRD_AUTH_01를 선택
#################### 트레이스 분석 ( 2016년 1월 11 - 12 조회, 2015년도 동일 )
------------------ 인덱스 ACCESS Cost 부분
Access Path: index (RangeScan)
Index: IDX_PN_MC_TRD_AUTH_01
resc_io: 12198.00 resc_cpu: 236997274
ix_sel: 0.003378 ix_sel_with_filters: 0.000034
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 300.00
***** End Logdef Adjustment ******
Cost: 12217.70 Resp: 12217.70 Degree: 1
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 230.00 resc_cpu: 1667959
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 230.07 Resp: 230.07 Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 217.00 resc_cpu: 1548152
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 217.07 Resp: 217.07 Degree: 0
===> out-of-range 발생안하며 높은 Cost를 보임
#################### 트레이스 분석 ( Between으로 조건 변경 시 )
------------------ 인덱스 ACCESS Cost 부분
Access Path: index (RangeScan)
Index: IDX_PN_MC_TRD_AUTH_01
resc_io: 16803.00 resc_cpu: 275433356
ix_sel: 0.078966 ix_sel_with_filters: 0.000790
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 300.00
***** End Logdef Adjustment ******
Cost: 16815.65 Resp: 16815.65 Degree: 1
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 6.00 resc_cpu: 41262
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 6.00 Resp: 6.00 Degree: 1
Access Path: index (skip-scan)
SS scan sel: 0.078966 SS filter sel: 0.078966 ANDV (#skips): 10101760.000000
SS io: 130695.000000 vs. table scan io: 181628.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: PK_PN_MC_TRD_AUTH
resc_io: 949876.00 resc_cpu: 5934019873
ix_sel: 0.078966 ix_sel_with_filters: 0.078966
Cost: 950138.07 Resp: 950138.07 Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (AllEqRange)
Index: IDX_PN_MC_TRD_AUTH_02
resc_io: 3.00 resc_cpu: 21964
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 3.00 Resp: 3.00 Degree: 0
===> 마찬가지로 out-of-range가 발생하지 않는다. 파티션에서 Between 사용시 Histogram을 사용하지 않음을 추측할 수 있음
#################### out-of-range에 의한 prorated density 비율 확인
------------------ 날짜별 prorated density 확인 ( Histogram은 1/13일까지 존재 )
2017 12/3 - 12/4 : 0.000729
2017 11/3 - 11/4 : 0.000738
2017 10/3 - 10/4 : 0.000746
2017 9/3 - 9/4 : 0.000755
2017 1/3 - 1/4 : 0.000824
2016 12/3 - 12/4 : 0.001595
2016 11/3 - 11/4 : 0.001603
2016 2/3 - 2/4 : 0.001681
2016 1/15 - 1/15 : 0.001689
------------------ TRD 테이블 prorated density 확인
* PN_MC_TRD의 TRD_DT = Histogram 없음
* PN_M_TRD의 TRD_DT = 5/24일이 최고 Value --> 트레이스에서 보이는 out-of-range가 0.000251임
2018년 1/7 : 0.000251
2016년 1/11 : 0.000419
2015년 12/3 : 0.000494
---> out-of-range의 비율이 차이나는 이유
= PN_MC_TRD_AUTH의 AUTH_DT : MIN Value가 20140611
= PN_M_TRD의 TRD_DT : MIN Value가 20091203
#################### Histogram 수집된 컬럼 확인
파일 따로 첨부
SELECT OWNER, TABLE_NAME, COLUMN_NAME
,DATA_TYPE, NUM_DISTINCT, LOW_VALUE
,HIGH_VALUE, DENSITY, NUM_BUCKETS
,LAST_ANALYZED, SAMPLE_SIZE, HISTOGRAM
FROM DBA_TAB_COLUMNS
WHERE OWNER NOT IN ('SYS','SYSTEM','WMSYS','SPLEX','DBSNMP')
AND TABLE_NAME IN (
SELECT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE OWNER NOT IN ('SYS','SYSTEM','WMSYS','SPLEX','DBSNMP')
AND STATTYPE_LOCKED IS NOT NULL
)
AND HISTOGRAM <>'NONE'
ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;
'Oracle > 통계정보' 카테고리의 다른 글
| dbms_stats.lock_table_stats (0) | 2022.01.24 |
|---|---|
| 통계정보 (0) | 2022.01.24 |