-- ----------------------------------------------------------------------------------------------
--
-- Script: xplan.sql
--
-- Author: Adrian Billington
--
--
-- Description: Creates a package named XPLAN as a wrapper over DBMS_XPLAN. Provides access to
-- the following DBMS_XPLAN pipelined functions:
--
-- 1. DISPLAY;
-- 2. DISPLAY_CURSOR;
-- 3. DISPLAY_AWR (optional - see Notes section for licence implications).
--
-- The XPLAN wrapper package has one purpose: to include an "order" column in the
-- plan output to show the order in which plan operations are performed. See the
-- following example for details.
--
-- Example: DBMS_XPLAN output (format BASIC):
-- ------------------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | MERGE JOIN | |
-- | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | SORT JOIN | |
-- | 5 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------
--
-- Equivalent XPLAN output (format BASIC):
-- --------------------------------------------------------
-- | Id | Order | Operation | Name |
-- --------------------------------------------------------
-- | 0 | 6 | SELECT STATEMENT | |
-- | 1 | 5 | MERGE JOIN | |
-- | 2 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 4 | SORT JOIN | |
-- | 5 | 3 | TABLE ACCESS FULL | EMP |
-- --------------------------------------------------------
--
-- Usage: SELECT * FROM TABLE(XPLAN.DISPLAY(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_CURSOR(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_AWR(...));
--
-- Usage for XPLAN is exactly the same as for DBMS_XPLAN. See the DBMS_XPLAN
-- documentation for all options.
--
-- Note that the only exception to this is that XPLAN.DISPLAY does not contain
-- the FILTER_PREDS parameter available in 10.2+ versions of DBMS_XPLAN.DISPLAY
-- (this parameter enables us to limit the data being returned from an Explain
-- Plan but is of quite limited use).
--
-- See the Notes section for details on the licensing implications of using
-- XPLAN.DISPLAY_AWR.
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: 1) PLAN_TABLE of at least 10.1 format
--
-- 2) Either:
-- SELECT ANY DICTIONARY
-- Or:
-- SELECT on V$DATABASE
-- SELECT on V$SQL_PLAN
-- SELECT on V$SESSION
-- SELECT on V$MYSTAT
-- SELECT on DBA_HIST_SQL_PLAN
--
-- 3) CREATE TYPE, CREATE PROCEDURE
--
-- Notes: *** IMPORTANT: PLEASE READ ***
--
-- 1) Oracle license implications
-- ---------------------------
-- The AWR functionality of XPLAN accesses a DBA_HIST% view which means
-- that it requires an Oracle Diagnostic Pack license. The XPLAN.DISPLAY_AWR
-- pipelined function is therefore disabled by default. It can be included
-- by modifying two substitution variables at the start of the script. Please
-- ensure that you are licensed to use this feature: the author accepts
-- no responsibility for any use of this functionality in an unlicensed database.
--
-- Installation: Installation requires SQL*Plus or any IDE that supports substitution
-- variables and SQL*Plus SET commands. To install, simply run the script in
-- the target schema.
--
-- Creates: 1) XPLAN_OT object type
-- 2) XPLAN_NTT collection type
-- 3) XPLAN package
--
-- Removal: 1) DROP PACKAGE xplan;
-- 3) DROP TYPE xplan_ntt;
-- 4) DROP TYPE xplan_ot;
--
--
-- ----------------------------------------------------------------------------------------------
--
-- Define the "commenting-out" substitution variables for the AWR elements of this utility. The
-- default is commented out. To include the AWR functionality, change the variables to " " (i.e.
-- a single space).
--
SET DEFINE ON
DEFINE _awr_start = "/*"
DEFINE _awr_end = "*/"
--
-- Supporting types for the pipelined functions...
--
CREATE OR REPLACE TYPE xplan_ot AS OBJECT( plan_table_output VARCHAR2(300) );
/
CREATE OR REPLACE TYPE xplan_ntt AS TABLE OF xplan_ot;
/
--
-- Xplan package...
--
CREATE OR REPLACE PACKAGE xplan AS
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_start
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_end
END xplan;
/
CREATE OR REPLACE PACKAGE BODY xplan AS
TYPE ntt_order_map_binds IS TABLE OF VARCHAR2(100);
TYPE aat_order_map IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
g_map aat_order_map;
g_hdrs PLS_INTEGER;
g_len PLS_INTEGER;
g_pad VARCHAR2(300);
----------------------------------------------------------------------------
PROCEDURE reset_state IS
BEGIN
g_hdrs := 0;
g_len := 0;
g_pad := NULL;
g_map.DELETE;
END reset_state;
----------------------------------------------------------------------------
PROCEDURE build_order_map( p_sql IN VARCHAR2,
p_binds IN ntt_order_map_binds ) IS
TYPE rt_id_data IS RECORD
( id PLS_INTEGER
, ord PLS_INTEGER );
TYPE aat_id_data IS TABLE OF rt_id_data
INDEX BY PLS_INTEGER;
aa_ids aat_id_data;
v_cursor SYS_REFCURSOR;
v_sql VARCHAR2(32767);
BEGIN
-- Build SQL template...
-- ---------------------
v_sql := 'WITH sql_plan_data AS ( ' ||
p_sql || '
)
, hierarchical_sql_plan_data AS (
SELECT id
FROM sql_plan_data
START WITH id = 0
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id DESC
)
SELECT id
, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS ord
FROM hierarchical_sql_plan_data';
-- Binds will differ according to plan type...
-- -------------------------------------------
CASE p_binds.COUNT
WHEN 0
THEN
OPEN v_cursor FOR v_sql;
WHEN 1
THEN
OPEN v_cursor FOR v_sql USING p_binds(1);
WHEN 2
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2));
WHEN 3
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2)),
TO_NUMBER(p_binds(3));
END CASE;
-- Fetch the ID and order data...
-- ------------------------------
FETCH v_cursor BULK COLLECT INTO aa_ids;
CLOSE v_cursor;
-- Populate the order map...
-- -------------------------
FOR i IN 1 .. aa_ids.COUNT LOOP
g_map(aa_ids(i).id) := aa_ids(i).ord;
END LOOP;
-- Use the map to determine padding needed to slot in our order column...
-- ----------------------------------------------------------------------
IF g_map.COUNT > 0 THEN
g_len := LEAST(LENGTH(g_map.LAST) + 7, 8);
g_pad := LPAD('-', g_len, '-');
END IF;
END build_order_map;
----------------------------------------------------------------------------
FUNCTION prepare_row( p_curr IN VARCHAR2,
p_next IN VARCHAR2 ) RETURN xplan_ot IS
v_id PLS_INTEGER;
v_row VARCHAR2(4000);
v_hdr VARCHAR2(64) := '%|%Id%|%Operation%|%';
BEGIN
-- Intercept the plan section to include a new column for the
-- the operation order that we mapped earlier. The plan output
-- itself will be bound by the 2nd, 3rd and 4th dashed lines.
-- We need to add in additional dashes, the order column heading
-- and the order value itself...
-- -------------------------------------------------------------
IF p_curr LIKE '---%' THEN
IF p_next LIKE v_hdr THEN
g_hdrs := 1;
v_row := g_pad || p_curr;
ELSIF g_hdrs BETWEEN 1 AND 3 THEN
g_hdrs := g_hdrs + 1;
v_row := g_pad || p_curr;
ELSE
v_row := p_curr;
END IF;
ELSIF p_curr LIKE v_hdr THEN
v_row := REGEXP_REPLACE(
p_curr, '\|',
RPAD('|', GREATEST(g_len-7, 2)) || 'Order |',
1, 2
);
ELSIF REGEXP_LIKE(p_curr, '^\|[\* 0-9]+\|') THEN
v_id := REGEXP_SUBSTR(p_curr, '[0-9]+');
v_row := REGEXP_REPLACE(
p_curr, '\|',
'|' || LPAD(g_map(v_id), GREATEST(g_len-8, 6)) || ' |',
1, 2
);
ELSE
v_row := p_curr;
END IF;
RETURN xplan_ot(v_row);
END prepare_row;
----------------------------------------------------------------------------
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_plan_table VARCHAR2(128) := NVL(p_table_name, 'PLAN_TABLE');
v_sql VARCHAR2(512);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the inputs for the order map...
-- ---------------------------------------
v_sql := 'SELECT id, parent_id
FROM ' || v_plan_table || '
WHERE plan_id = (SELECT MAX(plan_id)
FROM ' || v_plan_table || '
WHERE id = 0 %bind%)
ORDER BY id';
IF p_statement_id IS NULL THEN
v_sql := REPLACE(v_sql, '%bind%');
ELSE
v_sql := REPLACE(v_sql, '%bind%', 'AND statement_id = :bv_statement_id');
v_binds := ntt_order_map_binds(p_statement_id);
END IF;
-- Build the order map...
-- --------------------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY(
v_plan_table, p_statement_id, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display;
----------------------------------------------------------------------------
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql_id v$sql_plan.sql_id%TYPE;
v_child_no v$sql_plan.child_number%TYPE;
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Set a SQL_ID if default parameters passed...
-- --------------------------------------------
IF p_sql_id IS NULL THEN
SELECT prev_sql_id, prev_child_number
INTO v_sql_id, v_child_no
FROM v$session
WHERE sid = (SELECT m.sid FROM v$mystat m WHERE ROWNUM = 1)
AND username IS NOT NULL
AND prev_hash_value <> 0;
ELSE
v_sql_id := p_sql_id;
v_child_no := p_cursor_child_no;
END IF;
-- Prepare the inputs for the order mapping...
-- -------------------------------------------
v_sql := 'SELECT id, parent_id
FROM v$sql_plan
WHERE sql_id = :bv_sql_id
AND child_number = :bv_child_no';
v_binds := ntt_order_map_binds(v_sql_id, v_child_no);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
v_sql_id, v_child_no, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display_cursor;
&_awr_start
----------------------------------------------------------------------------
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the SQL for the order mapping...
-- ----------------------------------------
v_sql := 'SELECT id, parent_id
FROM dba_hist_sql_plan
WHERE sql_id = :bv_sql_id
AND plan_hash_value = :bv_plan_hash_value
AND dbid = :bv_dbid';
-- Determine all plans for the sql_id...
-- -------------------------------------
FOR r_awr IN (SELECT DISTINCT
sql_id
, plan_hash_value
, dbid
FROM dba_hist_sql_plan
WHERE sql_id = p_sql_id
AND plan_hash_value = NVL(p_plan_hash_value, plan_hash_value)
AND dbid = NVL(p_db_id, (SELECT dbid FROM v$database))
ORDER BY
plan_hash_value)
LOOP
-- Prepare the binds and build the order map...
-- --------------------------------------------
v_binds := ntt_order_map_binds(r_awr.sql_id,
r_awr.plan_hash_value,
r_awr.dbid);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
r_awr.sql_id, r_awr.plan_hash_value,
r_awr.dbid, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
END LOOP;
reset_state();
RETURN;
END display_awr;
&_awr_end
END xplan;
/
UNDEFINE _awr_start
UNDEFINE _awr_end
我们来进行实验
01:45:28 scott@orcl> select t1.empno,t1.ename,t2.dname from scott.emp t1,scott.dept t2 where t1.deptno=t2.deptno;
EMPNO ENAME DNAME
------------- ---------- --------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
7900 JAMES SALES
7698 BLAKE SALES
7654 MARTIN SALES
查找出当前sql的sqlid
01:50:26 sys@orcl> /
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
13abjsdxuzb24
select t1.empno,t1.ename,t2.dname from scott.emp t1,scott.dept t2 where t1.deptno=t2.deptno我们先用正常的dbms_xplan来查看sql的执行计划
01:49:22 scott@orcl> select * from table(dbms_xplan.display_cursor('13abjsdxuzb24',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13abjsdxuzb24, child number 0
-------------------------------------
select t1.empno,t1.ename,t2.dname from scott.emp t1,scott.dept t2 where
t1.deptno=t2.deptno
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T2@SEL$1
3 - SEL$1 / T2@SEL$1
5 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_MERGE(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."DEPTNO"="T2"."DEPTNO")
filter("T1"."DEPTNO"="T2"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T2"."DNAME"[VARCHAR2,14], "T1"."EMPNO"[NUMBER,22],
"T1"."ENAME"[VARCHAR2,10]
2 - "T2"."DEPTNO"[NUMBER,22], "T2"."DNAME"[VARCHAR2,14]
3 - "T2".ROWID[ROWID,10], "T2"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "T1"."DEPTNO"[NUMBER,22], "T1"."EMPNO"[NUMBER,22],
"T1"."ENAME"[VARCHAR2,10]
5 - "T1"."EMPNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10],
"T1"."DEPTNO"[NUMBER,22]
61 rows selected.现在我们再来看看使用xplan的包以后的执行效果
01:54:08 sys@orcl> select * from table(xplan.display_cursor('13abjsdxuzb24',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13abjsdxuzb24, child number 0
-------------------------------------
select t1.empno,t1.ename,t2.dname from scott.emp t1,scott.dept t2 where
t1.deptno=t2.deptno
Plan hash value: 844388907
------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | 6 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | 5 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | 1 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | 3 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
---------------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T2@SEL$1
3 - SEL$1 / T2@SEL$1
5 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_MERGE(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."DEPTNO"="T2"."DEPTNO")
filter("T1"."DEPTNO"="T2"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T2"."DNAME"[VARCHAR2,14], "T1"."EMPNO"[NUMBER,22],
"T1"."ENAME"[VARCHAR2,10]
2 - "T2"."DEPTNO"[NUMBER,22], "T2"."DNAME"[VARCHAR2,14]
3 - "T2".ROWID[ROWID,10], "T2"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "T1"."DEPTNO"[NUMBER,22], "T1"."EMPNO"[NUMBER,22],
"T1"."ENAME"[VARCHAR2,10]
5 - "T1"."EMPNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10],
"T1"."DEPTNO"[NUMBER,22]
61 rows selected.仔细比较就能发现区别了，通过xplan的包，他可以直接显示sql的执行顺序了，哪条先执行，哪条后执行，我们通过这个就能更好的看执行计划
503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
