[20230308]12c以上版本模糊查詢問題.txt
[20230308]12c以上版本模糊查詢問題.txt
--//前幾天看了連結http://www.itpub.net/thread-2148700-1-1.html，對方提到模糊查詢慢的問題，實際上這個問題使用常規模式基本
--//無解,僅僅使用全文字檢索，當然對方解析也很慢我就不知道為什麼了。
--//不過我突然想起我去年看的連結提到的情況，當時因為其他事
--//情，僅僅看了帖子，但是自己忘了自己測試一下。
--//簡單說明實際應用設定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包.當前版本不支援.
--//
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');
--//關於模糊查詢我個人建議還是儘量減少前面使用%的情況.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2939215/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql 模糊查詢問題SQL
- 【最佳化】模糊查詢索引問題索引
- [20190524]淺談模糊查詢.txt
- mysql 模糊查詢MySql
- [20190107]12c以上版本配置dg注意.txt
- oracle 精確查詢和模糊查詢Oracle
- pgsql查詢優化之模糊查詢SQL優化
- elasticsearch的模糊查詢Elasticsearch
- MyBatis模糊查詢LIKEMyBatis
- sql日期模糊查詢SQL
- mybatis - [07] 模糊查詢MyBatis
- [20210421]12c以上版本增加欄位與預設值.txt
- Java ——MongDB 插入資料、 模糊查詢、in查詢Java
- MySQL 5.1.6以上版本動態開啟慢查詢日誌MySql
- mybatis做like模糊查詢MyBatis
- Mybatis各種模糊查詢MyBatis
- 反向索引與模糊查詢索引
- [20210418]查詢v$檢視問題.txt
- MySQL 5.1.6以上版本動態開啟慢查詢日誌薦MySql
- 關於Room資料庫，拼寫模糊查詢語句遇到的問題OOM資料庫
- int 被當作模糊查詢
- LINQ中的模糊查詢
- Mysql高效的模糊查詢(轉)MySql
- [20150727]使用標量子查詢小問題.txt
- Xilinx問題查詢
- 斷號查詢問題
- oracle 12C以上 版本資料庫訪問 ORA-28040 ORA-03134Oracle資料庫
- 報表怎麼做模糊查詢
- 【PostgreSQL】 字首模糊查詢級優化SQL優化
- mybatis-模糊查詢like CONCATMyBatis
- Mybatis中Oracle的拼接模糊查詢MyBatisOracle
- Mybatis模糊查詢結果為空MyBatis
- mybatis 對特殊字元的模糊查詢MyBatis字元
- PostgreSQL DBA(192) - 整行模糊查詢SQL
- 模糊查詢區分大小寫嗎？
- Peewee Sqlite3 中文模糊查詢SQLite
- Oracle特殊符號的模糊查詢Oracle符號
- 儲存過程模糊查詢(like)儲存過程