獲取SQL執行計劃的常見幾種方法
一、獲取庫緩沖區中的執行計劃
1. 查詢v$sql動態性能視圖,找到要查詢的SQL語句的sql_id。
2. 調用dbms_xplan包的display_cursor方法,查看該語句執行時的執行計劃。
例如:
SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;
SELECT sql_id FROM v$sql WHERE sql_text='SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('00gyc848k0q4v'));
直接調用display_cursor,不指定sql_id,就可以將剛剛當前會話執行的SQL命令執行計劃從library cache中查詢出來。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
注意:display_cursor支持format參數,可以進行詳細執行計劃信息的抽取。
只能在sqlplus或者sqlplusw上使用,如果是TOAD、PL/SQL develop等其它第三方工具,可能調用程序包dbms_xplan不能正常使用。
二、獲取SQL預執行的執行計劃
Explain plan for 命令在Oracle中,可以對後面的SQL語句進行直接的解析,將執行計劃保存在一個plan_table的中間表中,之後通過dbms_xplan包的display方法進行獲取。
例如:
EXPLAIN PLAN FOR SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
通過指定參數,Explain plan for命令可以顯示更詳細的信息。
例如:
EXPLAIN PLAN FOR SELECT dname FROM emp, dept WHERE emp.deptno=dept.deptno;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'advanced'));
使用EXPLAIN PLAN FOR 需要注意的是:
explain plan for是單純對SQL語句進行優化器分析,獲取並產生到的執行計劃。
這個過程中,並沒有真正執行。
所以,生成的執行計劃有時候會可能有問題,而且進行統計的信息情況沒有autotrace的准確度高。
三、使用autotrace工具分析SQL的執行計劃
1.配置autotrace工具
配置AUTOTRACE 的方法不止一種,以下是其中一種的方式:
(1)cd [ORACLE_HOME]/sqlplus/admin;
(2)作為SYS 或SYSDBA 登錄SQL*Plus;
(3)運行@plustrce.sql;
(4)運行GRANT PLUSTRACE TO PUBLIC。
注:ORACLE_HOME是指Oracle的安裝路徑,如果是在Windows系統下,可以進入到相應路徑再運行腳本。
如果願意,可以把GRANT TO PUBLIC 中的PUBLIC 替換為某個用戶。
通過將PLAN_TABLE置為public,任何人都可以使用SQL*Plus 進行跟蹤。
這麼一來,就不需要每個用戶都安裝自己的計劃表。
以下是腳本plustrce.sql中的內容:
set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
set echo off
大概意思如下:
創建角色plustrace。
將在動態性能視圖v_$sesstat,v_$statname,v_$mystat上的SELECT權限授予該角色。
把該角色授權給DBA用戶,並使其有權限將該角色授予其它用戶。
注:v$_開頭的動態性能視圖是以v_$開頭的視圖的同義詞。
可以通過查詢視圖dba_objects確認相關對象的類型。
例如:
select object_type from dba_objects where object_name=upper('v_$sesstat');
select object_type from dba_objects where object_name=upper('v$sesstat');
2. 關於如何使用autotrace工具
用法1:查看執行計劃、統計信息並且返回sql結果集:
SET AUTOTRACE ON
用法2:查看執行計劃、統計信息不返回sql結果集:
SET AUTOTRACE TRACEONLY
用法3:查看執行計劃不返回統計信息、不返回sql結果集:
SET AUTOTRACE TRACEONLY EXPLAIN
用法4:查看統計信息不返回執行計劃、不返回sql結果集:
SET AUTOTRACE TRACEONLY STATISTICS
以上是查看SQL執行計劃的幾種方式。
關於如何分析Oracle SQL執行計劃