[20230512]最佳化的困惑19.txt
[20230512]最佳化的困惑19.txt
--//在最佳化生產系統一條sql語句遇到的情況.
1.環境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.測試例子建立:
$ cat ee1.txt
@cs lis
@sl all
select TEST_DATE from lis_test where pat_id= '1111' or identity_id= '2222';
@cs sys
$ cat ee2.txt
@cs lis
@sl all
select TEST_DATE from lis_test where pat_id= '1111' or pat_name= '2222';
@cs sys
--//不同之處在於前者identity_id= '2222'後者pat_name= '2222'.
3.問題提出:
SYS@192.168.100.235:1521/orcl> @ ee1.txt
alter session set current_schema=lis
Session altered.
alter session set statistics_level = all;
Session altered.
no rows selected
alter session set current_schema=sys
Session altered.
SYS@192.168.100.235:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ggqfz9201njzj, child number 0
-------------------------------------
select TEST_DATE from lis_test where pat_id= '1111' or identity_id=
'2222'
Plan hash value: 958283288
-------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|
--------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | | 8 (100)| | 0|00:00:00.01| 7|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 1| 53 | 8 (13)|00:00:01| 0|00:00:00.01| 7|
| 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 0|00:00:00.01| 7|
| 3| BITMAP OR | | 1| | | | | 0|00:00:00.01| 7|
| 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3|
|*5| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3|
| 6| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 4|
| 7| SORT ORDER BY | | 1| | | | | 0|00:00:00.01| 4|
|*8| INDEX RANGE SCAN |IX_LIS_TEST_IDENTITY_ID| 1| | | 4 (0)|00:00:01| 0|00:00:00.01| 4|
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LIS_TEST@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PAT_ID"=U'1111')
8 - access("IDENTITY_ID"=U'2222')
filter("IDENTITY_ID"=U'2222')
--//執行計劃沒有怎麼特殊的地方,但是細節很重要,不理解為什麼id=7出現一次SORT ORDER BY呢?
SYS@192.168.100.235:1521/orcl> @ ee2.txt
alter session set current_schema=lis
Session altered.
alter session set statistics_level = all;
Session altered.
TEST_DATE
-------------------
2022-03-25 00:00:00
--//有點意外竟然生產系統資料庫存在pat_name= '2222'的情況.
alter session set current_schema=sys
Session altered.
SYS@192.168.100.235:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 613tuxrh1j1xz, child number 0
-------------------------------------
select TEST_DATE from lis_test where pat_id= '1111' or pat_name= '2222'
Plan hash value: 408580782
-----------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|
-----------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | | 6 (100)| | 1|00:00:00.01| 7|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 1| 33 | 6 (0)|00:00:01| 1|00:00:00.01| 7|
| 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 1|00:00:00.01| 6|
| 3| BITMAP OR | | 1| | | | | 1|00:00:00.01| 6|
| 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 1|00:00:00.01| 3|
|*5| INDEX RANGE SCAN |IX_LIS_TEST_PAT_NAME| 1| | | 3 (0)|00:00:01| 1|00:00:00.01| 3|
| 6| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3|
|*7| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3|
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LIS_TEST@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PAT_NAME"=U'2222')
7 - access("PAT_ID"=U'1111')
--//這個執行計劃就沒有sort order by操作.為什麼呢?
--//檢視索引的一些細節.
SYS@192.168.100.235:1521/orcl> @ ind2 lis.lis_test
Display indexes where table or index name matches lis.lis_test...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
----------- ----------- ------------------------------ ---- ------------------------------ ----
LIS LIS_TEST IX_LIS_TEST_BARCODE 1 BARCODE
IX_LIS_TEST_IDENTITY_ID 1 IDENTITY_ID
2 SYS_NC00142$
IX_LIS_TEST_ORDER_TIME 1 ORDER_TIME
IX_LIS_TEST_PAT_BARCODE 1 PAT_BARCODE
IX_LIS_TEST_PAT_ID 1 PAT_ID
IX_LIS_TEST_PAT_NAME 1 PAT_NAME
IX_LIS_TEST_PHONE_NO 1 PHONE_NO
I_LIS_TEST_AUDIT_TIME 1 AUDIT_TIME
I_LIS_TEST_ORIGINAL_BARCODE 1 ORIGINAL_BARCODE
I_LIS_TEST_TEST_DATE_INST_ID_X 1 TEST_DATE
2 INST_ID
3 TEST_NO
PK_LIS_TEST 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
----------- ----------- ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
LIS LIS_TEST IX_LIS_TEST_BARCODE NORMAL NO VALID NO N 3 60211 9530368 9859167 9652034 2022-11-01 09:17:08 1 VISIBLE
LIS_TEST IX_LIS_TEST_IDENTITY_ID FBI NORMAL NO VALID NO N 4 71817 852492 9859185 7530972 2022-11-01 09:18:05 1 VISIBLE
LIS_TEST IX_LIS_TEST_ORDER_TIME NORMAL NO VALID NO N 3 27784 3517952 9859083 9577769 2022-11-01 09:14:49 1 VISIBLE
LIS_TEST IX_LIS_TEST_PAT_BARCODE NORMAL NO VALID NO N 3 51019 1774080 9551801 9381020 2022-11-01 09:15:41 1 VISIBLE
LIS_TEST IX_LIS_TEST_PAT_ID NORMAL NO VALID NO N 3 50518 1586560 9552995 9382441 2022-11-01 09:16:16 1 VISIBLE
LIS_TEST IX_LIS_TEST_PAT_NAME NORMAL NO VALID NO N 3 32214 745792 9780497 9616062 2022-11-01 09:17:29 1 VISIBLE
LIS_TEST IX_LIS_TEST_PHONE_NO NORMAL NO VALID NO N 3 2070 45832 333127 320998 2022-11-01 09:15:11 1 VISIBLE
LIS_TEST I_LIS_TEST_AUDIT_TIME NORMAL NO VALID NO N 3 25473 6009344 9584155 8979755 2022-11-01 09:14:56 1 VISIBLE
LIS_TEST I_LIS_TEST_ORIGINAL_BARCODE NORMAL NO VALID NO N 1 0 0 0 0 2022-11-01 09:14:41 1 VISIBLE
LIS_TEST I_LIS_TEST_TEST_DATE_INST_ID_X NORMAL NO VALID NO N 3 21084 9708295 9859083 8940127 2022-11-01 09:14:41 1 VISIBLE
LIS_TEST PK_LIS_TEST NORMAL YES VALID NO N 3 19981 9859155 9859155 9026718 2022-11-01 09:16:34 1 VISIBLE
--//IX_LIS_TEST_IDENTITY_ID 是一個函式索引.
SYS@192.168.100.235:1521/orcl> @ ddl lis.IX_LIS_TEST_IDENTITY_ID
C300
-------------------------------------------------------------------------------------
CREATE INDEX "LIS"."IX_LIS_TEST_IDENTITY_ID" ON "LIS"."LIS_TEST" ("IDENTITY_ID", 0)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "LIS_MAX_DATA" ;
--//可以看出開發為了使用索引查詢IDENTITY_ID是null的情況在加入常量欄位0,這樣保證IDENTITY_ID=null也在索引中.實際上的情況是
--//畫蛇添足!!,根本沒有.因為null值太多了.
SYS@192.168.100.235:1521/orcl> @ desczz lis.lis_test IDENTITY_ID,pat_id,pat_name
eXtended describe of lis.lis_test
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ----- -------------- ------------ -------------- ---------- --------- ----------- ----------- --------------------------
LIS LIS_TEST 8486 2022-12-12 10:03:00 9 PAT_ID NVARCHAR2(36) 1586560 .00000000100 306100 1 M10019 質控樣本7
8675 2022-12-12 10:03:00 12 PAT_NAME NVARCHAR2(100) 745792 .00000000100 78681 1 AAAAA ｖｃ＋
6290 2022-12-12 10:03:00 120 IDENTITY_ID NVARCHAR2(36) 848256 .00000000100 2745499 1 港澳臺通行證號：HYYYYYYYY
--//IDENTITY_ID is null 的值很多.
--//搞不懂為什麼生產庫會出現pat_id='質控樣本7',pat_name='ｖｃ＋'的情況.
--//我自己本身還是無法理解為什麼這樣的方式會出現一個sort order by的情況.
$ cat ee3.txt
@cs lis
@sl all
select TEST_DATE,PAT_NAME from lis_test where pat_id= '1111' or TEST_DATE='2022-10-13 10:00:00';
@cs sys
--//執行如上ee3.txt,再次出現1次sort,使用如下查詢執行計劃.(第2引數加入projection)
SYS@192.168.100.235:1521/orcl> @ dpc '' projection
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gjaykccnjuupu, child number 0
-------------------------------------
select TEST_DATE,PAT_NAME from lis_test where pat_id= '1111' or
TEST_DATE='2022-10-13 10:00:00'
Plan hash value: 124482500
---------------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|
---------------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | |5170 (100)| | 0|00:00:00.01| 6|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1| 15271| 492K|5170 (1)|00:00:01| 0|00:00:00.01| 6|
| 2| BITMAP CONVERSION TO ROWIDS | | 1| | | | | 0|00:00:00.01| 6|
| 3| BITMAP OR | | 1| | | | | 0|00:00:00.01| 6|
| 4| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3|
| 5| SORT ORDER BY | | 1| | | | | 0|00:00:00.01| 3|
|*6| INDEX RANGE SCAN |I_LIS_TEST_TEST_DATE_INST_ID_X| 1| | | 35 (0)|00:00:01| 0|00:00:00.01| 3|
| 7| BITMAP CONVERSION FROM ROWIDS | | 1| | | | | 0|00:00:00.01| 3|
|*8| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 1| | | 3 (0)|00:00:01| 0|00:00:00.01| 3|
---------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LIS_TEST@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TEST_DATE"=TO_DATE(' 2022-10-13 10:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("TEST_DATE"=TO_DATE(' 2022-10-13 10:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("PAT_ID"=U'1111')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST_DATE"[DATE,7], "PAT_NAME"[NVARCHAR2,100]
2 - "LIS_TEST".ROWID[ROWID,10]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
5 - (#keys=1) "LIS_TEST".ROWID[ROWID,10]
6 - "LIS_TEST".ROWID[ROWID,10]
7 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
8 - "LIS_TEST".ROWID[ROWID,10]
45 rows selected.
--//仔細看Column Projection Information (identified by operation id):部分就很容易明白了為什麼出現sort order by操作,id=6
--//僅僅取出rowid.對於I_LIS_TEST_TEST_DATE_INST_ID_X索引來講,索引的儲存順序是TEST_DATE,INST_ID,TEST_NO,ID,rowid.
--//按照INDEX RANGE SCAN取出rowid的順序可能並沒有排序,因為id=4操作BITMAP CONVERSION FROM ROWIDS,需要rowid是排序的,這樣增加一步排序
--//rowid的操作,就出現上述的情況.
--//使用IX_LIS_TEST_IDENTITY_ID索引的情況類似,索引的儲存順序順是IDENTITY_ID,0,rowid,oracle並沒有智慧,知道INDEX RANGE
--//SCAN取出rowid已經排序的,出現一個sort order by排序操作就很正常了.
--//實際上先入為主的觀念是以為IX_LIS_TEST_IDENTITY_ID僅僅包含1個IDENTITY_ID欄位,看到是函式索引時,第2個值為0,自己也沒有轉過這個彎,
--//實際上僅僅需要理解BITMAP CONVERSION FROM ROWIDS之前的rowid要排序的這步操作,上述情況很容易理解.
--//順便建立索引IDENTITY_ID索引.
CREATE INDEX LIS.I_LIS_TEST_IDENTITY_ID ON LIS.LIS_TEST (IDENTITY_ID) LOGGING NOPARALLEL ONLINE;
ALTER INDEX LIS.IX_LIS_TEST_IDENTITY_ID INVISIBLE;
--//觀察一段來決定是否刪除LIS.IX_LIS_TEST_IDENTITY_ID索引.
SYS@192.168.100.235:1521/orcl> @ ee1.txt
alter session set current_schema=lis
Session altered.
alter session set statistics_level = all;
Session altered.
no rows selected
alter session set current_schema=sys
Session altered.
Plan hash value: 2203104331
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 1 | 53 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 7 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 7 |
| 3 | BITMAP OR | | 1 | | | | | 0 |00:00:00.01 | 7 |
| 4 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | IX_LIS_TEST_PAT_ID | 1 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 6 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | | 0 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | I_LIS_TEST_IDENTITY_ID | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------------------------------------------
--//使用新的I_LIS_TEST_IDENTITY_ID索引,這樣的情況就不會出現sort order by了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2952012/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20230511]最佳化的困惑17.txt
- [20200422]最佳化的困惑9.txt
- strtotime 的困惑
- 困惑度的計算
- 學習java的困惑Java
- 令人困惑的 TensorFlow！(II)
- 個人部落格的困惑
- [20210203]max優化的困惑.txt優化
- [20220507]優化的困惑13.txt優化
- [20220428]優化的困惑12.txt優化
- [20200408]優化的困惑6.txt優化
- [20200808]優化的困惑10.txt優化
- [20200401]優化的困惑5.txt優化
- 【原創】多專案控制的困惑
- 優必選的商業化困惑
- [20200320]SQL語句優化的困惑.txtSQL優化
- 關於非同步爬蟲排序的困惑非同步爬蟲排序
- 如何消除ERP選型困惑
- [20201224]sql優化困惑.txtSQL優化
- 設計模式系列 · 無從下手的困惑 (一)設計模式
- 測試面試困惑求解答面試
- [20240313]使用tpt ashtop.sql指令碼的困惑.txtSQL指令碼
- [20200324]SQL語句優化的困惑2.txtSQL優化
- C++運算子過載的一些困惑C++
- 10分鐘搞定讓你困惑的 Jenkins 環境變數Jenkins變數
- android觸控事件分發機制，曾困惑你我的地方Android事件
- 技術轉管理，你遇到了哪些困惑？
- 【譯】關於Webpack中一些讓人困惑的地方的解答Web
- 馮老師的困惑 —— 測試和正式環境掐架篇（二）
- 4月3號下午一條通告帶來的技術困惑
- [原創]成立售後服務部門的一些困惑
- 機器學習中那些必要又困惑的數學知識機器學習
- 負對數似然（NLL）和困惑度（PPL）
- 六西格瑪企業：從困惑到成功的11個步驟！
- 技術管理者的困惑——技術與管理應該如何平衡？
- 「Adobe國際認證」讓我困惑的顏色：什麼是 Pantone？
- 架構師修煉之道（一）技術高手的困惑與發展架構
- Google Chrome 瀏覽器開始阻止令人困惑的 URL 網址GoChrome瀏覽器