[20200326]為什麼選擇這個索引.txt
[20200326]為什麼選擇這個索引.txt
--//昨天例行檢查遇到執行緩慢的阿問題，首先寫了2篇說明我遇到的情況。連結如下：
http://blog.itpub.net/267265/viewspace-2682613/=>[20200326]繫結變數抓取與NULL值.txt
http://blog.itpub.net/267265/viewspace-2682615/=>[20200326]dbms_monitor跟蹤與SQL語句分析.txt
1.環境：
SYS@ZZZZ/dbcn> @ 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.分析：
--//有問題的語句如下：
SYS@ZZZZ/dbcn> @ tpt/sqlid b5nnkbvnzh7dg ''
Show SQL text, child cursors and execution stats for SQLID b5nnkbvnzh7dg child nvl('','%')
HASH_VALUE PLAN_HASH_VALUE CH# SQL_TEXT
------------ --------------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
3925351855 1911168271 0 select * from xxxxxx_yyy.emr_bl_bl01 where bllb = :"SYS_B_0" and mblb = :"SYS_B_1" and jzhm = :"SYS_B_2" and WCSJ < to_date(:"SYS_B_3", :"SYS_B_4")
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
---- ---------------- ---------------- ------------ ------------ ------------ ------------ ------------ -------------- -------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------
0 000000063A9A7368 00000001491AE400 1911168271 19451 1 19451 19450 1059 .05444730077 83028.197755 4.2685824767 83343.522597 4.2847937174 2860971145 147086.06987 70164 0 1
--//平均每次執行需要4秒。實際上有問題的語句執行要8秒。
--//檢視執行計劃如下：
Plan hash value: 1911168271
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 141 | 2 (0)| 00:00:01 | | | |
|* 2 | INDEX RANGE SCAN | I_EMR_BL_BL01_WCSJ | 1 | | 2 (0)| 00:00:01 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------
SYS@ZZZZ/dbcn> @ bind_cap b5nnkbvnzh7dg ''
C200
---------------------------------------------------------------------------------------------------------------------------------------------------
select * from xxxxxx_yyy.emr_bl_bl01 where bllb = :"SYS_B_0" and mblb = :"SYS_B_1" and jzhm = :"SYS_B_2" and WCSJ < to_date(:"SYS_B_3", :"SYS_B_4")
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------- -------- ---------- ------------------- --------------- ---------------------
b5nnkbvnzh7dg 0 YES :SYS_B_0 1 22 2020-03-25 11:45:25 NUMBER 294
YES :SYS_B_1 2 22 2020-03-25 11:45:25 NUMBER 2000201
YES :SYS_B_2 3 32 2020-03-25 11:45:25 VARCHAR2(32) 571387
YES :SYS_B_3 4 32 2020-03-25 11:45:25 VARCHAR2(32) 2020/3/13 23:34:31
YES :SYS_B_4 5 32 2020-03-25 11:45:25 VARCHAR2(32) yyyy-mm-dd hh24:mi:ss
--//很明顯選擇錯誤索引,應該選擇jzhm索引。而選擇日期欄位WCSJ這樣幾乎掃描整個索引在回表，這樣邏輯讀異常高。
--//我第1個猜測可能查詢日期有誤，比如輸入的是'0020/03/ ...'之類的日期，正好重新分析，導致oracle認為這個日期範圍的值很少
--//，導致選擇錯誤的索引。我也沒有多想重新分析表，可是問題依舊。我利用我寫的指令碼帶入以上引數，是很好的選擇jzhm索引的。
--//再次檢視抓取的繫結變數值：
SYS@ZZZZ/dbcn> @ bind_cap b5nnkbvnzh7dg ''
C200
---------------------------------------------------------------------------------------------------------------------------------------------------
select * from xxxxxx_yyy.emr_bl_bl01 where bllb = :"SYS_B_0" and mblb = :"SYS_B_1" and jzhm = :"SYS_B_2" and WCSJ < to_date(:"SYS_B_3", :"SYS_B_4")
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- --------- -------- ---------- ------------------- --------------- ----------------------
b5nnkbvnzh7dg 0 YES :SYS_B_0 1 22 2020-03-25 12:28:21 NUMBER 294
YES :SYS_B_1 2 22 2020-03-25 12:28:21 NUMBER 2000201
YES :SYS_B_2 3 32 2020-03-25 12:28:21 VARCHAR2(32) 565759
YES :SYS_B_3 4 32 2020-03-25 12:28:21 VARCHAR2(32) NULL
YES :SYS_B_4 5 32 2020-03-25 12:28:21 VARCHAR2(32) yyyy-mm-dd hh24:mi:ss
--//恩!! :SYS_B_3 ='NULL',什麼回事.我想當然認為開發:"SYS_B_3"初始值是NULL字串，實際上NULL值，參考連結裡面
--//http://blog.itpub.net/267265/viewspace-2682613/=>[20200326]繫結變數抓取與NULL值.txt
--//而且很明顯我帶入'NULL'字串，結果報錯，我自己也沒注意，但是執行計劃確實選擇好的執行計劃。
--//這樣我開始考慮跟蹤會話執行的sql語句獲取繫結變數值。
3.跟蹤會話執行的sql語句：
--//透過select * from V$OPEN_CURSOR where sql_id='b5nnkbvnzh7dg'確定會話sid。
exec dbms_monitor.session_trace_enable(session_id => 4221,serial_num => 1769 , waits => true , binds => true);
--//等40秒，執行如下：
exec dbms_monitor.session_trace_disable(session_id => 4221,serial_num => 1769);
--//結果遇到奇葩的事情，我跟蹤執行計劃變好，取消跟蹤執行計劃變壞。
--//參考連線：http://blog.itpub.net/267265/viewspace-2682615/=>[20200326]dbms_monitor跟蹤與SQL語句分析.txt
--//實際上當我跟蹤時語句開始時都要重分析，而第一次:"SYS_B_3"是非NULL的。這樣正好選擇好的執行計劃，取消跟蹤執行計劃變壞。
--//當然透過抓取繫結變數值知道帶入值確實有是NULL的情況：
BINDS #140345468516232:
Bind#0
oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=12f5168128 bln=22 avl=03 flg=09
value=294
Bind#1
oacdty=02 mxl=22(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=25d56eb28 bln=22 avl=05 flg=09
value=2000201
Bind#2
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=01 csi=852 siz=32 off=0
kxsbbbfp=5f41627f8 bln=32 avl=06 flg=09
value="565759"
Bind#3
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0300 frm=01 csi=852 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
--//abl=00,長度是0.沒有值。
Bind#4
oacdty=01 mxl=32(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0300 frm=01 csi=852 siz=32 off=0
kxsbbbfp=12ff9f3a90 bln=32 avl=21 flg=09
value="yyyy-mm-dd hh24:mi:ss"
4.我生成一個指令碼驗證這個問題：
$ cat b5nnkbvnzh7dg.sql6
variable SYS_B_0 NUMBER
variable SYS_B_1 NUMBER
variable SYS_B_2 VARCHAR2(32)
variable SYS_B_3 VARCHAR2(32)
variable SYS_B_4 VARCHAR2(32)
begin
:SYS_B_0 := 294;
:SYS_B_1 := 2000201;
:SYS_B_2 := '560128';
:SYS_B_3 := NULL;
--//:SYS_B_3 := 'NULL';
--//:SYS_B_3 := '2020-03-27 10:00:00';
:SYS_B_4 := 'yyyy-mm-dd hh24:mi:ss';
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=xxxxxx_yyy;
--//alter session set statistics_level=all;
Select * from xxxxxx_yyy.emr_bl_bl01 where bllb = :"SYS_B_0" and mblb = :"SYS_B_1" and jzhm = :"SYS_B_2" and WCSJ < to_date(:"SYS_B_3", :"SYS_B_4");
set termout on
set sqlblanklines off
@dpc '' ''
rollback;
--//每次使用不同的:SYS_B_3,都要修改sql語句，我一般習慣把select逐步換成SELECT。每次更改1個字元，主要目的是每次都要硬分析。
--//使用 :SYS_B_3 := NULL;
SYS@ZZZZ/dbcn> @ b5nnkbvnzh7dg.sql6
PL/SQL procedure successfully completed.
Plan hash value: 1911168271
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 150 | 2 (0)| 00:00:01 | | | |
|* 2 | INDEX RANGE SCAN | I_EMR_BL_BL01_WCSJ | 1 | | 2 (0)| 00:00:01 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------
--//帶入NULL使用錯誤索引。
--//使用 :SYS_B_3 := 'NULL';
Plan hash value: 3458316953
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 150 | 5 (0)| 00:00:01 | | | |
|* 2 | INDEX RANGE SCAN | I_EMR_BL_BL01_JZHM_BLLB_MBLB | 2 | | 3 (0)| 00:00:01 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------
--//帶入'NULL'字串使用索引正確。實際上如果你註解set termout off，就會發現如下錯誤。
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
--//順便再現問題：
--//首先使用 :SYS_B_3 := NULL，生成壞執行計劃，然後換成:SYS_B_3 := '2020-03-27 10:00:00';這樣問題再現：
Plan hash value: 1911168271
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 1 | 150 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 2 | INDEX RANGE SCAN | I_EMR_BL_BL01_WCSJ | 1 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1911168271
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 3 |00:01:06.62 | 8436K| 36785 | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 1 | 1 | 150 | 2 (0)| 00:00:01 | 3 |00:01:06.62 | 8436K| 36785 | | | |
|* 2 | INDEX RANGE SCAN | I_EMR_BL_BL01_WCSJ | 1 | 1 | | 2 (0)| 00:00:01 | 8795K|00:00:16.87 | 38954 | 36785 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5.看看awr報表：
--//結束時間當天的18:00之前的統計：
SYS@ZZZZ/dbcn> @ tpt/ash/ash_wait_chains username||':'||event2 "sql_id='b5nnkbvnzh7dg'" trunc(sysdate) trunc(sysdate)+1
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- ----------------------------------------------
100% 47589 .6 -> xxxxxx_yyy:ON CPU
0% 25 0 -> xxxxxx_yyy:gc current block 2-way
0% 17 0 -> xxxxxx_yyy:cell single block physical read
0% 5 0 -> xxxxxx_yyy:gc cr block 2-way
0% 1 0 -> xxxxxx_yyy:gc cr request
--//看看執行這條語句花的時間是47589/3600 = 13.22小時，這樣的團隊失望，只能說明我們伺服器超級強勁，真正的先進工作者^_^。
--//今天再統計昨天的情況，注意使用dash_wait_chains指令碼，訪問的檢視不一樣，統計誤差有點大。
SYS@ZZZZ/dbcn> @ tpt/ash/dash_wait_chains username||':'||event2 "sql_id='b5nnkbvnzh7dg'" trunc(sysdate-1) trunc(sysdate)
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- ---------------------------------------------
100% 56450 .7 -> xxxxxx_yyy:ON CPU
0% 30 0 -> xxxxxx_yyy:gc current block 2-way
0% 10 0 -> xxxxxx_yyy:cell single block physical read
--//今天的統計：
SYS@ZZZZ/dbcn> @ tpt/ash/ash_wait_chains username||':'||event2 "sql_id='b5nnkbvnzh7dg'" trunc(sysdate) trunc(sysdate)+1
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------
98% 44 0 -> xxxxxx_yyy:ON CPU
2% 1 0 -> xxxxxx_yyy:gc cr block 2-way
--//到現在11點僅僅使用45秒。
6.補充如何更正：
--//最佳的方式叫開發修改程式碼，規避帶入NULL這個錯誤。我看開發的工作效率往往遙遙無期....
--//我最後使用sql profile穩定執行計劃，這樣比較保險。我現在基本按照第三方運維的方式解決這類問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2682641/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Elasticsearch 中為什麼選擇倒排索引而不選擇 B 樹索引Elasticsearch索引
- [20220331]為什麼不使用索引.txt索引
- [20201203]為什麼不使用索引.txt索引
- 你為什麼選擇程式設計師這個職業？程式設計師
- 為什麼選擇.NETCore？NetCore
- 為什麼選擇b+樹作為儲存引擎索引結構儲存引擎索引
- [20190827]函式索引與選擇率.txt函式索引
- 為什麼選擇linux這條職業賽道Linux
- 為什麼我會選擇走 Java 這條路？Java
- 程式語言這麼多，為什麼建議選擇Python?Python
- Aembit為什麼選擇 Rust？Rust
- 為什麼選擇使用Rust？Rust
- 為什麼選擇Guice框架GUI框架
- 從 InfluxDB 到 TDengine，陽光氫能為什麼會做出這個選擇？UX
- 市面這麼多桌面管理軟體，為什麼選擇Endpoint Central？
- 市面這麼多網路管理系統，為什麼選擇OpManager？
- 為什麼選擇高防DNS？DNS
- 為什麼選擇centos系統CentOS
- 為什麼選擇Cynefin框架？ – zwischenzugs框架
- 因為你這個人，我選擇了這個公司
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- MYSQL索引為什麼這麼快?瞭解索引的神奇之處MySql索引
- [20210301]為什麼邏輯讀這麼多.txt
- 這就是為什麼企業會選擇自助分析工具的理由
- 為什麼選擇Python做爬蟲Python爬蟲
- 為什麼選擇ASP.NET CoreASP.NET
- 為什麼建議新手選擇Ubuntu?告訴你選擇理由!Ubuntu
- 技術分享 | 為什麼 SELECT 查詢選擇全表掃描，而不走索引？索引
- 行業資訊：為什麼選擇IT行業，在這裡找到答案！行業
- 你當初為什麼選擇了前端？前端
- 老闆：你為什麼要選擇 Vue？Vue
- 為什麼爬蟲要選擇住宅代理？爬蟲
- 為什麼選擇獨立伺服器伺服器
- 為什麼選擇無伺服器模型？伺服器模型
- 阿里巴巴為什麼選擇Apache Flink？阿里Apache
- 普通索引和唯一索引，應該怎麼選擇？索引
- 什麼Jupyter?為什麼初學Python推薦選擇Jupyter?Python
- 新加坡為什麼是ICO的最後選擇，同時也是最佳選擇？