[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2952012/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
