[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’);
–//关于模糊查询我个人建议还是尽量减少前面使用%的情况.