欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle查看SQL執(zhí)行計劃的常見方法總結(jié)

 更新時間：2025年08月09日 09:50:08   作者：Mike117.  
在日常的運維工作中,SQL優(yōu)化是DBA的進階技能,SQL優(yōu)化的前提是要看SQL的執(zhí)行計劃是否正確,這篇文章主要介紹了Oracle查看SQL執(zhí)行計劃的常見方法,需要的朋友可以參考下

執(zhí)行計劃：

在SQL的執(zhí)行過程中，執(zhí)行計劃的重要性不容忽視。一個恰當(dāng)?shù)膱?zhí)行計劃是確保SQL語句高效運行的關(guān)鍵因素之一。每一條SQL語句，在任意類型的數(shù)據(jù)庫系統(tǒng)中，都與其特定的執(zhí)行計劃相對應(yīng)。特別是在Oracle數(shù)據(jù)庫環(huán)境中，執(zhí)行計劃可以進一步區(qū)分為估計的執(zhí)行計劃和實際執(zhí)行的執(zhí)行計劃（其他數(shù)據(jù)庫管理系統(tǒng)也存在類似的概念）。因此，掌握如何準(zhǔn)確地查看特別是實際發(fā)生的執(zhí)行計劃的方法變得尤為關(guān)鍵。常用的方法包括但不限于以下幾種：

  • 工具類的圖形化界面點擊（如PLSQLDEV中的F5）
  • 黑屏工具SQL*Plus中的AUTOTRACE工具
  • EXPLAIN PLAN FOR 解釋之后的執(zhí)行計劃
  • DBMS_XPLAN提取的Cursor或者PLAN_TABLE或者AWR中的
  • Oracle11g版本以上的SQL_MONITOR
  • 萬能的10046和10053追蹤事件

分類列舉：

在Oracle數(shù)據(jù)庫中，執(zhí)行計劃主要分為兩類：估算的執(zhí)行計劃和實際的執(zhí)行計劃。接下來，我們將針對這兩種類型的執(zhí)行計劃，探討不同的查看方法及其所呈現(xiàn)的是估算結(jié)果還是實際運行情況。

估算的執(zhí)行計劃：

對于估算的執(zhí)行計劃，可以采用前三種方法，分別為：

工具類圖形化界面：

打開PLSQL_DEV工具，右上角選擇新建一個Explain Plan Window，輸入我們想查看執(zhí)行計劃的SQL，然后點左上角的執(zhí)行，得到執(zhí)行計劃（估算的）如下：

注意標(biāo)簽選擇Text格式，除了樹形的執(zhí)行計劃信息我們還可以得到額外的謂詞過濾信息。

AUTOTRACE方式

通過使用如SQL*Plus這樣的命令行工具連接至數(shù)據(jù)庫，可以通過設(shè)置SET AUTOTRACE <選項>來獲取SQL語句的預(yù)估執(zhí)行計劃。AUTOTRACE功能提供了多種選項，每種選項具有不同的用途：

  • ON：開啟自動跟蹤模式，顯示查詢的結(jié)果以及其執(zhí)行計劃。
  • OFF：關(guān)閉自動跟蹤模式。
  • TRACEONLY：僅顯示執(zhí)行計劃而不顯示查詢結(jié)果。
  • EXPLAIN：僅生成執(zhí)行計劃，不實際執(zhí)行查詢。
  • STATISTICS：除了執(zhí)行計劃外，還提供關(guān)于執(zhí)行過程中發(fā)生的統(tǒng)計信息，比如邏輯讀取次數(shù)等。

一通常最常用的命令是 SET AUTOTRACE，該命令主要用于展示SQL語句的執(zhí)行計劃及相關(guān)的統(tǒng)計信息，具體使用方法如下：

SQL> set autot trace
SQL> select count(*) from scott.emp where empno=7788;
執(zhí)行計劃信息：
Execution Plan
----------------------------------------------------------
Plan hash value: 1729829196
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
統(tǒng)計信息：
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

和上一個方法相比優(yōu)點是可以看到執(zhí)行過程中的統(tǒng)計信息。

EXPLAIN PLAN FOR方式

同樣使用黑屏工具連接到數(shù)據(jù)庫執(zhí)行，具體使用方法如下：

explain plan for select count(*) from scott.emp;

命令執(zhí)行完成之后會把當(dāng)前sql對應(yīng)的執(zhí)行計劃信息存放進當(dāng)前用戶的plan_table表里，然后再通過如下方法獲取上一條執(zhí)行的SQL對應(yīng)執(zhí)行計劃，如下：

SQL> set lines 900 pages 900
SQL> explain plan for select count(*) from scott.emp where empno=7788;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1729829196
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
14 rows selected.

真實的執(zhí)行計劃

DBMS_XPLAN包方式

DBMS_XPLAN 包提供了從 PLAN_TABLE、游標(biāo)以及 AWR（Automatic Workload Repository）中提取與 SQL 語句相關(guān)聯(lián)的執(zhí)行計劃的功能。在實際應(yīng)用中，最常見的使用場景是從 PLAN_TABLE 和游標(biāo)中獲取這些信息。特別地，通過游標(biāo)獲取的執(zhí)行計劃能夠附加諸如 A-TIME（實際時間）和 A-ROWS（實際行數(shù)）等額外指標(biāo)，從而提供了一個更為詳盡且基于實際運行情況的執(zhí)行計劃視圖。

查看帶有實際執(zhí)行時間的真實執(zhí)行計劃，操作方法如下：

#先為SQL添加/*+ gather_plan_statistics */這個hint：

select /*+ gather_plan_statistics */ count(*) from scott.emp_new where empno=7788;
COUNT(*)
----------
4096

#SQL執(zhí)行完成之后使用如下語句獲取真實執(zhí)行計劃信息：

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dsz37xu3fzz97, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from scott.emp_new
where empno=7788
Plan hash value: 4139378512
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 374 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 374 |
|* 2 | TABLE ACCESS FULL| EMP_NEW | 1 | 4187 | 4096 |00:00:00.01 | 374 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPNO"=7788)
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.

有些時候也可以通過的dbms_xplan包獲取當(dāng)前正在執(zhí)行的SQL對應(yīng)的執(zhí)行計劃，具體操作如下：

#運行一個比較耗時間的慢SQL：
select /*+ use_nl(a,b) */ count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno;
#通過v$sql找到這個sql_id:
select sql_id,sql_text,child_number from v$sql where sql_text like '%use_nl%';

SQL_MONITO方式

SQL Monitor是Oracle 11g版本中引入的一項新特性，旨在對資源消耗較高的SQL語句自動進行監(jiān)控。當(dāng)檢測到符合條件的SQL執(zhí)行時，Oracle數(shù)據(jù)庫將默認啟動對該SQL語句的監(jiān)視過程，并將收集到的相關(guān)數(shù)據(jù)存儲于專用的內(nèi)存區(qū)域中，以便后續(xù)用于性能分析與優(yōu)化。利用SQL Monitor功能，用戶能夠獲取被監(jiān)控SQL語句的實際執(zhí)行計劃、運行期間產(chǎn)生的各類統(tǒng)計信息以及發(fā)生的等待事件等詳細資料。

#發(fā)起一個慢查詢語句：

select /*+ use_nl(a,b) */ count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno;

#通過v$sql找到對應(yīng)的SQL_ID為：fta6y74yurgrj

#查看v$sql_monitor視圖是否有數(shù)據(jù)記錄：

select count(*) from v$sql_monitor where sql_id='fta6y74yurgrj'
COUNT(*)
 
----------
 
1 --代表已經(jīng)監(jiān)控到

#使用dbms_sqltune包把監(jiān)控的內(nèi)容調(diào)出來(推薦在白屏工具上調(diào)用)：

select dbms_sqltune.report_sql_monitor(sql_id=>'fta6y74yurgrj') from dual;

#然后將SQL_ID與CHILD_NUMBER都帶入dbms_xplan參數(shù)

select * from table(dbms_xplan.display_cursor('fta6y74yurgrj',0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fta6y74yurgrj, child number 0
-------------------------------------
select /*+ use_nl(a,b) */ count(*) from scott.emp_new a,scott.emp_new b
where a.empno=b.empno
Plan hash value: 2272243336
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5932K(100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | NESTED LOOPS | | 248M| 6168M| 5932K (1)| 19:46:31 |
| 3 | TABLE ACCESS FULL| EMP_NEW | 59018 | 749K| 102 (0)| 00:00:02 |
|* 4 | TABLE ACCESS FULL| EMP_NEW | 4216 | 54808 | 101 (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."EMPNO"="B"."EMPNO")
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.

這樣看來，sql_monitor要比先前的幾種方式更加全面，所得的信息更多，尤其是Activity顯示比較耗時的算子值得我們重點關(guān)注。

當(dāng)然有時候我們需要對一條件不那么耗資源的SQL也進行監(jiān)控，這個時候需要在SQL語句中添加/*+ monitor */這個hint來告訴優(yōu)化器對該條SQL進行強制的監(jiān)控。

10046和10053方式

事件10046與10053均為Oracle數(shù)據(jù)庫中用于內(nèi)部追蹤的重要工具，二者均可提供SQL語句的執(zhí)行計劃及其相關(guān)統(tǒng)計信息。然而，它們各自的關(guān)注點有所不同：事件10046主要聚焦于會話內(nèi)特定SQL語句的實際執(zhí)行路徑；而事件10053則更加側(cè)重于揭示單個SQL語句執(zhí)行計劃的生成過程。在日常實踐中，除非需要深入分析較為復(fù)雜的SQL性能問題，否則通常推薦使用事件10046來進行監(jiān)控和調(diào)試。

值得注意的是，事件10046提供了多個級別選項，其中最常用的為LEVEL 12。此外，該事件還支持針對不同范圍的應(yīng)用，包括但不限于全局（需謹慎使用）、會話、模塊乃至單獨的SQL語句層面。基于本次討論的目的，我們將重點探討如何在會話層面上應(yīng)用事件10046。

#找到當(dāng)前會話的spid信息：
select p.spid from v$session s,v$process p where p.addr =s.paddr and s.sid=(select userenv('sid') from dual);
SPID
------------------------
6879
#對這個spid做oradebug 10046，具體如下：
SQL> oradebug setospid 6879; --設(shè)置spid
Oracle pid: 33, Unix process pid: 6879, image: oracle@centos7 (TNS V1-V3)
SQL> oradebug tracefile_name; --獲取trace文件名
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6879.trc
SQL> oradebug event 10046 trace name context forever,level 12; --開啟10046 level 12
Statement processed.
#執(zhí)行我們想要查看執(zhí)行計劃的SQL：
SQL> select count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno;
COUNT(*)
----------
234881024
#關(guān)閉會話級別的10046
SQL> oradebug event 10046 trace name context off;
Statement processed.
#找到對應(yīng)的trc文件，并作tkprof格式化處理：
[oracle@centos7 arch]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6879.trc sql.trc
TKPROF: Release 11.2.0.4.0 - Development on Mon May 19 19:36:45 2025
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

#打開格式化的trc文件并找到對應(yīng)的執(zhí)行計劃信息：

總結(jié)：

Oracle數(shù)據(jù)庫中的執(zhí)行計劃對于優(yōu)化SQL語句的執(zhí)行性能至關(guān)重要。文檔詳細介紹了如何查看估算和實際的執(zhí)行計劃，提供了多種方法和工具，包括圖形化界面、AUTOTRACE、EXPLAIN PLAN FOR語句、DBMS_XPLAN包、SQL Monitor以及10046和10053追蹤事件。

通過這些方法，我們可以更深入地理解SQL語句的執(zhí)行過程，并據(jù)此進行性能優(yōu)化，雖然文檔提供了多種查看執(zhí)行計劃的方法，但在實際應(yīng)用中，如何根據(jù)不同的業(yè)務(wù)場景和性能需求，選擇最合適的執(zhí)行計劃查看方法，以及如何根據(jù)執(zhí)行計劃的結(jié)果進行有效的SQL優(yōu)化，仍然是一個值得深入探討的問題。

到此這篇關(guān)于Oracle查看SQL執(zhí)行計劃的文章就介紹到這了,更多相關(guān)Oracle查看SQL執(zhí)行計劃內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論