[20201203]为什么不使用索引.txt
--//生产系统一条sql语句出现问题：
1.环境：
> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.问题探究：
> @ dpc 485xs929brpxa ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 485xs929brpxa, child number 1
-------------------------------------
SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" ,
"MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" ,
"MS_BRDA"."SFZH" , "MS_BRDA"."BRXZ" ,
"MS_BRDA"."BRXB" , "MS_BRDA"."CSNY" ,
"MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" ,
"MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" ,
"MS_BRDA"."GMYW" , "MS_BRDA"."DWXH" ,
"MS_BRDA"."DWMC" , "MS_BRDA"."DWDH" ,
"MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" ,
"MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" ,
"MS_BRDA"."JZCS" , "MS_BRDA"."JZRQ" ,
"MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" ,
"MS_BRDA"."SFDM" , "MS_BRDA"."JGDM" ,
"MS_BRDA"."GJDM" , "MS_BRDA"."LXRM" ,
"MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" ,
"MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" ,
"MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" ,
Plan hash value: 1849663881
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 33524 (100)| | | | |
| 1 | SORT ORDER BY | | 62430 | 9693K| 19M| 33524 (1)| 00:06:43 | 48128 | 48128 |43008 (0)|
|* 2 | TABLE ACCESS FULL| MS_BRDA | 62430 | 9693K| | 31339 (1)| 00:06:17 | | | |
------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / MS_BRDA@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (DATE): 08/18/2020 00:00:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MS_BRDA"."JDSJ">=:LDT_JDSJ)
--//首先对应索引是存在的，为什么不使用呢?
--//注意下划线，一开始我以为是查询时间的问题，注意它的过滤条件仅仅一个"MS_BRDA"."JDSJ">=:LDT_JDSJ,带入参数是08/18/2020 00:00:00.
> @ tpt/sqlid 485xs929brpxa %
Show SQL text, child cursors and execution stats for SQLID 485xs929brpxa child nvl('%','%')
HASH_VALUE PLAN_HASH_VALUE CH# SQL_TEXT
---------- --------------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
2461783978 1849663881 1 SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" , "MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" , "MS_BRDA"."SFZH" ,
"MS_BRDA"."BRXZ" , "MS_BRDA"."BRXB" , "MS_BRDA"."CSNY" , "MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" ,
"MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" , "MS_BRDA"."GMYW" , "MS_BRDA"."DWXH" , "MS_BRDA"."DWMC" ,
"MS_BRDA"."DWDH" , "MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" , "MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" ,
"MS_BRDA"."JZCS" , "MS_BRDA"."JZRQ" , "MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" , "MS_BRDA"."SFDM" ,
"MS_BRDA"."JGDM" , "MS_BRDA"."GJDM" , "MS_BRDA"."LXRM" , "MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" ,
"MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" , "MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" ,
CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC CPU_SEC_EXEC ELA_SEC ELA_SEC_EXEC LIOS LIOS_EXEC PIOS SORTS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ---------- ------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------------
1 00000005FA105698 00000005A7F792D0 1849663881 91379 1 91380 182760 8590622 47.0049354 107272.287 1.17391428 107565.243 1.17712019 1095809048 11991.7821 7 91380 0
> @ bind_cap 485xs929brpxa ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "MS_BRDA"."BRID" , "MS_BRDA"."MZHM" , "MS_BRDA"."BRXM" , "MS_BRDA"."FYZH" , "MS_BRDA"."SFZH" , "MS_BRDA"."BRXZ" , "MS_BRDA"."BRXB"
, "MS_BRDA"."CSNY" , "MS_BRDA"."HYZK" , "MS_BRDA"."ZYDM" , "MS_BRDA"."MZDM" , "MS_BRDA"."XXDM" , "MS_BRDA"."GMYW" , "MS_BRDA"."DW
XH" , "MS_BRDA"."DWMC" , "MS_BRDA"."DWDH" , "MS_BRDA"."DWYB" , "MS_BRDA"."HKDZ" , "MS_BRDA"."JTDH" , "MS_BRDA"."HKYB" , "MS_BRDA".
"JZCS" , "MS_BRDA"."JZRQ" , "MS_BRDA"."CZRQ" , "MS_BRDA"."JZKH" , "MS_BRDA"."SFDM" , "MS_BRDA"."JGDM" , "MS_BRDA"."GJDM" , "MS_BRD
A"."LXRM" , "MS_BRDA"."LXGX" , "MS_BRDA"."LXDZ" , "MS_BRDA"."LXDH" , "MS_BRDA"."DBRM" , "MS_BRDA"."DBGX" , "MS_BRDA"."SBHM" , "MS_
BRDA"."YBKH" , "MS_BRDA"."ZZTX" , "MS_BRDA"."JDSJ" , "MS_BRDA"."JDR" , "MS_BRDA"."ZXBZ" , "MS_BRDA"."ZXR" , "MS_BRDA"."ZXSJ" , "MS
_BRDA"."CSD_SQS" , "MS_BRDA"."CSD_S" , "MS_BRDA"."CSD_X" , "MS_BRDA"."JGDM_SQS" , "MS_BRDA"."JGDM_S" , "MS_BRDA"."XZZ_SQS" , "MS_BRDA"."XZZ_
S" , "MS_BRDA"."XZZ_X" , "MS_BRDA"."XZZ_YB" , "MS_BRDA"."XZZ_DH" , "MS_BRDA"."HKDZ_SQS" , "MS_BRDA"."HKDZ_S" , "MS_BRDA"."HKDZ_X" ,
"MS_BRDA"."XZZ_QTDZ" , "MS_BRDA"."HKDZ_QTDZ" , "MS_BRDA"."BRSF" , "MS_BRDA"."YYRQ" , "MS_BRDA"."YYSD" , "MS_BRDA"."BRLY" , "MS_BRDA"."PYDM
" , "MS_BRDA"."FYLX" , "MS_BRDA"."XSETZ" , "MS_BRDA"."PBRY" , "MS_BRDA"."PBRDZ" , "MS_BRDA"."ZJLX" , "MS_BRDA"."ZYCS" , "MS_BRDA".
"BASID" , "MS_BRDA"."BASZYH" , "MS_BRDA"."VIP" , "MS_BRDA"."XGR" , "MS_BRDA"."XGRQ" , "MS_BRDA"."SSXDZ" , "MS_BRDA"."YXZJHM" , "MS
_BRDA"."YXZJLX" FROM "MS_BRDA" WHERE ( "MS_BRDA"."JDSJ" >= :ldt_jdsj ) ORDER BY "MS_BRDA"."JDSJ" ASC
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- -------------------------------------------------- ------------------------------
485xs929brpxa 1 YES :LDT_JDSJ 1 7 2020-12-03 11:21:59 DATE 2020/12/02 00:00:00
--//实际上查询时间范围不大，数据返回量应该不大。
> @ tab_lh XXXXXX_YYY MS_BRDA JDSJ
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
Column Null Distinct Sample Number Number
Name DATA_TYPE DATA_LENGTH able Values Density Size TRANS_LOW TRANS_HIGH Nulls Buckets LAST_ANALYZED HISTOGRAM DATA_DEFAULT
------------------------ ---------- ----------- ---- ------------ ----------- -------------- -------------------------------- -------------------------------- ---------- ------- ------------------- --------------- --------------------
JDSJ DATE 7 N 3,315,200 0.00000030 4,571,135 0100-11-26 00:00:00 2047-03-18 12:55:29 0 1 2017-02-14 11:34:04 NONE
--//注意看TRANS_HIGH=2047-03-18 12:55:29。表里面有一些垃圾数据的干扰，导致优化器认为该日期范围很大，选择了全表扫描。不过
--//我感到奇怪的是最小值也是有问题的0100-11-26 00:00:00。安装道理应该纠正回来啊。
--//一个简单例子就可以验证问题在哪里：
> explain plan for select * from MS_BRDA where JDSJ>=trunc(sysdate)+10;
> @ dp
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 3614505696
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61678 | 9576K| 31425 (1)| 00:06:18 |
|* 1 | TABLE ACCESS FULL| MS_BRDA | 61678 | 9576K| 31425 (1)| 00:06:18 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / MS_BRDA@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "MS_BRDA"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_bloom_filter_enabled' 'false')
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JDSJ">=TRUNC(SYSDATE@!)+10)
--//可以发现我查询条件TDSJ>=trunc(sysdate)+10;竟然估计返回61678。而实际的情况仅仅返回一条。
--//简单推测看看
--//(2047-2020)/(2047-100)*4571135 = 63390.16,与执行计划看到差不多。457113数值来源与统计
> @ sosiz XXXXXX_YYY MS_BRDA
**********************************
Table Level 参数 schema tablename
**********************************
Table Number Empty Average Chain Average Global User Sample
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size LAST_ANALYZED
--------------- -------------- ------------ ------------ -------- -------- -------- ------ ------ -------------- -------------------
MS_BRDA 4,571,135 115,200 0 0 0 159 YES NO 4,571,135 2017-02-14 11:34:04
> select jdsj from MS_BRDA where JDSJ>=trunc(sysdate)+10;
JDSJ
-------------------
2047-03-18 12:55:29
--//正是由于操作人员录入了错误的数据，如果某次分析表后，统计信息出现异常，而导致执行计划发生畸变，选择全表扫描，当然这里不是这样的情况。
3.解决方法：
--//敦促相关人员纠正录入数据库表中的错误。
--//重新分析表，取样小一些看看是否能规避取样问题。
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXXXX_YYY'
,TabName => 'MS_BRDA'
,Estimate_Percent => 1
,Method_Opt => 'FOR ALL COLUMNS SIZE REPEAT '
,Degree => 4
,Cascade => TRUE
,FORCE => true
,No_Invalidate => FALSE);
END;
/
> @ tab_lh XXXXXX_YYY MS_BRDA JDSJ
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
Column Null Distinct Sample Number Number
Name DATA_TYPE DATA_LENGTH able Values Density Size TRANS_LOW TRANS_HIGH Nulls Buckets LAST_ANALYZED HISTOGRAM DATA_DEFAULT
------------------------ -------------------- ----------- ---- ------------ ----------- -------------- -------------------------------- -------------------------------- ---------- ------- ------------------- --------------- --------------------
JDSJ DATE 7 N 519,595 0.00000192 57,843 0100-11-26 00:00:00 2020-12-03 11:35:34 0 1 2020-12-03 11:48:40 NONE
--//TRANS_HIGH=2020-12-03 11:35:34.
> explain plan for select * from MS_BRDA where JDSJ>=trunc(sysdate)+10;
> @ dp
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 1302555158
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1826 | 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MS_BRDA | 11 | 1826 | 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_MS_BRDA_JDSJ | 11 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / MS_BRDA@SEL$1
2 - SEL$1 / MS_BRDA@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "MS_BRDA"@"SEL$1" ("MS_BRDA"."JDSJ"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_bloom_filter_enabled' 'false')
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JDSJ">=TRUNC(SYSDATE@!)+10)
--//暂时这样解决问题。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2739307/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
