[20181220]使用提示OR_EXPAND優化.txt

来源:https://www.cnblogs.com/lfree/archive/2018/12/20/10150079.html
-Advertisement-
Play Games

[20181220]使用提示OR_EXPAND優化.txt--//鏈接http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的討論.--//ZALBB建議在18c下嘗試看看,我 ...


[20181220]使用提示OR_EXPAND優化.txt

--//鏈接http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的討論.
--//ZALBB建議在18c下嘗試看看,我們這裡僅僅1台18c,而且還是生產系統,正好前幾天在辦公機器重新安裝12c,在12c測試看看.
--//主要問題感覺oracle對於這樣的sql有點奇怪....

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t1 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;
create table t2 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000;
create index i_t1_id1 on t1(id1);
create index i_t1_id2 on t1(id2);
create index i_t2_id1 on t2(id1);

--//分析略.

2.測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select  * from t1 where t1.id1 in  (select  t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );
       ID1        ID2 NAME
---------- ---------- ----------------------------------------------------------------------------------------------------
        10         10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
        11         11 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gz5pqkg6svm7k, child number 0
-------------------------------------
select  * from t1 where t1.id1 in  (select  t2.id1 from t2 where
t2.id1=11 ) or  (t1.id2=10 )
Plan hash value: 1962644737
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |       |    30 (100)|          |      2 |00:00:00.01 |     115 |
|*  1 |  FILTER            |          |      1 |        |       |            |          |      2 |00:00:00.01 |     115 |
|   2 |   TABLE ACCESS FULL| T1       |      1 |   6000 |   638K|    30   (0)| 00:00:01 |   6000 |00:00:00.01 |     113 |
|*  3 |   FILTER           |          |   5999 |        |       |            |          |      1 |00:00:00.01 |       2 |
|*  4 |    INDEX RANGE SCAN| I_T2_ID1 |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$2
   4 - SEL$2 / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("T1"."ID2"=10 OR  IS NOT NULL))
   3 - filter(11=:B1)
   4 - access("T2"."ID1"=:B1)
32 rows selected.

--//執行計劃存在1個全表掃描.裡面的索引選擇性很好,oracle並沒有選擇合理的執行計劃.
--//而且有1個小小的細節,id=4的starts=1,而前面的id=3的starts=5999.你可以看出這裡oracle顯示執行計劃有1個小小的bug.
--//id=4的starts應該是5999.這樣看到的邏輯讀不應該是後面的2而是2*5999 = 11998.
--//而且你可以看出oracle忽略的id=4多次INDEX RANGE SCAN的成本.
--//鏈接http://www.itpub.net/thread-2107240-2-1.html裡面的顯示倒是正確的.它的版本是11.2.0.4.180717.

3.是否通過提示優化sql語句:
--//首先想到的是USE_CONCAT.
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in  (select /*+unnest */ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );

--//執行計劃如下:
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |       |    33 (100)|          |      2 |00:00:00.01 |     118 |
|   1 |  CONCATENATION                       |          |      1 |        |       |            |          |      2 |00:00:00.01 |     118 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                  | I_T1_ID2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  4 |   FILTER                             |          |      1 |        |       |            |          |      1 |00:00:00.01 |     114 |
|*  5 |    TABLE ACCESS FULL                 | T1       |      1 |   5999 |   638K|    30   (0)| 00:00:01 |   5999 |00:00:00.01 |     112 |
|*  6 |    FILTER                            |          |   5999 |        |       |            |          |      1 |00:00:00.01 |       2 |
|*  7 |     INDEX RANGE SCAN                 | I_T2_ID1 |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / T1@SEL$1
   3 - SEL$1_1 / T1@SEL$1
   5 - SEL$1_2 / T1@SEL$1_2
   6 - SEL$2
   7 - SEL$2   / T2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID2"=10)
   4 - filter( IS NOT NULL)
   5 - filter(LNNVL("T1"."ID2"=10))
   6 - filter(11=:B1)
   7 - access("T2"."ID1"=:B1)

--//很奇怪id=4,依舊選擇過濾,unnest提示沒有用.實際上使用USE_CONCAT相當每個or分支加入LNNVL(條件)來排他符合條件的記錄.
--//也就是oracle依舊選擇的執行計劃不是很理想,甚至比前面還要差.

4.嘗試OR_EXPAND提示:
select /*+ OR_EXPAND */ * from t1 where t1.id1 in  (select  /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );

--//執行計劃如下:
Plan hash value: 1716482303
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |       |     5 (100)|          |      2 |00:00:00.01 |       9 |
|   1 |  VIEW                                  | VW_ORE_BA8ECEFB |      1 |      2 |   156 |     5   (0)| 00:00:01 |      2 |00:00:00.01 |       9 |
|   2 |   UNION-ALL                            |                 |      1 |        |       |            |          |      2 |00:00:00.01 |       9 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T1              |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN                   | I_T1_ID2        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   5 |    NESTED LOOPS SEMI                   |                 |      1 |      1 |   113 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|*  6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1              |      1 |      1 |   109 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  7 |      INDEX RANGE SCAN                  | I_T1_ID1        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  8 |     INDEX RANGE SCAN                   | I_T2_ID1        |      1 |      1 |     4 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$9162BF3C   / VW_ORE_BA8ECEFB@SEL$BA8ECEFB
   2 - SET$9162BF3C
   3 - SET$9162BF3C_1 / T1@SEL$1
   4 - SET$9162BF3C_1 / T1@SEL$1
   5 - SEL$C90BA1D5
   6 - SEL$C90BA1D5   / T1@SEL$1
   7 - SEL$C90BA1D5   / T1@SEL$1
   8 - SEL$C90BA1D5   / T2@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$C90BA1D5")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SET$9162BF3C_1")
      OUTLINE_LEAF(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE_LEAF(@"SEL$BA8ECEFB")
      OUTLINE(@"SET$9162BF3C_2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB")
      INDEX_RS_ASC(@"SET$9162BF3C_1" "T1"@"SEL$1" ("T1"."ID2"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SEL$1")
      INDEX_RS_ASC(@"SEL$C90BA1D5" "T1"@"SEL$1" ("T1"."ID1"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$C90BA1D5" "T1"@"SEL$1")
      INDEX(@"SEL$C90BA1D5" "T2"@"SEL$2" ("T2"."ID1"))
      LEADING(@"SEL$C90BA1D5" "T1"@"SEL$1" "T2"@"SEL$2")
      USE_NL(@"SEL$C90BA1D5" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."ID2"=10)
   6 - filter(LNNVL("T1"."ID2"=10))
   7 - access("T1"."ID1"=11)
   8 - access("T2"."ID1"=11)
       filter("T1"."ID1"="T2"."ID1")

--//12c下oracle選擇正確的執行計劃.可以發現id=2使用UNION-ALL,也就是oracle做了查詢轉換成union all的形式.
--//另外我曾經嘗試將ounline date的提示信息加入到11g環境,執行計劃依舊沒有選擇OR_EXPAND.
--//通過10053事件看看.

SCOTT@test01p> @ 10053x cg5kmfhgczjfd 0
PL/SQL procedure successfully completed.

ORE: after OR Expansion:******* UNPARSED QUERY IS *******
SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "ID1","VW_ORE_BA8ECEFB"."ITEM_2" "ID2","VW_ORE_BA8ECEFB"."ITEM_3" "NAME" FROM  ( (SELECT "T1"."ID1" "ITEM_1","T1"."ID2" "ITEM_2","T1"."NAME" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID2"=10) UNION ALL  (SELECT "T1"."ID1" "ITEM_1","T1"."ID2" "ITEM_2","T1"."NAME" "ITEM_3" FROM "SCOTT"."T1" "T1" WHERE "T1"."ID1"=ANY (SELECT /*+ UNNEST */ "T2"."ID1" "ID1" FROM "SCOTT"."T2" "T2" WHERE "T2"."ID1"=11) AND LNNVL("T1"."ID2"=10))) "VW_ORE_BA8ECEFB"

--//格式化顯示如下:
SELECT "VW_ORE_BA8ECEFB"."ITEM_1" "ID1"
      ,"VW_ORE_BA8ECEFB"."ITEM_2" "ID2"
      ,"VW_ORE_BA8ECEFB"."ITEM_3" "NAME"
  FROM ( (SELECT "T1"."ID1" "ITEM_1"
                ,"T1"."ID2" "ITEM_2"
                ,"T1"."NAME" "ITEM_3"
            FROM "SCOTT"."T1" "T1"
           WHERE "T1"."ID2" = 10)
        UNION ALL
        (SELECT "T1"."ID1" "ITEM_1"
               ,"T1"."ID2" "ITEM_2"
               ,"T1"."NAME" "ITEM_3"
           FROM "SCOTT"."T1" "T1"
          WHERE     "T1"."ID1" = ANY (SELECT /*+ UNNEST */
                                            "T2"."ID1" "ID1"
                                        FROM "SCOTT"."T2" "T2"
                                       WHERE "T2"."ID1" = 11)
                AND LNNVL ("T1"."ID2" = 10))) "VW_ORE_BA8ECEFB";

--//也就是oracle查詢轉換為 UNION ALL的形式.
--//你可以看到第2個條件人為的加入LNNVL ("T1"."ID2" = 10).
--// OR_EXPAND 提示 與 USE_CONCAT 提示到底有什麼不同?

5.補充使用USE_CONCAT看到的情況:

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in  (select /*+unnest */ t2.id1 from t2 where t2.id1=11 ) or  (t1.id2=10 );

SCOTT@test01p> @ 10053x 18h6hkqcqq3w2 0
PL/SQL procedure successfully completed.

--//看這些太煩,不過可以發現如下:
LORE: Or-Expansion validity checks failed on query block SEL$2 (#2) because Cost based OR expansion enabled

SYS@test01p> @ hide or_exp
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%or_exp%')
NAME                               DESCRIPTION                                       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------- ------------------------------------------------- ------------- ------------- ------------
_no_or_expansion                   OR expansion during optimization disabled         TRUE          FALSE         FALSE
_optimizer_cbqt_or_expansion       enables cost based OR expansion                   TRUE          ON            ON
_optimizer_interleave_or_expansion interleave OR Expansion during CBQT               TRUE          TRUE          TRUE
_optimizer_or_expansion            control or expansion approach used                TRUE          DEPTH         DEPTH
_optimizer_or_expansion_subheap    Use subheap for optimizer or-expansion            TRUE          TRUE          TRUE
_or_expand_nvl_predicate           enable OR expanded plan for NVL/DECODE predicate  TRUE          TRUE          TRUE
6 rows selected.
--//也就是12c預設打開因為以上原因.不過我嘗試"_optimizer_cbqt_or_expansion"=off也無效.放棄!!

--//我也嘗試提高全表掃描的成本看看是否執行計劃會發生改變,不過依舊沒用.
SCOTT@test01p> exec dbms_stats.SET_TABLE_STATS(user,'T1',NUMBLKS=>800000000000);
PL/SQL procedure successfully completed.


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

-Advertisement-
Play Games
更多相關文章
  • 清清楚楚地搭建MongoDB資料庫（以搭建4.0.4版本的副本集為例）
    數據的目錄文件層次設計 我們一般採用多實例的方式，而不是將所有的資料庫儘可能地放在一個實例中。 主要基於以下考慮： 1:不同業務線對應的資料庫放在不同的實例上，部分操作的運維時間容易協調等到。 2:相互獨立，減少相互干擾。不會因為某個業務的激增或某個開發Team的代碼問題，拖累太多的資料庫。 3:實 ...
  • Mysql語句 AND 和 OR 的運用
    數據記錄 查詢方式1 查詢方式2 查詢方式3 ...
  • Ubuntu 12.04上安裝HBase並運行
    Ubuntu 12.04上安裝HBase並運行 作者：凱魯嘎吉 - 博客園 http://www.cnblogs.com/kailugaji/ 一、HBase的安裝 在官網上下載HBase-1.1.2，將其解壓到/home/wrr文件夾下 配置環境變數 在.bashrc文件最後添加 查看HBase版 ...
  • Ubuntu 12.04上安裝MySQL並運行
    Ubuntu 12.04上安裝MySQL並運行 作者：凱魯嘎吉 - 博客園 http://www.cnblogs.com/kailugaji/ 安裝MySQL資料庫 確認是否安裝成功 當mysql節點處於LISTEN狀態表示啟動成功 登錄資料庫 MySQL資料庫常用操作 1.顯示所有資料庫（註意後面 ...
  • redis 系列25 哨兵Sentinel (高可用演示 下)
    一. Sentinel 高可用環境準備 1.1 Sentinel 集群環境 1.2 Redis主庫庫環境,主從庫搭建在(redis 系列22 複製Replication 下) 二. Sentinel 配置說明 2.1 啟動Sentinel服務方法 對於啟動Sentinel服務有二種方法： (1)是使 ...
  • Call to localhost/127.0.0.1:9000 failed on connection exception:java.net.ConnectException的解決方案
    Call to localhost/127.0.0.1:9000 failed on connection exception:java.net.ConnectException的解決方案 作者：凱魯嘎吉 - 博客園 http://www.cnblogs.com/kailugaji/ 在啟動hado ...
  • Spark MemoryManager記憶體模型
    ...
  • 經典51道SQL查詢練習題
    數據表介紹 --1.學生表 Student(SId,Sname,Sage,Ssex) --SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別 --2.課程表 Course(CId,Cname,TId) --CId 課程編號,Cname 課程名稱,TId 教師編號 --3. ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類