[20230308]12c以上版本模糊查询问题.txt

[20230308]12c以上版本模糊查询问题.txt --//前几天看了链接http://www.itpub.net/thread-2148700-1-1.html，对方提到模糊查询慢的问题，实际上这个问题使用常规模式基本 --//无解,仅仅使用全文本检索，当然对方解析也很慢我就不知道为什么了。 --//不过我突然想起我去年看https://jonathanlewis.wordpress.com/2022/07/15/index-wildcard/的链接提到的情况，当时因为其他事 --//情，仅仅看了帖子，但是自己忘了自己测试一下。 --//简单说明实际应用设置cursor_sharing = force，如果查询使用column_name like '%XYZ%'之类查询时，12c以上版本逻辑读很很高 --//的情况。 1.环境： TTT@192.168.2.7:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立测试例子: create table t1 as select  *  from all_objects ; --//注：原始链接将结果插入5次，执行1次一样可以测试出来作者遇到的问题。 create index t1_id on t1(object_name); --//分析略。 alter session set cursor_sharing = force; alter session set statistics_level = all; alter session set events '10053 trace name context forever'; TTT@192.168.2.7:1521/orcl> select count(*) from t1;   COUNT(*) ----------      69688 3.测试: TTT@192.168.2.7:1521/orcl> select  /*+ index(t1(object_name)) cursor_sharing_exact */ t1.* from t1 t1 where object_name like '%XYZ%'; no rows selected      --//执行计划如下: TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dwduhqcm4r08c, child number 0 ------------------------------------- select  /*+ index(t1(object_name)) cursor_sharing_exact */ t1.* from t1 t1 where object_name like '%XYZ%' Plan hash value: 2798063786 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |      1 |        |       |  2204 (100)|          |      0 |00:00:00.02 |     454 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   3484 |   452K|  2204   (1)| 00:00:01 |      0 |00:00:00.02 |     454 | |*  2 |   INDEX FULL SCAN                   | T1_ID |      1 |   3484 |       |   455   (1)| 00:00:01 |      0 |00:00:00.02 |     454 | --------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_NAME" LIKE '%XYZ%') --//3484/69688 = .04999,基本可以推断按照5%估算. --//设置cursor_sharing_exact，也就是cursor_sharing = force不起作用，里面的常量'%XYZ%'不会转义为:SYS_B_0变量。 TTT@192.168.2.7:1521/orcl> select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like '%XYZ%'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  azfnvmrr42y1k, child number 0 ------------------------------------- select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like :"SYS_B_0" Plan hash value: 539998951 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |      1 |        |       |  1774 (100)|          |      0 |00:00:00.11 |   35419 | |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   3484 |   452K|  1774   (0)| 00:00:01 |      0 |00:00:00.11 |   35419 | |*  2 |   INDEX RANGE SCAN                  | T1_ID |      1 |   3484 |       |    25   (0)| 00:00:01 |  69688 |00:00:00.02 |     454 | --------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :1 (VARCHAR2(30), CSID=1): '%XYZ%' Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_NAME" LIKE :SYS_B_0)    2 - access("OBJECT_NAME" LIKE :SYS_B_0) 32 rows selected. --//执行计划选择INDEX RANGE SCAN,理论我的测试要扫描全部索引,buffers=454可以很前面的INDEX FULL SCAN对上,但是ID=1的 --//Buffers=35419,比前面的测试高许多.我开始也犯浑,所以留下很深的印象,看作者讲解才发现filer发生在id=1上,也就是表上. --//这样逻辑读很高就很正常了,如果filter发生在id=2就没有这个高的逻辑读了. TTT@192.168.2.7:1521/orcl> select * from v$system_fix_control where SQL_FEATURE like '%ACCESS_PATH%' and DESCRIPTION like '%LIKE%';    BUGNO VALUE SQL_FEATURE                    DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID -------- ----- ------------------------------ ---------------------------------------------------------------- ------------------------ ----- ---------- ------  3628118     1 QKSFM_ACCESS_PATH_3628118      Do not consider LIKE with leading wildcard as index key          10.2.0.1                     0          1      3  9011016     1 QKSFM_ACCESS_PATH_9011016      use 1/NDV+1/NROWS for col1 LIKE col2 selectivities (index driver 11.2.0.2                     0          1      3  9303766     1 QKSFM_ACCESS_PATH_9303766      use 1/NDV+1/NROWS for col1 LIKE col2 selectivities (table access 11.2.0.2                     0          1      3 20289688     1 QKSFM_ACCESS_PATH_20289688     check for leading wildcard in LIKE with bind                     12.2.0.1                     0          1      3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//DESCRIPTION的信息竟然显示不全VARCHAR2(64)!! --//利用参数提示opt_param('_fix_control' '20289688:0') TTT@192.168.2.7:1521/orcl> select  /*+ index(t1(object_name)) opt_param('_fix_control' '20289688:0') */ t1.* from t1 t1 where object_name like '%XYZ%'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dss7wrqs7zuv9, child number 0 ------------------------------------- select  /*+ index(t1(object_name)) opt_param('_fix_control' '20289688:0') */ t1.* from t1 t1 where object_name like :"SYS_B_0" Plan hash value: 539998951 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |      1 |        |       |  1774 (100)|          |      0 |00:00:00.03 |     454 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   3484 |   452K|  1774   (0)| 00:00:01 |      0 |00:00:00.03 |     454 | |*  2 |   INDEX RANGE SCAN                  | T1_ID |      1 |   3484 |       |    25   (0)| 00:00:01 |      0 |00:00:00.03 |     454 | --------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :1 (VARCHAR2(30), CSID=1): '%XYZ%' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_NAME" LIKE :SYS_B_0)        filter("OBJECT_NAME" LIKE :SYS_B_0) 32 rows selected. --//OK!! --//采用opt_param('_optim_peek_user_binds' 'false')也可以解决问题. TTT@192.168.2.7:1521/orcl> select  /*+ index(t1(object_name)) opt_param('_optim_peek_user_binds' 'false') */ t1.* from t1 t1 where object_name like '%XYZ%'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dh5u4yug04fzp, child number 0 ------------------------------------- select  /*+ index(t1(object_name)) opt_param('_optim_peek_user_binds' 'false') */ t1.* from t1 t1 where object_name like :"SYS_B_0" Plan hash value: 539998951 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |      1 |        |       |   322 (100)|          |      0 |00:00:00.03 |     454 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   3484 |   452K|   322   (0)| 00:00:01 |      0 |00:00:00.03 |     454 | |*  2 |   INDEX RANGE SCAN                  | T1_ID |      1 |    627 |       |     7   (0)| 00:00:01 |      0 |00:00:00.03 |     454 | --------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_NAME" LIKE :SYS_B_0)        filter("OBJECT_NAME" LIKE :SYS_B_0) 27 rows selected. 3.如果在生产系统遇到设置cursor_sharing = force的情况,目前的oracle版本遇到这类问题该如何解决呢? --//还可以执行如下: SCOTT@test01p> ALTER SYSTEM SET "_fix_control" = '20289688:0'; System altered. TTT@192.168.2.7:1521/orcl> ALTER SYSTEM SET "_fix_control" = '20289688:0'; System altered. TTT@192.168.2.7:1521/orcl> select * from v$system_fix_control where bugno=20289688;    BUGNO VALUE SQL_FEATURE                DESCRIPTION                                  OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT CON_ID -------- ----- -------------------------- -------------------------------------------- ------------------------ ----- ---------- ------ 20289688     0 QKSFM_ACCESS_PATH_20289688 check for leading wildcard in LIKE with bind 12.2.0.1                     0          0      3 TTT@192.168.2.7:1521/orcl> Select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like '%XYZ%'; no rows selected TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dytp4h89b4p3x, child number 0 ------------------------------------- Select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like :"SYS_B_0" Plan hash value: 539998951 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |       |      1 |        |       |  1774 (100)|          |      0 |00:00:00.02 |     454 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |   3484 |   452K|  1774   (0)| 00:00:01 |      0 |00:00:00.02 |     454 | |*  2 |   INDEX RANGE SCAN                  | T1_ID |      1 |   3484 |       |    25   (0)| 00:00:01 |      0 |00:00:00.02 |     454 | --------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T1@SEL$1    2 - SEL$1 / T1@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - :1 (VARCHAR2(30), CSID=1): '%XYZ%' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_NAME" LIKE :SYS_B_0)        filter("OBJECT_NAME" LIKE :SYS_B_0) 32 rows selected. --//还原: TTT@192.168.2.7:1521/orcl> ALTER SYSTEM SET "_fix_control" = '20289688:1'; System altered. TTT@192.168.2.7:1521/orcl> ALTER SYSTEM RESET "_fix_control"; System altered. --//如果19c版本还支持使用dbms_optim_bundle包.当前版本不支持. --//https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/dbms_optim_bundle.html#GUID-D9DBDC73-38A2-428A-BC27-7CA8EDC67E8A 118.2.4 SET_FIX_CONTROLS Procedure The DBMS_OPTIM_BUNDLE subprogram, SET_FIX_CONTROLS procedure enables or disables a list of fixes with _fix_controls. The fixes can be present in a base version, in a release update, or in a one-off release. This procedure appends the new fix control settings to the existing ones. Syntax DBMS_OPTIM_BUNDLE.SET_FIX_CONTROLS (    fix_control_string               IN  VARCHAR2,    sid                              IN  VARCHAR2  DEFAULT '*',    scope                            IN  VARCHAR2  DEFAULT 'MEMORY',    current_setting_precedence       IN  VARCHAR2  DEFAULT 'YES'); --//exec dbms_optim_bundle.set_fix_controls('20289688:0','*', 'BOTH', 'NO'); --//关于模糊查询我个人建议还是尽量减少前面使用%的情况.