dbms_xplan之display_cursor函式的使用（R0.1）
DBMS_XPLAN包中display_cursor函式不同於display函式，display_cursor用於顯示SQL語句的真實的執行計劃，在大多數情況下，顯示真實
的執行計劃有助於更好的分析SQL語句的全過程，尤其是執行此SQL語句實時的I/O開銷。透過對比預估的I/O與真實的I/O開銷來判斷SQL語句所存
在問題，如缺少統計資訊，SQL語句執行的次數，根據實際中間結果集的大小來選擇合適的連線方式等。本文僅僅講述display_cursor函式的使
用。
有關執行計劃中各欄位模組的描述請參考： 執行計劃中各欄位各模組描述
有關由SQL語句來獲取執行計劃請參考： 使用 EXPLAIN PLAN 獲取SQL語句執行計劃
有關使用autotrace來獲取執行計劃請參考： 啟用 AUTOTRACE 功能
有關dbms_xplan之display函式請參考： dbms_xplan之display函式的使用
一、display_cursor函式用法
1、display_cursor函式語法
2、display_cursor函式引數描述
sql_id
指定位於庫快取執行計劃中SQL語句的父遊標。預設值為null。當使用預設值時當前會話的最後一條SQL語句的執行計劃將被返回
可以透過查詢V$SQL 或 V$SQLAREA的SQL_ID列來獲得SQL語句的SQL_ID。
child_number
指定父遊標下子游標的序號。即指定被返回執行計劃的SQL語句的子游標。預設值為0。如果為null，則sql_id所指父遊標下所有子游標
的執行計劃都將被返回。
format
控制SQL語句執行計劃的輸出部分，即哪些可以顯示哪些不顯示。使用與display函式的format引數與修飾符在這裡同樣適用。
除此之外當在開啟statistics_level=all時或使用gather_plan_statistics提示可以獲得執行計劃中實時的統計資訊
有關詳細的format格式描述請參考：dbms_xplan之display函式的使用 中format引數的描述
下面給出啟用統計資訊時format新增的修飾符
iostats 控制I/O統計的顯示
last 預設，顯示所有執行計算過的統計。如果指定該值，則只顯示最後一次執行的統計資訊
memstats 控制pga相關統計的顯示
allstats 此為iostats memstats的快捷方式，即allstats包含了iostats和memstats
run_stats_last 等同於iostats last。只能用於oracle 10g R1
run_stats_tot 等同於iostats。只能用於oracle 10g R1
二、演示使用display_cursor函式獲取執行計劃
1、當前資料庫版本以及載入執行計劃到庫快取
2、檢視真實的執行計劃
3、檢視真實執行計劃並獲得統計資訊
前提條件
設定引數statistics_level為all,可以基於session級別以及例項級別
或者啟用gather_plan_statistics提示
三、總結
1、與display函式不同，display_cursor顯示的為真實的執行計劃
2、對於format引數，使用與display函式的各個值，同樣適用於display_cursor函式
3、當statistics_level為all或使用gather_plan_statistics提示可以獲得執行時的統計資訊
4、根據真實與預估的統計資訊可以初步判斷SQL效率低下的原因，如統計資訊的準確性、主要的開銷位於那些步驟等
轉載地址：http://blog.csdn.net/leshami/article/details/6866925
的執行計劃有助於更好的分析SQL語句的全過程，尤其是執行此SQL語句實時的I/O開銷。透過對比預估的I/O與真實的I/O開銷來判斷SQL語句所存
在問題，如缺少統計資訊，SQL語句執行的次數，根據實際中間結果集的大小來選擇合適的連線方式等。本文僅僅講述display_cursor函式的使
用。
有關執行計劃中各欄位模組的描述請參考： 執行計劃中各欄位各模組描述
有關由SQL語句來獲取執行計劃請參考： 使用 EXPLAIN PLAN 獲取SQL語句執行計劃
有關使用autotrace來獲取執行計劃請參考： 啟用 AUTOTRACE 功能
有關dbms_xplan之display函式請參考： dbms_xplan之display函式的使用
一、display_cursor函式用法
1、display_cursor函式語法
- DBMS_XPLAN.DISPLAY_CURSOR(
- sql_id IN VARCHAR2 DEFAULT NULL,
- child_number IN NUMBER DEFAULT NULL,
- format IN VARCHAR2 DEFAULT 'TYPICAL');
2、display_cursor函式引數描述
sql_id
指定位於庫快取執行計劃中SQL語句的父遊標。預設值為null。當使用預設值時當前會話的最後一條SQL語句的執行計劃將被返回
可以透過查詢V$SQL 或 V$SQLAREA的SQL_ID列來獲得SQL語句的SQL_ID。
child_number
指定父遊標下子游標的序號。即指定被返回執行計劃的SQL語句的子游標。預設值為0。如果為null，則sql_id所指父遊標下所有子游標
的執行計劃都將被返回。
format
控制SQL語句執行計劃的輸出部分，即哪些可以顯示哪些不顯示。使用與display函式的format引數與修飾符在這裡同樣適用。
除此之外當在開啟statistics_level=all時或使用gather_plan_statistics提示可以獲得執行計劃中實時的統計資訊
有關詳細的format格式描述請參考：dbms_xplan之display函式的使用 中format引數的描述
下面給出啟用統計資訊時format新增的修飾符
iostats 控制I/O統計的顯示
last 預設，顯示所有執行計算過的統計。如果指定該值，則只顯示最後一次執行的統計資訊
memstats 控制pga相關統計的顯示
allstats 此為iostats memstats的快捷方式，即allstats包含了iostats和memstats
run_stats_last 等同於iostats last。只能用於oracle 10g R1
run_stats_tot 等同於iostats。只能用於oracle 10g R1
二、演示使用display_cursor函式獲取執行計劃
1、當前資料庫版本以及載入執行計劃到庫快取
- SQL> select * from v$version where rownum<2;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- SQL> SELECT ename,dname,loc
- 2 FROM emp e, dept d
- 3 WHERE e.deptno = d.deptno
- 4 AND e.empno = 7788;
- ENAME DNAME LOC
- ---------- -------------- -------------
- SCOTT RESEARCH DALLAS
2、檢視真實的執行計劃
- /*----------------不傳遞任何引數給display_cursor函式，顯示當前會話最後一條SQL語句的執行計劃-------------*/
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
- SQL> select * from table(dbms_xplan.display_cursor(null,null));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------------
- SQL_ID a67wqmkfb9j65, child number 0
- -------------------------------------
- SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
- e.empno = 7788
- Plan hash value: 2385808155
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | NESTED LOOPS | | 1 | 63 | 3 (0)| 00:00:01 |
- | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
- |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
- | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 409 | 12270 | 1 (0)| 00:00:01 |
- |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
- ----------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("E"."EMPNO"=7788)
- 5 - access("E"."DEPTNO"="D"."DEPTNO")
- /*------------------- 獲得SQL語句的SQL_ID，可以看出此SQL_ID與上面顯示的執行計劃中的SQL_ID一致 ----------*/
- SQL> select sql_id,address,plan_hash_value,hash_value,child_number from v$sql
- 2 where sql_text like '%SELECT ename%' and sql_text not like '%from v$sql%';
- SQL_ID ADDRESS PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER
- ------------- ---------------- --------------- ---------- ------------
- a67wqmkfb9j65 0000000091DBFBC8 2385808155 2629092549 0
- /*-------------- 傳遞SQL_ID以及format引數，並配合修飾符控制執行計劃的輸出 ------------------------*/
- SQL> select * from table(dbms_xplan.display_cursor('a67wqmkfb9j65',null,'typical -predicate -rows'));
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------------
- SQL_ID a67wqmkfb9j65, child number 0
- -------------------------------------
- SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno
- AND e.empno = 7788
- Plan hash value: 2385808155
- --------------------------------------------------------------------------------
- | Id | Operation | Name | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | 3 (100)| |
- | 1 | NESTED LOOPS | | 63 | 3 (0)| 00:00:01 |
- | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 33 | 2 (0)| 00:00:01 |
- | 3 | INDEX UNIQUE SCAN | PK_EMP | | 1 (0)| 00:00:01 |
- | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 12270 | 1 (0)| 00:00:01 |
- | 5 | INDEX UNIQUE SCAN | PK_DEPT | | 0 (0)| |
- --------------------------------------------------------------------------------
3、檢視真實執行計劃並獲得統計資訊
前提條件
設定引數statistics_level為all,可以基於session級別以及例項級別
或者啟用gather_plan_statistics提示
- /*-------------檢視例項引數statistics_level的值，並在會話級別將其設定為all ---------*/
- SQL> show parameter statistics_le
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- statistics_level string ALL
- SQL> alter session set statistics_level=all;
- Session altered.
- SQL> select e.ename,e.sal,s.grade
- 2 from emp e
- 3 join salgrade s
- 4 on e.sal between losal and hisal
- 5 and e.deptno = 20;
- ENAME SAL GRADE
- ---------- ---------- ----------
- SCOTT 3000 4
- FORD 3000 4
- JONES 2975 4
- ADAMS 1100 1
- SMITH 800 1
- /*------- 執行上述SQL語句後獲得其真實的執行計劃，使用了iostats last -predicate -note 修飾符控制顯示輸出 -----*/
- SQL> set pagesize 0
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last -predicate -note'));
- SQL_ID 243b0tpjxj6wv, child number 0
- -------------------------------------
- select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between losal and
- hisal and e.deptno = 20
- Plan hash value: 4204027666
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
- -------------------------------------------------------------------------------------------
- | 1 | MERGE JOIN | | 1 | 1 | 5 |00:00:00.01 | 14 |
- | 2 | SORT JOIN | | 1 | 5 | 5 |00:00:00.01 | 7 |
- | 3 | TABLE ACCESS FULL | EMP | 1 | 5 | 5 |00:00:00.01 | 7 |
- | 4 | FILTER | | 5 | | 5 |00:00:00.01 | 7 |
- | 5 | SORT JOIN | | 5 | 5 | 14 |00:00:00.01 | 7 |
- | 6 | TABLE ACCESS FULL| SALGRADE | 1 | 5 | 5 |00:00:00.01 | 7 |
- -------------------------------------------------------------------------------------------
- /*---------------- 修改會話級別的引數statistics_level為typical並驗證修改結果 ----------------*/
- SQL> alter session set statistics_level=typical;
- SQL> col name format a40
- SQL> col value format a25
- SQL> col display_value format a25
- SQL> select name, value, display_value, isses_modifiable
- 2 from v$parameter
- 3 where isses_modifiable = 'TRUE'
- 4 and name like '%&input_name%';
- Enter value for input_name: statistics_level
- old 4: and name like '%&input_name%'
- new 4: and name like '%statistics_level%'
- NAME VALUE DISPLAY_VALUE ISSES
- ---------------------------------------- ------------------------- ------------------------- -----
- statistics_level TYPICAL TYPICAL TRUE
- /*-------- 使用提示gather_plan_statistics，並獲得其真實執行計劃，使用了allstats -rows修飾符控制顯示輸出 ---*/
- SQL> set pagesize 180
- SQL> SELECT /*+ gather_plan_statistics */ ename,dname,loc
- 2 FROM emp e, dept d
- 3 WHERE e.deptno = d.deptno
- 4 AND d.deptno=20 ORDER BY 1,2,3;
- ENAME DNAME LOC
- ---------- -------------- -------------
- ADAMS RESEARCH DALLAS
- FORD RESEARCH DALLAS
- JONES RESEARCH DALLAS
- SCOTT RESEARCH DALLAS
- SMITH RESEARCH DALLAS
- SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats -rows'));
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------
- SQL_ID d2hh42yzqqjz7, child number 0
- -------------------------------------
- SELECT /*+ gather_plan_statistics */ ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
- d.deptno=20 ORDER BY 1,2,3
- Plan hash value: 3339094711
- ---------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
- ---------------------------------------------------------------------------------------------------------------------
- | 1 | SORT ORDER BY | | 1 | 5 |00:00:00.01 | 9 | 2048 | 2048 | 1/0/0|
- | 2 | NESTED LOOPS | | 1 | 5 |00:00:00.01 | 9 | | | |
- | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.01 | 2 | | | |
- |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 | | | |
- |* 5 | TABLE ACCESS FULL | EMP | 1 | 5 |00:00:00.01 | 7 | | | |
- ---------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("D"."DEPTNO"=20)
- 5 - filter("E"."DEPTNO"=20)
- Note
- -----
- - dynamic sampling used for this statement
三、總結
1、與display函式不同，display_cursor顯示的為真實的執行計劃
2、對於format引數，使用與display函式的各個值，同樣適用於display_cursor函式
3、當statistics_level為all或使用gather_plan_statistics提示可以獲得執行時的統計資訊
4、根據真實與預估的統計資訊可以初步判斷SQL效率低下的原因，如統計資訊的準確性、主要的開銷位於那些步驟等
轉載地址：http://blog.csdn.net/leshami/article/details/6866925
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26613085/viewspace-1125688/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dbms_xplan之display_cursor函式的使用函式
- dbms_xplan 函式包函式
- 執行計劃函式display和display_cursor函式
- PHP之string之ord()函式使用PHP函式
- Dart語法篇之函式的使用(四)Dart函式
- jmeter學習指南之常用函式的使用JMeter函式
- Golang 基礎之函式使用 (三)Golang函式
- Golang 基礎之函式使用 (二)Golang函式
- Golang 基礎之函式使用 (一)Golang函式
- 字串函式之Strtok()函式字串函式
- 函式: 函式是怎麼使用的?函式
- Oracle開發專題之：分析函式的使用Oracle函式
- 函式的祕密之 函式返回值函式
- PHP之string之str_split()函式使用PHP函式
- AWK高階之內部函式使用函式
- 使用bind()函式的產生的函式用作建構函式this的指向函式
- Shell 函式的使用函式
- Thunk函式的使用函式
- Generator 函式的使用函式
- LNNVL函式的使用函式
- Python 擴充之特殊函式（lambda 函式，map 函式，filter 函式，reduce 函式）Python函式Filter
- Kotlin 之 let、with、run、apply、also 函式的使用KotlinAPP函式
- PHP之string之str_pad()函式使用PHP函式
- 常見函式之單行函式函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- round函式與trunc函式的使用方法函式
- 如何使用函式指標呼叫類中的函式和普通函式函式指標
- 11. 使用MySQL之使用資料處理函式MySql函式
- AT&T彙編之使用C庫函式函式
- OCP課程6：SQL之使用組函式SQL函式
- 07：函式之函式的引數和返回值函式
- NumPy之:ndarray中的函式函式
- Kotlin之“with”函式和“apply”函式Kotlin函式APP
- 高階函式的使用函式