Oracle查看SQL執(zhí)行計(jì)劃的常見方法總結(jié)
執(zhí)行計(jì)劃：
在SQL的執(zhí)行過(guò)程中，執(zhí)行計(jì)劃的重要性不容忽視。一個(gè)恰當(dāng)?shù)膱?zhí)行計(jì)劃是確保SQL語(yǔ)句高效運(yùn)行的關(guān)鍵因素之一。每一條SQL語(yǔ)句，在任意類型的數(shù)據(jù)庫(kù)系統(tǒng)中，都與其特定的執(zhí)行計(jì)劃相對(duì)應(yīng)。特別是在Oracle數(shù)據(jù)庫(kù)環(huán)境中，執(zhí)行計(jì)劃可以進(jìn)一步區(qū)分為估計(jì)的執(zhí)行計(jì)劃和實(shí)際執(zhí)行的執(zhí)行計(jì)劃（其他數(shù)據(jù)庫(kù)管理系統(tǒng)也存在類似的概念）。因此，掌握如何準(zhǔn)確地查看特別是實(shí)際發(fā)生的執(zhí)行計(jì)劃的方法變得尤為關(guān)鍵。常用的方法包括但不限于以下幾種：
- 工具類的圖形化界面點(diǎn)擊（如
PLSQLDEV中的F5） - 黑屏工具SQL*Plus中的
AUTOTRACE工具 EXPLAIN PLAN FOR解釋之后的執(zhí)行計(jì)劃DBMS_XPLAN提取的Cursor或者PLAN_TABLE或者AWR中的- Oracle11g版本以上的
SQL_MONITOR - 萬(wàn)能的10046和10053追蹤事件
分類列舉：
在Oracle數(shù)據(jù)庫(kù)中，執(zhí)行計(jì)劃主要分為兩類：估算的執(zhí)行計(jì)劃和實(shí)際的執(zhí)行計(jì)劃。接下來(lái)，我們將針對(duì)這兩種類型的執(zhí)行計(jì)劃，探討不同的查看方法及其所呈現(xiàn)的是估算結(jié)果還是實(shí)際運(yùn)行情況。
估算的執(zhí)行計(jì)劃：
對(duì)于估算的執(zhí)行計(jì)劃，可以采用前三種方法，分別為：
工具類圖形化界面：
打開PLSQL_DEV工具，右上角選擇新建一個(gè)Explain Plan Window，輸入我們想查看執(zhí)行計(jì)劃的SQL，然后點(diǎn)左上角的執(zhí)行，得到執(zhí)行計(jì)劃（估算的）如下：
注意標(biāo)簽選擇Text格式，除了樹形的執(zhí)行計(jì)劃信息我們還可以得到額外的謂詞過(guò)濾信息。
AUTOTRACE方式
通過(guò)使用如SQL*Plus這樣的命令行工具連接至數(shù)據(jù)庫(kù)，可以通過(guò)設(shè)置SET AUTOTRACE <選項(xiàng)>來(lái)獲取SQL語(yǔ)句的預(yù)估執(zhí)行計(jì)劃。AUTOTRACE功能提供了多種選項(xiàng)，每種選項(xiàng)具有不同的用途：
ON：開啟自動(dòng)跟蹤模式，顯示查詢的結(jié)果以及其執(zhí)行計(jì)劃。OFF：關(guān)閉自動(dòng)跟蹤模式。TRACEONLY：僅顯示執(zhí)行計(jì)劃而不顯示查詢結(jié)果。EXPLAIN：僅生成執(zhí)行計(jì)劃，不實(shí)際執(zhí)行查詢。STATISTICS：除了執(zhí)行計(jì)劃外，還提供關(guān)于執(zhí)行過(guò)程中發(fā)生的統(tǒng)計(jì)信息，比如邏輯讀取次數(shù)等。
一通常最常用的命令是 SET AUTOTRACE，該命令主要用于展示SQL語(yǔ)句的執(zhí)行計(jì)劃及相關(guān)的統(tǒng)計(jì)信息，具體使用方法如下：
SQL> set autot trace
SQL> select count(*) from scott.emp where empno=7788;
執(zhí)行計(jì)劃信息：
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)計(jì)信息：
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和上一個(gè)方法相比優(yōu)點(diǎn)是可以看到執(zhí)行過(guò)程中的統(tǒng)計(jì)信息。
EXPLAIN PLAN FOR方式
同樣使用黑屏工具連接到數(shù)據(jù)庫(kù)執(zhí)行，具體使用方法如下：
explain plan for select count(*) from scott.emp;
命令執(zhí)行完成之后會(huì)把當(dāng)前sql對(duì)應(yīng)的執(zhí)行計(jì)劃信息存放進(jìn)當(dāng)前用戶的plan_table表里，然后再通過(guò)如下方法獲取上一條執(zhí)行的SQL對(duì)應(yīng)執(zhí)行計(jì)劃，如下：
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.真實(shí)的執(zhí)行計(jì)劃
DBMS_XPLAN包方式
DBMS_XPLAN 包提供了從 PLAN_TABLE、游標(biāo)以及 AWR（Automatic Workload Repository）中提取與 SQL 語(yǔ)句相關(guān)聯(lián)的執(zhí)行計(jì)劃的功能。在實(shí)際應(yīng)用中，最常見的使用場(chǎng)景是從 PLAN_TABLE 和游標(biāo)中獲取這些信息。特別地，通過(guò)游標(biāo)獲取的執(zhí)行計(jì)劃能夠附加諸如 A-TIME（實(shí)際時(shí)間）和 A-ROWS（實(shí)際行數(shù)）等額外指標(biāo)，從而提供了一個(gè)更為詳盡且基于實(shí)際運(yùn)行情況的執(zhí)行計(jì)劃視圖。
查看帶有實(shí)際執(zhí)行時(shí)間的真實(shí)執(zhí)行計(jì)劃，操作方法如下：
#先為SQL添加/*+ gather_plan_statistics */這個(gè)hint：
select /*+ gather_plan_statistics */ count(*) from scott.emp_new where empno=7788; COUNT(*) ---------- 4096
#SQL執(zhí)行完成之后使用如下語(yǔ)句獲取真實(shí)執(zhí)行計(jì)劃信息：
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.有些時(shí)候也可以通過(guò)的dbms_xplan包獲取當(dāng)前正在執(zhí)行的SQL對(duì)應(yīng)的執(zhí)行計(jì)劃，具體操作如下：
#運(yùn)行一個(gè)比較耗時(shí)間的慢SQL： select /*+ use_nl(a,b) */ count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno; #通過(guò)v$sql找到這個(gè)sql_id: select sql_id,sql_text,child_number from v$sql where sql_text like '%use_nl%';
SQL_MONITO方式
SQL Monitor是Oracle 11g版本中引入的一項(xiàng)新特性，旨在對(duì)資源消耗較高的SQL語(yǔ)句自動(dòng)進(jìn)行監(jiān)控。當(dāng)檢測(cè)到符合條件的SQL執(zhí)行時(shí)，Oracle數(shù)據(jù)庫(kù)將默認(rèn)啟動(dòng)對(duì)該SQL語(yǔ)句的監(jiān)視過(guò)程，并將收集到的相關(guān)數(shù)據(jù)存儲(chǔ)于專用的內(nèi)存區(qū)域中，以便后續(xù)用于性能分析與優(yōu)化。利用SQL Monitor功能，用戶能夠獲取被監(jiān)控SQL語(yǔ)句的實(shí)際執(zhí)行計(jì)劃、運(yùn)行期間產(chǎn)生的各類統(tǒng)計(jì)信息以及發(fā)生的等待事件等詳細(xì)資料。
#發(fā)起一個(gè)慢查詢語(yǔ)句：
select /*+ use_nl(a,b) */ count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno;
#通過(guò)v$sql找到對(duì)應(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)出來(lái)(推薦在白屏工具上調(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.這樣看來(lái)，sql_monitor要比先前的幾種方式更加全面，所得的信息更多，尤其是Activity顯示比較耗時(shí)的算子值得我們重點(diǎn)關(guān)注。
當(dāng)然有時(shí)候我們需要對(duì)一條件不那么耗資源的SQL也進(jìn)行監(jiān)控，這個(gè)時(shí)候需要在SQL語(yǔ)句中添加/*+ monitor */這個(gè)hint來(lái)告訴優(yōu)化器對(duì)該條SQL進(jìn)行強(qiáng)制的監(jiān)控。
10046和10053方式
事件10046與10053均為Oracle數(shù)據(jù)庫(kù)中用于內(nèi)部追蹤的重要工具，二者均可提供SQL語(yǔ)句的執(zhí)行計(jì)劃及其相關(guān)統(tǒng)計(jì)信息。然而，它們各自的關(guān)注點(diǎn)有所不同：事件10046主要聚焦于會(huì)話內(nèi)特定SQL語(yǔ)句的實(shí)際執(zhí)行路徑；而事件10053則更加側(cè)重于揭示單個(gè)SQL語(yǔ)句執(zhí)行計(jì)劃的生成過(guò)程。在日常實(shí)踐中，除非需要深入分析較為復(fù)雜的SQL性能問(wèn)題，否則通常推薦使用事件10046來(lái)進(jìn)行監(jiān)控和調(diào)試。
值得注意的是，事件10046提供了多個(gè)級(jí)別選項(xiàng)，其中最常用的為L(zhǎng)EVEL 12。此外，該事件還支持針對(duì)不同范圍的應(yīng)用，包括但不限于全局（需謹(jǐn)慎使用）、會(huì)話、模塊乃至單獨(dú)的SQL語(yǔ)句層面?；诒敬斡懻摰哪康?，我們將重點(diǎn)探討如何在會(huì)話層面上應(yīng)用事件10046。
#找到當(dāng)前會(huì)話的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
#對(duì)這個(gè)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í)行計(jì)劃的SQL：
SQL> select count(*) from scott.emp_new a,scott.emp_new b where a.empno=b.empno;
COUNT(*)
----------
234881024
#關(guān)閉會(huì)話級(jí)別的10046
SQL> oradebug event 10046 trace name context off;
Statement processed.
#找到對(duì)應(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文件并找到對(duì)應(yīng)的執(zhí)行計(jì)劃信息：
總結(jié)：
Oracle數(shù)據(jù)庫(kù)中的執(zhí)行計(jì)劃對(duì)于優(yōu)化SQL語(yǔ)句的執(zhí)行性能至關(guān)重要。文檔詳細(xì)介紹了如何查看估算和實(shí)際的執(zhí)行計(jì)劃，提供了多種方法和工具，包括圖形化界面、AUTOTRACE、EXPLAIN PLAN FOR語(yǔ)句、DBMS_XPLAN包、SQL Monitor以及10046和10053追蹤事件。
通過(guò)這些方法，我們可以更深入地理解SQL語(yǔ)句的執(zhí)行過(guò)程，并據(jù)此進(jìn)行性能優(yōu)化，雖然文檔提供了多種查看執(zhí)行計(jì)劃的方法，但在實(shí)際應(yīng)用中，如何根據(jù)不同的業(yè)務(wù)場(chǎng)景和性能需求，選擇最合適的執(zhí)行計(jì)劃查看方法，以及如何根據(jù)執(zhí)行計(jì)劃的結(jié)果進(jìn)行有效的SQL優(yōu)化，仍然是一個(gè)值得深入探討的問(wèn)題。
到此這篇關(guān)于Oracle查看SQL執(zhí)行計(jì)劃的文章就介紹到這了,更多相關(guān)Oracle查看SQL執(zhí)行計(jì)劃內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle實(shí)現(xiàn)某表隨機(jī)抽取數(shù)據(jù)(隨機(jī)性抽取)
這篇文章主要介紹了Oracle實(shí)現(xiàn)某表隨機(jī)抽取數(shù)據(jù)(隨機(jī)性抽取),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-08-08
Oracle如何設(shè)置表空間數(shù)據(jù)文件大小
這篇文章主要介紹了Oracle如何設(shè)置表空間數(shù)據(jù)文件大小，文中講解非常細(xì)致，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
這篇文章主要為大家詳細(xì)介紹了oracle 12c的安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-03-03
這篇文章主要介紹了oracle去除空格的三種方式,分別是使用TRIM()函數(shù)去除字符串兩端空格,使用REPLACE()函數(shù)替換掉所有空格,以及利用REPLACE()結(jié)合CHR()函數(shù)去除制表符、換行符、回車符和空格符,這些方法幫助優(yōu)化字符串處理,需要的朋友可以參考下2024-11-11
Oracle使用PL/SQL操作COM對(duì)象...2007-03-03
這篇文章主要給大家介紹了關(guān)于Oracle關(guān)聯(lián)表更新操作的相關(guān)資料, Oracle關(guān)聯(lián)更新是一種用于更新多表中某個(gè)表的數(shù)據(jù)時(shí),引用另一個(gè)表中的數(shù)據(jù),以避免數(shù)據(jù)錯(cuò)誤的技術(shù),需要的朋友可以參考下2023-07-07
Windows Sever 2012下Oracle 12c安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了Windows Sever 2012下Oracle 12c安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-04-04
2009-06-06
oracle ORA-01114、ORA-27067錯(cuò)誤解決方法
本文章總結(jié)了關(guān)于ORA-01114、ORA-27067錯(cuò)誤解決方法，有需要學(xué)習(xí)的朋友可參考一下下哦2012-10-10
這篇文章主要介紹了CenterOs7 安裝oracle19c的方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03