[20201203]為什麼不使用索引.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2739307/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220331]為什麼不使用索引.txt索引
- [20200326]為什麼選擇這個索引.txt索引
- Python 的切片為什麼不會索引越界？Python索引
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- [20220422]為什麼執行不報錯.txt
- [20190910]索引分支塊中TERM使用什麼字元表示.txt索引字元
- MySQL索引為什麼使用B+樹?MySql索引
- MySQL實戰 | 為什麼要使用索引？MySql索引
- Oracle 查詢行數很少，為什麼不走索引？Oracle索引
- Elasticsearch 中為什麼選擇倒排索引而不選擇 B 樹索引Elasticsearch索引
- 為什麼索引無法使用is null和is not null索引Null
- [20201203]探究library cache mutex X 3.txtMutex
- MySQL 為什麼全文索引查中文找不結果MySql索引
- 為什麼不建議使用gotoGo
- MySQL索引那些事：什麼是索引？為什麼加索引就查得快了？MySql索引
- [20220328]查詢游標為什麼不共享指令碼.txt指令碼
- [MySQL]為什麼非聚簇索引不儲存資料位置MySql索引
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（一）Oracle索引
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（三）Oracle索引
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（二）Oracle索引
- 到底為什麼不建議使用SELECT * ？
- 到底為什麼不建議使用SELECT *？
- 為什麼不建議使用eval和with？
- [20220420]完善查詢游標為什麼不共享指令碼.txt指令碼
- MySQL中為什麼要使用索引合併(Index Merge)？MySql索引Index
- 為什麼我使用了索引，查詢還是慢？索引
- 為什麼Spring官方不推薦使用 @Autowired？Spring
- 為什麼IDEA不推薦你使用@Autowired ？Idea
- 徹底搞懂MySQL為什麼要使用B+樹索引MySql索引
- mysql為什麼加索引就能快MySql索引
- [20181015]為什麼是3秒.txt
- 【Java面試】Mysql為什麼使用B+Tree作為索引結構Java面試MySql索引
- 什麼情況下需要建立索引？ 索引的作用？為什麼能夠提高查詢速度？（索引的原理） 索引有什麼副作用嗎？索引
- MYSQL索引為什麼這麼快?瞭解索引的神奇之處MySql索引
- [20180503]檢視提示使用索引.txt索引
- Mysql：好好的索引，為什麼要下推？MySql索引
- 索引為什麼能提供查詢效能...索引
- [20210902]為什麼會使用多個共享記憶體段.txt記憶體