SQL Monitoring Report
SQL Text
------------------------------
SELECT TACTIC_DET_ID,
CALC_ID,
ORG_NO,
CONS_ID,
CONS_NO,
ESTI_PQ,
ESTI_AMT,
YM,
YMD,
COLL_TIME,
ESTI_DATE,
ACT_AMT,
BASE_COMP_TIME,
BASE_TACTIC_NO,
OVERDRAFT_VALUE,
CONS_STATUS,
DIRECTIVE_ID,
EXECUTE_TYPE,
EXECUTE_SCHEME,
EXECUTE_DATE,
EXECUTE_STATUS,
TRANSIT_HANDLE_ID,
TRANSIT_STEP,
MR_SECT_NO,
REMARK,
CP_NO,
CP_STATUS,
CP_PRIO,
CHANNEL_TYPE
FROM A_RCA_TACTIC_DET A
WHERE A.CONS_NO = :B5
AND A.ORG_NO LIKE :B4 || '%'
AND A.YM BETWEEN SUBSTR(:B2, 1, 6) AND :B3
AND A.YMD >= :B2
AND A.YMD <= :B1
ORDER BY A.ESTI_DATE DESC
Global Information
------------------------------
Status : EXECUTING
Instance ID : 2
Session : xxx (1709:38751)
SQL ID : cu1ktstbq4axt
SQL Execution ID : 33555216
Execution Started : 07/03/2020 14:28:18
First Refresh Time : 07/03/2020 14:28:24
Last Refresh Time : 07/03/2020 14:30:29
Duration : 131s
Module/Action : ro.cons.service.ConsViewService.getRcaTacticInfo/TH45-@dyxepm1_1:0703142819
Service : app1
Program : JDBC Thin Client
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B5 | 1 | VARCHAR2(32) | 6xxxxxxxx7 |
| :B4 | 2 | VARCHAR2(128) | 3xxxxx0 |
| :B2 | 3 | VARCHAR2(32) | 2xxxxxxx3 |
| :B3 | 4 | VARCHAR2(32) | 2xxxx8 |
| :B2 | 5 | VARCHAR2(32) | 2xxxxxxxx3 |
| :B1 | 6 | VARCHAR2(32) | 2xxxxxxxx3 |
========================================================================================================================
Global Stats
==================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
==================================================================================
| 131 | 5.78 | 118 | 0.00 | 7.91 | 261K | 19313 | 302MB |
==================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3175721642)
===============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
===============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | |
| 1 | SORT ORDER BY | | 1 | 32 | | | 1 | | | | | |
| 2 | FILTER | | | | | | 1 | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 31 | | | 1 | | | | | |
| -> 4 | TABLE ACCESS BY LOCAL INDEX ROWID | A_RCA_TACTIC_DET | 1 | 31 | 135 | +1 | 2 | 0 | 15181 | 237MB | 70.23 | gc cr grant 2-way (4) |
| | | | | | | | | | | | | Cpu (4) |
| | | | | | | | | | | | | db file sequential read (84) |
| -> 5 | INDEX RANGE SCAN | IDX_A_RCA_TACTIC_DET_YMD | 1 | 31 | 133 | +3 | 2 | 848K | 4231 | 66MB | 29.77 | gc cr grant 2-way (3) |
| | | | | | | | | | | | | Cpu (1) |
| | | | | | | | | | | | | db file sequential read (35) |
===============================================================================================================================================================================================
e-row和a-rows差别很大，一般都是由于统计信息不准确导致。（还有可能是cost计算方式不合理）
select num_rows,blocks,last_analyzed from dba_tables where table_name='A_RCA_TACTIC_DET';
300090470 9391916 2019/11/21 4:42:00
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME='A_RCA_TACTIC_DET';
CONS_NO 11736064
YMD 1236 存在直方图信息
select BLEVEL,distinct_keys,leaf_blocks,clustering_factor from dba_indexes where index_name in('IDX_A_RCA_TACTIC_DET_YMD','LOC_A_RCA_TACTIC_DET_CONSNO')
2 11736064 2558042 295677239
2 1236 1516690 9088619
直接做个10053trace
Column (#9): YMD(
AvgLen: 9 NDV: 1236 Nulls: 687 Density: 0.000024
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 85
Using prorated density: 0.000000 of col #9 as selectvity of out-of-range/non-existent value pred
我们发现YMD的是越界的，不在列统计信息high_value,low_value之间，导致执行计划评估不正确。
（如果想知道cost的具体计算方式可以参SQL优化核心思想这本书，
cost = blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity)
且选择性计算方法是和high_value,low_value等值存在关联）。
解决方案：
重新收集统计信息。
删除YMD列上的索引。（本身列的选择性就不高）
profile绑定执行计划(具体操作：https://www.cnblogs.com/muzisanshi/p/11889727.html)
select * from table(dbms_xplan.display_awr('cu1ktstbq4axt'))
Plan hash value: 948946192
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 219 | 34 (3)| 00:00:01 | | |
| 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 219 | 33 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| A_RCA_TACTIC_DET | 1 | 219 | 33 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN | LOC_A_RCA_TACTIC_DET_CONSNO | 8 | | 31 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3175721642
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 32 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 226 | 32 (4)| 00:00:01 | | |
| 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 226 | 31 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| A_RCA_TACTIC_DET | 1 | 226 | 31 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN | IDX_A_RCA_TACTIC_DET_YMD | 1 | | 31 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------