sql-查看執行計劃的方法

来源:http://www.cnblogs.com/gull/archive/2016/06/02/5547905.html
-Advertisement-
Play Games

sql執行計劃：把SQL語句拆分為每個的操作步驟組合，按照一定的順序執行得出結果，查看並看懂執行計劃是調優的關鍵步驟 查看執行計劃的方法 DBMS_XPLAN包 sql*plus AUTO trace V$SQL_PLAN、DBA_HIST_SQL_PLAN ?/rdbms/admin/awrsqr ...


sql執行計劃：把SQL語句拆分為每個的操作步驟組合，按照一定的順序執行得出結果，查看並看懂執行計劃是調優的關鍵步驟

查看執行計劃的方法

  • DBMS_XPLAN包
  • sql*plus AUTO trace
  • V$SQL_PLAN、DBA_HIST_SQL_PLAN
  • ?/rdbms/admin/awrsqrpt.sql
  • 工具類：toad、pl/SQL DEV
  • 跟蹤dump：10046,10053

大多數人比較喜歡用工具直接看，以下來說明這些方式的不同用處

一、DBMS_XPLAN包

10g以後可以查看AWR中的語句執行計劃，也可以查看當前記憶體中游標的執行計劃。

主要有以下五個方法：

  • DISPLAY - 格式化並顯示plan table中內容，類似工具的F5（EXPLAIN PLAN FOR …）

  • DISPLAY_AWR -格式和顯示存儲在AWR中的SQL語句的執行計劃。數據來源: DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT

  • DISPLAY_CURSOR -格式和顯示任何載入游標執行計劃的內容。數據來源：V$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL

  • DISPLAY_SQL_PLAN_BASELINE - 顯示一個或多個執行計劃的SQL語句通過SQL handle 標識 。數據來源：DBA_SQL_PLAN_BASELINES

  • DISPLAY_SQLSET - 格式和顯示存儲在sql調優集中的執行計劃內容。數據來源：ALL_SQLSET_STATEMENTS、ALL_SQLSET_PLANS

 

最常用的DISPLAY_AWR和DISPLAY_CURSOR，來闡述具體的調用方式，顯示的內容

（一）、DISPLAY_CURSOR

顯示當前shared pool->library cache中的執行計劃

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id            IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no   IN  NUMBER    DEFAULT  0, 
   format            IN  VARCHAR2  DEFAULT  'TYPICAL');
參數 說明（常用說明）
sql_id SQL的唯一標識，取SELECT SQL_ID FROM V$SQL，為null的情況下，取上一條語句的執行計劃
cursor_child_no 子游標的id號，為null，取出全部子游標的執行計劃
format 輸出格式化
預設TYPICAL
ADVANCED +PEEKED_BINDS
ALLSTATS

例子查看

#預設格式輸出
select * from  table(dbms_xplan.display_cursor('bjqjt2dfvya84',null))

SQL_ID  bjqjt2dfvya84, child number 0
-------------------------------------
select *　from emp where empno=7521

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7521)

＃格式化為ADVANCED +PEEKED_BINDS
select * from  table(dbms_xplan.display_cursor('bjqjt2dfvya84',null,'ADVANCED +PEEKED_BINDS'))

SQL_ID  bjqjt2dfvya84, child number 0
-------------------------------------
select *　from emp where empno=7521

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7521)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]


＃格式化ALLSTATS
兩種處理方式： * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
select /*+gather_plan_statistics*/  *　from emp where empno=7521

select * from  table(dbms_xplan.display_cursor('dvj95t2z1gh2a',null,'ALLSTATS'))

SQL_ID  dvj95t2z1gh2a, child number 0
-------------------------------------
select /*+gather_plan_statistics*/  *　from emp where empno=7521

Plan hash value: 2949544139

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7521)
A-ROW是實際的行數，E-ROW是優化器評估的

 

（二）、DISPLAY_AWR

顯示AWR中的SQL的執行計劃，對分析歷史SQL慢的相當有幫組

DBMS_XPLAN.DISPLAY_AWR(
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER DEFAULT NULL,
   db_id             IN      NUMBER DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT TYPICAL);
參數 常用說明
SQL_ID SQL的唯一標識，取SELECT SQL_ID FROM DBA_HIST_SQLTEXT
PLAN_HASH_VALUE 指定SQL語句的PLAN_HASH_VALUE,忽略取全部的SQL_ID下的執行計劃
DB_ID 預設取V$DATABASE.DATABASE_ID
FORMAT 輸出格式化
預設TYPICAL
ADVANCED +PEEKED_BINDS

例子查看

#預設輸出的格式
select * from  table(dbms_xplan.display_awr('bjqjt2dfvya84'));

SQL_ID bjqjt2dfvya84
--------------------
select *　from emp where empno=7521

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------


#通過格式化ADVANCED +PEEKED_BINDS
select * from  table(dbms_xplan.display_awr('bjqjt2dfvya84',FORMAT=>'ADVANCED +PEEKED_BINDS'));
SQL_ID bjqjt2dfvya84
--------------------
select *　from emp where empno=7521

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
      END_OUTLINE_DATA
  */

 

二、sqlplus  autotrace

scott@GULL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

可以體現執行語句的執行計劃和統計信息

scott@GULL> SET autotrace  trace
scott@GULL> select *　from emp where empno=7521;


執行計劃
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7521)


統計信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        902  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@GULL>

 

三、V$SQL_PLAN

直接取數據字典的源數據

scott@GULL> col id format 99;
scott@GULL> col parent_id format 99999;
scott@GULL> col operation format a25;
scott@GULL> col options format a20;
scott@GULL> select id,parent_id,operation,options from V$SQL_PLAN where sql_id='bjqjt2dfvya84';

 ID PARENT_ID OPERATION                 OPTIONS
--- --------- ------------------------- --------------------
  0           SELECT STATEMENT
  1         0 TABLE ACCESS              BY INDEX ROWID
  2         1 INDEX                     UNIQUE SCAN

 

四、?/rdbms/admin/awrsqrpt.sql

查看AWR中執行計劃，調用這個腳本後，相關的直觀展現

scott@GULL> @?/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3239200222 GULL                1 gullstby


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
輸入 report_type 的值:  html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3239200222        1 GULL         gullstby     gull02

Using 3239200222 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


輸入 num_days 的值:  1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
gullstby     GULL               368 01 6月  2016 10:08     1
                                369 01 6月  2016 10:12     1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值:  368
Begin Snapshot Id specified: 368

輸入 end_snap 的值:  369
End   Snapshot Id specified: 369




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
輸入 sql_id 的值:  bjqjt2dfvya84
SQL ID specified:  bjqjt2dfvya84

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_368_369.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

輸入 report_name 的值:  /home/oracle/0601.html
 
 

image

 
 

 

五、工具類：toad（ctrl+e）、pl/SQL DEV(f5)

工具類查看執行計劃，非常快捷、簡單、能清楚知道每部步驟的優先順序，但是他們實際調用的是EXPLAIN PLAN for，寫入plan_TABLE，是優化器的預評估，不是正在SQL執行時的執行計劃。簡單分析是有用的，但是在你的應用程式中SQL很慢，常用的SQL用toad等很快，此時就不要在用工具快捷方式去查看執行計劃，要去分析正在執行的執行計劃，採用以上的內容方式。

toad(ctrl+e)

image

 

五、10046

10046這個事件是查看SQL具體的執行步驟，等待的是什麼事件，實際內部調用的是哪些SQL。SQL語句執行很慢時，可以查看具體等待的是什麼，導致執行這麼慢。還有對我們瞭解create 、truncate具體在執行哪些語句有幫組。

SQL> conn /as sysdba
已連接。
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 28';

會話已更改。

SQL> select *　from scott.emp where empno=7521;

SQL> select c.value || '/' || d.instance_name || '_ora_' ||
  2            a.spid || '.trc' ||
  3             case when e.value is not null then '_'||e.value end trace
  4        from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
  5
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 單元測試框架junit
    #單元測試junit* 定義一個類繼承AndroidTestCase，在類中定義方法，即可測試該方法 * 在指定指令集時，targetPackage指定你要測試的應用的包名 <instrumentation android:name="android.test.InstrumentationTest ...
  • 使用JAVA與SmartFoxServer來實現游戲伺服器概述
    SmartFoxServer 是專門為Adobe Flash設計的跨平臺socket伺服器，讓開發者高效地開發多人應用及游戲. 該伺服器主要用來創建多玩家游戲。並提供強大的製作工具，各種回合制游戲和實時游戲都可以勝任。 SmartFoxServer 支持的播放器從Flash player6版到最新的 ...
  • iOS-使用代碼約束佈局（Masonry）
    一、引子 學完了可視化編程的Xib和Storyboard，LZ對它們的感受就是的就是UI控制項創建直接拖拽，尺寸適配加約束，Storyboard的頁面跳轉邏輯清晰可見，比起代碼佈局節省了很多的工作量。但是LZ相信還是很多人喜歡用純代碼來編寫一個程式的（LZ就是一個，用代碼寫出來東西的成就感很足！），所 ...
  • OC 協議
    OC 協議 概念：定義了一個介面，其他類負責來實現這些介面。如果你的類實現了一個協議的方法時，則說該類遵循此協議。 非正式協議：非正式協議雖名為協議，但實際上是掛於NSObject上的未實現分類(Unimplemented Category)的一種稱謂。 協議的格式： 協議中定義的方法還有兩個修飾符 ...
  • Android Fragment使用(二) 嵌套Fragments (Nested Fragments) 的使用及常見錯誤
    嵌套Fragments (Nested Fragments), 是在Fragment內部又添加Fragment. 使用時, 主要要依靠宿主Fragment的 `getChildFragmentManager()` 來獲取FragmentManger. 雖然看起來和在activity中添加fragme... ...
  • mysql 判斷表欄位或索引是否存在
    判斷欄位是否存在： 判斷索引是否存在： 從這兩段可以看出很多東西，具體可以自己試驗一下 ...
  • SQL SERVER 中如何用腳本管理作業
    在SQL SERVER中用腳本管理作業，在絕大部分場景下，腳本都比UI界面管理作業要高效、簡潔。打個簡單的比方，如果你要查看作業的運行時長，如果用UI界面查看，100個作業，你就得在歷史記錄裡面至少查看一百次甚至更多，還要記錄、統計作業各個步驟的執行時間。而用腳本，一個查詢就OK了。這篇文章分享一些... ...
  • Mysql創建函數時報錯
    先去查詢 show variables like '%func%' ; 這個語句，如果該語句最後輸出的值是OFF 那麼就用下麵的語句去修改就可以：set global log_bin_trust_function_creators = 1 然後再創建，就OK了！ ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類