[20230308]12c以上版本模糊查詢問題.txt

来源:https://www.cnblogs.com/lfree/archive/2023/03/12/17208433.html
-Advertisement-
Play Games

公眾號:MCNU雲原生,文章首發地,歡迎微信搜索關註,更多乾貨,第一時間掌握! @ 一、PostgreSQL是什麼? PostgreSQL是一種開源的關係型資料庫管理系統,也被稱為Postgres。它最初由加拿大電腦科學家Michael Stonebraker在1986年創建,其目標是創建一個具有 ...


[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.環境:
[email protected]: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';

[email protected]:1521/orcl> select count(*) from t1;
  COUNT(*)
----------
     69688

3.測試:
[email protected]:1521/orcl> select  /*+ index(t1(object_name)) cursor_sharing_exact */ t1.* from t1 t1 where object_name like '%XYZ%';
no rows selected
    
--//執行計劃如下:
[email protected]: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變數。

[email protected]:1521/orcl> select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like '%XYZ%';
no rows selected

[email protected]: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就沒有這個高的邏輯讀了.

[email protected]: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')

[email protected]: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

[email protected]: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')也可以解決問題.
[email protected]: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

[email protected]: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.

[email protected]:1521/orcl> ALTER SYSTEM SET "_fix_control" = '20289688:0';
System altered.

[email protected]: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

[email protected]:1521/orcl> Select  /*+ index(t1(object_name))  */ t1.* from t1 t1 where object_name like '%XYZ%';
no rows selected

[email protected]: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.

--//還原:
[email protected]:1521/orcl> ALTER SYSTEM SET "_fix_control" = '20289688:1';
System altered.

[email protected]: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');
--//關於模糊查詢我個人建議還是儘量減少前面使用%的情況.


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在 WebAssembly 中使用 Rust 編寫 eBPF 程式併發布 OCI 鏡像 作者:於桐,鄭昱笙 eBPF(extended Berkeley Packet Filter)是一種高性能的內核虛擬機,可以運行在內核空間中,以收集系統和網路信息。隨著電腦技術的不斷發展,eBPF 的功能日益強 ...
  • 一般來說,有時為了方便會直接在Ubuntu終端登陸root賬戶獲取許可權 在root下打開vscode會提示無法打開。 環境:Ubuntu22.04 解決辦法: 終端輸入: vi ~/.bashrc 添加一行: alias code='/usr/share/code/code . --no-sandb ...
  • apt-get update更新時出現錯誤,提示Release文件已經過期,無論是使用kali官方源還是阿裡源、中科大源都報該錯誤。 網上查找相關資料,簽名出錯需要下載數字簽名,方案如下: wget archive.kali.org/archive-key.asc //下載簽名 apt-key ad ...
  • 痞子衡嵌入式半月刊: 第 73 期 這裡分享嵌入式領域有用有趣的項目/工具以及一些熱點新聞,農曆年分二十四節氣,希望在每個交節之日準時發佈一期。 本期刊是開源項目(GitHub: JayHeng/pzh-mcu-bi-weekly),歡迎提交 issue,投稿或推薦你知道的嵌入式那些事兒。 上期回顧 ...
  • 經常用老毛桃裝系統,也裝過很多次Ubuntu+windows雙系統,但是對系統啟動的原理卻一直沒搞清楚。 這次就遇到了棘手的問題:裝完Ubuntu之後,開機的引導選項里沒有windows boot manager,直接就進了Ubuntu。 復盤一下安裝過程 (1)我是雙固態,一個裝了win10,另一 ...
  • 本文主要介紹博主在以往開發過程中,對於不同業務所對應的 sql 寫法進行歸納總結而來。進而分享給大家。 本文所講述 sql 語法都是基於 MySql 8.0 博主github地址:http://github.com/wayn111 歡迎大家關註,點個star 一、ORDER BY FIELD() 自 ...
  • 事務 事務:是一組操作的集合,是一個不可分割的工作單位,事務會把所有操作作為一個整體一起向系統提交或撤銷操作請求,即這些操作要麼同時成功,要麼同時失敗(當操作中某一步出現異常時,前面已執行的步驟也會失效)。 感覺有點像是函數。 預設MySQL的事務是自動提交的,當執行一條DML語句,MySQL會立即 ...
  • 前言 使用redis作為緩存,必然存在redis緩存和DB數據一致性的問題:某一時刻,redis緩存數據和DB數據不一致 一 緩存更新策略 按照緩存更新的方式大致分為: 記憶體淘汰、過期刪除、主動更新 一) 記憶體淘汰 利用Redis的記憶體淘汰策略,當記憶體不足時自動進行淘汰部分數據,下次查詢時更新緩存, ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...