[20240313]toad gather_plan_statistics執行計劃相關問題.txt

来源:https://www.cnblogs.com/lfree/p/18071547
-Advertisement-
Play Games

在MySQL 8.0中，可以通過創建自定義哈希函數來處理VARCHAR類型的欄位，以便用作分區鍵。下麵是一個簡單的示例，演示如何在MySQL8.0中創建自定義哈希函數來處理VARCHAR類型的欄位 分區後的表效果 方法一，自定義哈希函數，失敗而告終 創建自定義哈希函數： DELIMITER // C ...


[20240313]toad gather_plan_statistics執行計劃相關問題.txt

--//自己現在已經很少使用toad,使用也是作為輔助功能,畢竟圖形界面能更快的操作顯示信息.
--//昨天遇到一個問題,自己當時沒有反映過來,浪費點時間,做一個記錄避免以後再次犯渾.
--//我一般在toad的sql編輯界面下儘可能看真實的執行計劃
--//參考:https://blog.itpub.net/267265/viewspace-2130781/=>[20161216]toad下顯示真實的執行計劃.txt
--//沒有想到看到的執行計劃統計存在一些問題，看下麵的例子：

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立例子:

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e4;
update t set flag='0' where id=1e4;
commit ;
create index i_t_flag on t(flag);

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => '',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 4',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

--//在flag欄位上建立直方圖.

3.測試:
--//在toad界面上輸入如下語句並執行如下：
SELECT  /*+ gather_plan_statistics */ * FROM T WHERE FLAG=:X;
--//註:加入gather_plan_statistics提示執行計劃可以顯示更多信息.

--//帶入'1'測試看看,執行計劃如下：
--//註意是字元串類型。
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    48 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   5000 |   522K|    48   (0)| 00:00:01 |
---------------------------------------------------------------------------
--//你可以發現並沒有顯示完整的執行計劃統計信息，提示gather_plan_statistics沒有起作用。
--//實際上顯示是explain plan的執行計劃.

--//帶入'0'測試看看，執行計劃如下:
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |      1 |00:00:00.01 |     160 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |      1 |00:00:00.01 |     160 |
--------------------------------------------------------------------------------------------------------------------
-//看到的執行計劃不同，為什麼呢? 實際上問題在於toad執行時每次fetch 1001行。
--//:X = 0 是僅僅返回1條，執行計劃已經完成，可以顯示完整的統計信息。
--//而帶入:X = 1時，僅僅顯示前500條(實際上fetch 1001行)，沒有執行完成，無法顯示完整的執行統計信息。可以拖動滾動條或者按
--//page down鍵到結尾，這樣也可以得到完整的執行計劃。

--//如果滾動到中間(實際上只要不到結尾), 看執行計劃：
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |   1001 |00:00:00.01 |      18 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |   1001 |00:00:00.01 |      18 |
--------------------------------------------------------------------------------------------------------------------
--//看到A-ROWS=1001.也就是第1次fetch的數量,也就是以後在toad下看這類執行計劃註意,這樣看到的執行計劃的統計信息可能不真實!!
--//如果按page down鍵到結尾再看執行計劃：
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

--//如果在sql編輯界面上打開auto trace，全部結果fetch完成就不會出現上面看到的情況了。
--//再次帶入'1'測試看看，執行計劃如下:
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

4.繼續測試：
--//關閉auto trace。
--//再次帶入'1'測試看看，執行計劃如下:
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

--//再次帶入'1'測試看看，執行計劃如下:
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |   1001 |00:00:00.01 |      18 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |   1001 |00:00:00.01 |      18 |
--------------------------------------------------------------------------------------------------------------------
--//註:當時馬上看執行計劃,理論講應該沒有A-Rows之類的統計,本來以為會顯示explain plan的執行計劃,但是看到的卻是
--//A-Rows=1001的情況,我猜測可能是使用dbms_xplan.display_cursor,format加入last的緣故.

--//開始我很困惑,實際上這樣第1次執行看到的是前1次的執行計劃統計信息。

--//使用SQL Tracker跟蹤發現,toad執行如下：
select * from table(dbms_xplan.display_cursor(sql_id => '7pnr0krspk166', cursor_child_no => null, format => 'ALL,
ALLSTATS, LAST, ROWS, BYTES, COST, OUTLINE, PARTITION, PARALLEL, PEEKED_BINDS, PREDICATE, PROJECTION, ALIAS, REMOTE,
NOTE'))

--//加入有參數last，顯示參數我基本全部選上。
--//而第2次執行，因為前面有記錄這個統計信息，這樣就有顯示，雖然不准確。當然這些是我的猜測!!

--//總之，在toad下註意這個細節，建議優化調試sql語句時打開auto trace，這樣看執行計劃的統計信息比較準確,不容易出現誤判。

5.另外的問題註意：
--//你可以註意一個細節，toad下無論帶入'0'還是'1'選擇的執行計劃都是全表掃描，E-Rows=5000並沒有採用直方圖的結果,也就是總記
--//錄的50%.
--//即使小量修改sql語句，sql_id不同,導致重新分析sql語句,第1次帶入參數'0'，執行計劃也是選擇全表掃描。
--//如果仔細看Outline Data部分：
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

--//不知道為什麼toad關閉了綁定變數peek。而且我不知道怎麼打開，使用toad的版本號12.6.0.53,只有toad 9.X版本正常的.
--//這樣使得在toad調試sql語句涉及到這方面相關問題時要小心，而且導致BIND_AWARE提示無效。

SELECT   /*+ gather_plan_statistics BIND_AWARE OPT_PARAM('_optim_peek_user_binds' 'true') */ * FROM T WHERE FLAG=:X;
Plan hash value: 1601196873
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    48 (100)|          |      1 |00:00:00.01 |     160 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   522K|    48   (0)| 00:00:01 |      1 |00:00:00.01 |     160 |
--------------------------------------------------------------------------------------------------------------------
--//還是全表掃描。
 
6.sqlplus下測試：

SCOTT@book> variable x varchar2(32) ;
SCOTT@book> exec :x :='0';
PL/SQL procedure successfully completed.

SCOTT@book> SELECT   /*+ gather_plan_statistics  */ * FROM T WHERE FLAG=:X;
        ID NAME                                     F
---------- ---------------------------------------- -
     10000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
           xxxxxxxxxxxxxxxxxxxx


SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8s7vwp7ykv52x, child number 1
-------------------------------------
SELECT   /*+ gather_plan_statistics  */ * FROM T WHERE FLAG=:X
Plan hash value: 120143814
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |      1 |   107 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG"=:X)
--//建立新的子游標,child number=1,可以使用I_T_FLAG索引。

7.總結：
--//註意toad下調試優化sql語句時註意這個細節，避免以後在這個方面浪費時間。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 常用Linux系統性能分析命令
    Linux系統提供了許多命令來分析系統性能。以下是一些常用的Linux系統性能分析命令： top：實時監視系統的運行狀態和進程信息，包括CPU使用率、記憶體使用情況、進程狀態等。 實例：直接在終端中輸入top，即可實時查看系統進程狀態及資源占用情況。 htop：類似於top，但提供更友好的界面和交互， ...
  • Linux安裝Nginx詳細教程
    一、下載Nginx安裝包 Nginx官網下載地址 根據需求選擇自己需要的版本下載後上傳至伺服器(路徑自行決定)。 如果伺服器有外網，可以直接在伺服器上下載。 wget -c https://nginx.org/download/nginx-1.24.0.tar.gz 二、安裝Nginx 解壓安裝包 ...
  • 關係資料庫範式詳解(Normal form，簡稱NF)
    引言 關係資料庫中的關係滿足一定要求的，滿足不同程度要求的為不同的範式，共有6種範式。 滿足最低要求的叫第一範式，簡稱 1NF；在第一範式的基礎上滿足進一步要求的稱為第二範式，簡稱 2NF; 其餘範式以此類推。 對於各種範式之間有如下關係： 5NF ∈ 4NF ∈ BCNF ∈ 3NF ∈ 2NF ...
  • PG14:auth_delay 插件源碼分析
    auth_delay 讓伺服器在報告身份驗證失敗前短暫暫停，以增加對資料庫密碼進行暴力破解的難度。需要註意的是，這對阻止拒絕服務攻擊毫無幫助，甚至可能加劇攻擊，因為在報告身份驗證失敗前等待的進程仍會占用連接。 要使用這個模塊必須要在 postgresql.conf 中配置參數 shared_prel ...
  • 實例帶你瞭解GaussDB的索引管理
    本文分享自華為雲社區《GaussDB資料庫的索引管理》，作者： Gauss松鼠會小助手2。 一、引言 GaussDB資料庫是華為公司傾力打造的自研企業級分散式關係型資料庫，索引的設計和管理對於提高查詢性能至關重要。下麵將通過實際例子深入研究GaussDB資料庫的索引管理。 二、GaussDB資料庫中 ...
  • PG14:adminpack 插件源碼分析
    adminpack 提供了大量支持功能，pgAdmin 和其他管理工具可以使用這些功能提供額外功能，例如遠程管理伺服器日誌文件。預設情況下，只有資料庫超級用戶才能使用所有這些功能，但其他用戶也可以使用 GRANT 命令使用這些功能。 我們先來看一下他支持的函數，可以通過 \dx+ adminpack ...
  • SQL Server索引查找/掃描沒有出現key lookup的案例淺析
    在我們講解這個案例前，我們先來瞭解/預熱一下SQL Server的兩個概念：鍵查找（key lookup)和RID查找（RID lookup),通常，當查詢優化器使用非聚集索引進行查找時，如果所選擇的列或查詢條件中的列只部分包含在使用的非聚集索引和聚集索引中時，就需要一個查找（lookup）來檢索其 ...
  • InnoDB是事務型資料庫的首選引擎
    支持事務安全表（ACID），支持行鎖定和外鍵; MySQL事務的ACID特性是確保數據準確性和可靠性的基本原則，包括**原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）**。具體如下： 1. **原子性（Atomicity） ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類