第3章 执行计划
3.1、获取执行计划常用方法
3.1.1、使用AUTOTRACE查看执行计划
set autot on : 该命令会运行统计信息；
set autot trace : 该命令会运行SQL，但不显示运行结果，会显示执行计划和统计信息；
set autot trace exp : 该命令查询语句不执行，DML语句会执行，只显示执行计划；
set autot trace stat : 该命令会运行SQL，只显示统计信息；
set autot off : 关闭AUTOTRACE ；
SCOTT@o11g>set autot Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
如果SQL语句中有自定义函数，recursive calls永远不会等于0，自定义函数被调用多少次，recursive calls就会显示为多少次。
db block gets 表示有多少个块发生变化，一般情况下，只有DML语句才会导致块发生变化，所以查询语句中db block gets一般为0。如果有延迟块清除，或者SQL语句中调用了返回CLOB的函数，db block gets也有可能会大于0，不要觉得奇怪。
consistent gets 表示逻辑读，单位是块。在进行SQL优化的时候，我们应该想方设法减少逻辑读个数。通常情况下逻辑读越小，性能也就越好。需要注意的是，逻辑读并不是衡量SQL执行快慢的唯一标准，需要结合I/O等其他综合因素共同判断。
怎么通过逻辑读判断一个SQL还存在较大优化空间呢？如果SQL的逻辑读远远大于SQL语句中所有表的段大小之和（假设所有表都走全表扫描，表关联方式为HASH JOIN），那么该SQL就存在较大优化空间。
physical reads 表示从磁盘读取了多少个数据块，如果表已经被缓存在buffer cache中，没有物理读，physical reads等于0。
redo size 表示产生了多少字节的重做日志，一般情况下只有DML语句才会产生redo，查询语句一般情况下不会产生redo，所以这里redo size为0。如果有延迟块清除，查询语句也会产生redo。
bytes sent via SQL*Net to client表示从数据库服务器发送了多少字节到客户端。
bytes received via SQL*Net from client表示从客户端发送了多少字节到服务端。
SQL*Net roundtrips to/from client 表示客户端与数据库服务端交互次数，我们可以通过设置arraysize减少交互次数。
sorts (memory)和sorts (disk) 分别表示内存排序和磁盘排序的次数。
rows processed 表示SQL一共返回多少行数据。我们在做SQL优化的时候最关心这部分数据，因为可以根据SQL返回的行数判断整个SQL应该是走HASH连接还是走嵌套循环。如果rows processed很大，一般走HASH连接；如果rows processed很小，一般走嵌套循环。
3.1.2、使用EXPLAIN PLAN FOR查看执行计划
explain plan for SQL语句;
select * from table(dbms_xplan.display);
explain plan for SQL语句;
select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));
3.1.3、查看带有A-TIME的执行计划
真实的执行计划，来自于v$sql_plan视图；
alter session set statistics_level = all ; 或者在SQL语句中添加hint: /*+ gather_plan_statistics */ select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))SCOTT@o11g>select /*+ gather_plan_statistics full(test) */ count(*) from test where owner='SYS' ; COUNT(*) ---------- 37792 SCOTT@o11g>select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------SQL_ID 5m5pdaru3wbp3, child number 0 ------------------------------------- select /*+ gather_plan_statistics full(test) */ count(*) from test where owner='SYS' Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows |A-Time | Buffers | Reads | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 1244 |1243 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1244 |1243 | |* 2 | TABLE ACCESS FULL| TEST | 1 | 2899 | 37792 |00:00:00.01 | 1244 |1243 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='SYS') 20 rows selected.Starts表示这个操作执行的次数；
E-Rows表示优化器估算的行数，就是普通执行计划中的Rows；
A-Rows表示真实的行数；
A-Time表示累加的总时间，与普通执行计划不同的是，普通执行计划中的Time是假的，而A-Time是真实的；
Buffers表示累加的逻辑读；
Reads表示累加的物理读；
3.1.4、查看正在执行的SQL的执行计划
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
3.2、定制执行计划
explain plan for select /*+ use_hash(a,dept) */ *
from scott.emp a, scott.dept
where a.deptno = dept.deptno
and a.sal > 3000;
定制一：
select case
when (filter_predicates is not null or
access_predicates is not null) then
'*'
else
' '
end || id as "Id",
lpad(' ', level) || operation || ' ' || options "Operation",
object_name "Name",
cardinality as "Rows",
filter_predicates "Filter",
access_predicates "Access"
from plan_table
start with id = 0
connect by prior id = parent_id;
定制二(增加表大小)：
select case
when (filter_predicates is not null or
access_predicates is not null) then
'*'
else
' '
end || id as "Id",
lpad(' ', level) || operation || ' ' || options "Operation",
object_name "Name",
cardinality as "Rows",
b.size_mb "Size_Mb",
filter_predicates "Filter",
access_predicates "Access"
from plan_table a,
(select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
from dba_segments
group by owner, segment_name) b
where a.object_owner = b.owner(+)
and a.object_name = b.segment_name(+)
start with id = 0
connect by prior id = parent_id;
定制三(增加表的大小、访问列/表的列总数)
select case
when access_predicates is not null or filter_predicates is not null then
'*' || id
else
' ' || id
end as "Id",
lpad(' ', level) || operation || ' ' || options "Operation",
object_name "Name",
cardinality "Rows",
b.size_mb "Mb",
case
when object_type like '%TABLE%' then
REGEXP_COUNT(a.projection, ']') || '/' || c.column_cnt
end as "Column",
access_predicates "Access",
filter_predicates "Filter",
case
when object_type like '%TABLE%' then
projection
end as "Projection"
from plan_table a,
(select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
from dba_segments
group by owner, segment_name) b,
(select owner, table_name, count(*) column_cnt
from dba_tab_cols
group by owner, table_name) c
where a.object_owner = b.owner(+)
and a.object_name = b.segment_name(+)
and a.object_owner = c.owner(+)
and a.object_name = c.table_name(+)
start with id = 0
connect by prior id = parent_id;
3.3、怎么通过查看执行计划建立索引
3.4、阅读执行计划
3.4.1、方法一(移动)
一、先从最开头一直连续往右看，直到看到最右边并行的地方；对于不并行的，靠右的先执行；如果见到并行的，就从上往下看，对于并列的部分，靠上的先执行。
二、光标移动
怎么快速找到执行计划的入口？
向右向下移动，直到找到没有儿子的Id，这个Id就是执行计划的入口；
怎么判断是那个表与哪个表进行关联的？
？？？
3.4.2、方法二(脚本)
# XPLAN
## 1.0 Introduction
This repository contains two versions of the XPLAN utility. XPLAN is a wrapper over DBMS_XPLAN functions that re-formats plan output to include parent operation ID and execution order columns. This makes plan interpretation easier for larger or more complex execution plans.
## 2.0 Versions
There are two versions provided.
### 2.1 Installed Package (xplan.package.sql)
This is a package of pipelined function wrappers over the DBMS_XPLAN reports (DISPLAY, DISPLAY_CURSOR, DISPLAY_AWR). It creates two types and the XPLAN package itself. See the description in the package header for more details and usage information.
### 2.2 Standalone Scripts (xplan.display.sql, xplan.display_cursor.sql, xplan.display_awr.sql)
These are standalone SQL scripts (for SQL*Plus) that simulate the XPLAN functionality but without having to create any database objects. Because they are free-standing SQL scripts, they are more portable, can be added to your SQLPath for instant availability and can be used in more restrictive environments. See the description in the script headers for more details and usage information. Note that a Tuning and Diagnostics Pack licence is required for the `xplan.display_awr.sql` script as it accesses AWR data.
## 3.0 Disclaimer
See http://www.oracle-developer.net/disclaimer.php
Adrian Billington
(c) www.oracle-developer.net
3.4.2.1、xplan.display.sql
-- ---------------------------------------------------------------------------------
--
-- Utility: XPLAN
--
-- Script: xplan.display.sql
--
-- Version: 1.2
--
-- Author: Adrian Billington
-- www.oracle-developer.net
-- (c) oracle-developer.net
--
-- Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
-- DBMS_XPLAN.DISPLAY pipelined function for an explained SQL statement.
--
-- The XPLAN utility has one purpose: to include the parent operation ID (PID)
-- and an execution order column (OID) in the plan output. This makes plan
-- interpretation easier for larger or more complex execution plans.
--
-- 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 | Pid | Ord | Operation | Name |
-- ------------------------------------------------------------
-- | 0 | | 6 | SELECT STATEMENT | |
-- | 1 | 0 | 5 | MERGE JOIN | |
-- | 2 | 1 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 2 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 1 | 4 | SORT JOIN | |
-- | 5 | 4 | 3 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------------------
--
-- Usage: @xplan.display.sql [plan_table] [statement_id] [plan_format]
--
-- Parameters: 1) plan_table - OPTIONAL (defaults to PLAN_TABLE)
-- 2) statement_id - OPTIONAL (defaults to NULL)
-- 3) plan_format - OPTIONAL (defaults to TYPICAL)
--
-- Examples: 1) Plan for last explained SQL statement
-- -------------------------------------
-- @xplan.display.sql
--
-- 2) Plan for a specific statement_id
-- --------------------------------
-- @xplan.display.sql "" "my_statement_id"
--
-- 3) Plan for last explained SQL statement using a non-standard plan table
-- -----------------------------------------------------------------
-- @xplan.display.sql "my_plan_table"
--
-- 4) Plan for last explained SQL statement with a non-default format
-- ---------------------------------------------------------------
-- @xplan.display.sql "" "" "basic +projection"
--
-- 5) Plan for a specific statement_id and non-default format
-- -------------------------------------------------------
-- @xplan.display.sql "" "my_statement_id" "advanced"
--
-- 6) Plan for last explained SQL statement with a non-default plan table and non-default format
-- ---------------------------------------------------------
-- @xplan.display.sql "my_plan_table" "my_statement_id" "advanced"
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: 1) Access to a plan table that corresponds to the Oracle version being used.
--
-- Notes: An XPLAN PL/SQL package is also available. This has wrappers for all of the
-- DBMS_XPLAN pipelined functions, but requires the creation of objects.
--
-- Credits: 1) James Padfield for the hierarchical query to order the plan operations.
--
-- Disclaimer: http://www.oracle-developer.net/disclaimer.php
--
-- ----------------------------------------------------------------------------------
set define on
define v_xp_version = 1.2
-- Initialise variables 1,2,3 in case they aren't supplied...
-- ----------------------------------------------------------
set termout off
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3
select null as "1"
, null as "2"
, null as "3"
from dual
where 1=2;
-- Set the plan table...
-- ---------------------
column plan_table new_value v_xp_plan_table
select nvl('&1', 'PLAN_TABLE') as plan_table
from dual;
-- Finally prepare the inputs to the main Xplan SQL...
-- ---------------------------------------------------
column plan_id new_value v_xp_plan_id
column stmt_id new_value v_xp_stmt_id
column format new_value v_xp_format
select nvl(max(plan_id), -1) as plan_id
, max(statement_id) keep (dense_rank first order by plan_id desc) as stmt_id
, nvl(max('&3'), 'typical') as format
from &v_xp_plan_table
where id = 0
and nvl(statement_id, '~') = coalesce('&2', statement_id, '~');
-- Main Xplan SQL...
-- -----------------
set termout on lines 200 pages 1000
col plan_table_output format a200
with sql_plan_data as (
select id, parent_id
from &v_xp_plan_table
where plan_id = &v_xp_plan_id
order by id
)
, hierarchy_data as (
select id, parent_id
from sql_plan_data
start with id = 0
connect by prior id = parent_id
order siblings by id desc
)
, ordered_hierarchy_data as (
select id
, parent_id as pid
, row_number() over (order by rownum desc) as oid
, max(id) over () as maxid
from hierarchy_data
)
, xplan_data as (
select /*+ ordered use_nl(o) */
rownum as r
, x.plan_table_output as plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, count(*) over () as rc
from table(dbms_xplan.display('&v_xp_plan_table','&v_xp_stmt_id','&v_xp_format')) x
left outer join
ordered_hierarchy_data o
on (o.id = case
when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|')
then to_number(regexp_substr(x.plan_table_output, '[0-9]+'))
end)
)
select plan_table_output
from xplan_data
model
dimension by (rownum as r)
measures (plan_table_output,
id,
maxid,
pid,
oid,
rc,
greatest(max(length(maxid)) over () + 3, 6) as csize,
cast(null as varchar2(128)) as inject)
rules sequential order (
inject[r] = case
when id[cv()+1] = 0
or id[cv()+3] = 0
or id[cv()-1] = maxid[cv()-1]
then rpad('-', csize[cv()]*2, '-')
when id[cv()+2] = 0
then '|' || lpad('Pid |', csize[cv()]) || lpad('Ord |', csize[cv()])
when id[cv()] is not null
then '|' || lpad(pid[cv()] || ' |', csize[cv()]) || lpad(oid[cv()] || ' |', csize[cv()])
end,
plan_table_output[r] = case
when inject[cv()] like '---%'
then inject[cv()] || plan_table_output[cv()]
when inject[cv()] is not null
then regexp_replace(plan_table_output[cv()], '\|', inject[cv()], 1, 2)
else plan_table_output[cv()]
end ||
case
when cv(r) = rc[cv()]
then chr(10) || chr(10) ||
'About' || chr(10) ||
'------' || chr(10) ||
' - XPlan v&v_xp_version by Adrian Billington (http://www.oracle-developer.net)'
end
)
order by r;
-- Teardown...
-- -----------
undefine v_xp_plan_table
undefine v_xp_plan_id
undefine v_xp_stmt_id
undefine v_xp_format
undefine v_xp_version
undefine 1
undefine 2
undefine 3
样例(SET STATEMENT_ID = ‘test’)
使用样例：(SET STATEMENT_ID = 'test')
SCOTT@o11g>EXPLAIN PLAN SET STATEMENT_ID = 'test' for select /*+use_hash(e,d) */ e.ename,e.job,d.dname from emp e,dept d where e.deptno =d.deptno and e.sal <
2000 ;
Explained.
SCOTT@o11g>@xplan.display.sql
old 3: from &v_xp_plan_table
new 3: from PLAN_TABLE
old 4: where plan_id = &v_xp_plan_id
new 4: where plan_id = 76
old 30: from table(dbms_xplan.display('&v_xp_plan_table','&v_xp_stmt_id','&v_xp_format')) x
new 30: from table(dbms_xplan.display('PLAN_TABLE','test','typical')) x
old 73: ' - XPlan v&v_xp_version by Adrian Billington (http://www.oracle-developer.net)'
new 73: ' - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)'
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
-------------------------------------------Plan hash value: 1123238657
-------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | | 4 | SELECT STATEMENT | | 4 | 136 | 6 (0)| 00:00:01 |
|* 1 | 0 | 3 | HASH JOIN | | 4 | 136 | 6 (0)| 00:00:01 |
|* 2 | 1 | 1 | TABLE ACCESS FULL| EMP | 4 | 84 | 3 (0)| 00:00:01 |
| 3 | 1 | 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."SAL"<2000)
About
------
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
16 rows selected.
3.4.2.2、xplan.display_cursor.sql(获取实际的执行计划)
-- --------------------------------------------------------------------------------------
--
-- Utility: XPLAN
--
-- Script: xplan.display_cursor.sql
--
-- Version: 1.2
--
-- Author: Adrian Billington
-- www.oracle-developer.net
-- (c) oracle-developer.net
--
-- Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
-- DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NO.
--
-- The XPLAN utility has one purpose: to include the parent operation ID (PID)
-- and an execution order column (OID) in the plan output. This makes plan
-- interpretation easier for larger or more complex execution plans.
--
-- 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 | Pid | Ord | Operation | Name |
-- ------------------------------------------------------------
-- | 0 | | 6 | SELECT STATEMENT | |
-- | 1 | 0 | 5 | MERGE JOIN | |
-- | 2 | 1 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 2 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 1 | 4 | SORT JOIN | |
-- | 5 | 4 | 3 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------------------
--
-- Usage: @xplan.display_cursor.sql <sql_id> [cursor_child_number] [format]
--
-- Parameters: 1) sql_id - OPTIONAL (defaults to last executed SQL_ID)
-- 2) sql_child_number - OPTIONAL (defaults to 0)
-- 3) plan_format - OPTIONAL (defaults to TYPICAL)
--
-- Examples: 1) Plan for last executed SQL (needs serveroutput off)
-- ---------------------------------------------------
-- @xplan.display_cursor.sql
--
-- 2) Plan for a SQL_ID with default child number
-- -------------------------------------------
-- @xplan.display_cursor.sql 9vfvgsk7mtkr4
--
-- 3) Plan for a SQL_ID with specific child number
-- --------------------------------------------
-- @xplan.display_cursor.sql 9vfvgsk7mtkr4 1
--
-- 4) Plan for a SQL_ID with default child number and non-default format
-- ------------------------------------------------------------------
-- @xplan.display_cursor.sql 9vfvgsk7mtkr4 "" "basic +projection"
--
-- 5) Plan for a SQL_ID, specific child number and non-default format
-- ---------------------------------------------------------------
-- @xplan.display_cursor.sql 9vfvgsk7mtkr4 1 "advanced"
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: 1) Access to GV$SESSION, GV$SQL_PLAN
--
-- Notes: An XPLAN PL/SQL package is also available. This has wrappers for all of the
-- DBMS_XPLAN pipelined functions, but requires the creation of objects.
--
-- Credits: 1) James Padfield for the hierarchical query to order the plan operations.
-- 2) Paul Vale for the suggestion to turn XPLAN.DISPLAY_CURSOR into a standalone
-- SQL script, including a prototype.
--
-- Disclaimer: http://www.oracle-developer.net/disclaimer.php
--
-- ----------------------------------------------------------------------------------------------
set define on
define v_xc_version = 1.2
-- Fetch the previous SQL details in case they're not supplied...
-- --------------------------------------------------------------
set termout off
column prev_sql_id new_value v_xc_prev_sql_id
column prev_child_number new_value v_xc_prev_child_no
select prev_sql_id
, prev_child_number
from gv$session
where inst_id = sys_context('userenv','instance')
and sid = sys_context('userenv','sid')
and username is not null
and prev_hash_value <> 0;
-- Initialise variables 1,2,3 in case they aren't supplied...
-- ----------------------------------------------------------
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3
select null as "1"
, null as "2"
, null as "3"
from dual
where 1=2;
-- Finally prepare the inputs to the main Xplan SQL...
-- ---------------------------------------------------
column sql_id new_value v_xc_sql_id
column child_no new_value v_xc_child_no
column format new_value v_xc_format
select nvl('&1', '&v_xc_prev_sql_id') as sql_id
, to_number(nvl('&2', '&v_xc_prev_child_no')) as child_no
, nvl('&3', 'typical') as format
from dual;
-- Main Xplan SQL...
-- -----------------
set termout on lines 200 pages 1000
col plan_table_output format a200
with sql_plan_data as (
select id, parent_id
from gv$sql_plan
where inst_id = sys_context('userenv','instance')
and sql_id = '&v_xc_sql_id'
and child_number = to_number('&v_xc_child_no')
)
, hierarchy_data as (
select id, parent_id
from sql_plan_data
start with id = 0
connect by prior id = parent_id
order siblings by id desc
)
, ordered_hierarchy_data as (
select id
, parent_id as pid
, row_number() over (order by rownum desc) as oid
, max(id) over () as maxid
from hierarchy_data
)
, xplan_data as (
select /*+ ordered use_nl(o) */
rownum as r
, x.plan_table_output as plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, count(*) over () as rc
from table(dbms_xplan.display_cursor('&v_xc_sql_id',to_number('&v_xc_child_no'),'&v_xc_format')) x
left outer join
ordered_hierarchy_data o
on (o.id = case
when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|')
then to_number(regexp_substr(x.plan_table_output, '[0-9]+'))
end)
)
select plan_table_output
from xplan_data
model
dimension by (rownum as r)
measures (plan_table_output,
id,
maxid,
pid,
oid,
greatest(max(length(maxid)) over () + 3, 6) as csize,
cast(null as varchar2(128)) as inject,
rc)
rules sequential order (
inject[r] = case
when id[cv()+1] = 0
or id[cv()+3] = 0
or id[cv()-1] = maxid[cv()-1]
then rpad('-', csize[cv()]*2, '-')
when id[cv()+2] = 0
then '|' || lpad('Pid |', csize[cv()]) || lpad('Ord |', csize[cv()])
when id[cv()] is not null
then '|' || lpad(pid[cv()] || ' |', csize[cv()]) || lpad(oid[cv()] || ' |', csize[cv()])
end,
plan_table_output[r] = case
when inject[cv()] like '---%'
then inject[cv()] || plan_table_output[cv()]
when inject[cv()] is not null
then regexp_replace(plan_table_output[cv()], '\|', inject[cv()], 1, 2)
else plan_table_output[cv()]
end ||
case
when cv(r) = rc[cv()]
then chr(10) ||
'About' || chr(10) ||
'------' || chr(10) ||
' - XPlan v&v_xc_version by Adrian Billington (http://www.oracle-developer.net)'
end
)
order by r;
-- Teardown...
-- -----------
undefine v_xc_sql_id
undefine v_xc_child_no
undefine v_xc_format
undefine v_xc_prev_sql_id
undefine v_xc_prev_child_no
undefine v_xc_version
undefine 1
undefine 2
undefine 3
3.4.2.3、xplan.display_awr.sql
-- ----------------------------------------------------------------------------------------------
--
-- Utility: XPLAN
--
-- Script: xplan.display_awr.sql
--
-- Version: 1.2
--
-- Author: Adrian Billington
-- www.oracle-developer.net
-- (c) oracle-developer.net
--
-- Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
-- DBMS_XPLAN.DISPLAY_AWR pipelined function for a given SQL_ID and optional
-- plan hash value.
--
-- The XPLAN utility has one purpose: to include the parent operation ID (PID)
-- and an execution order column (OID) in the plan output. This makes plan
-- interpretation easier for larger or more complex execution plans.
--
-- 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 | Pid | Ord | Operation | Name |
-- ------------------------------------------------------------
-- | 0 | | 6 | SELECT STATEMENT | |
-- | 1 | 0 | 5 | MERGE JOIN | |
-- | 2 | 1 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 2 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 1 | 4 | SORT JOIN | |
-- | 5 | 4 | 3 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------------------
--
-- Usage: @xplan.display_awr.sql <sql_id> [plan_hash_value] [plan_format]
--
-- Parameters: 1) sql_id - MANDATORY
-- 2) plan_hash_value - OPTIONAL (defaults to all available for the SQL ID)
-- 3) plan_format - OPTIONAL (defaults to TYPICAL)
--
-- Examples: 1) All AWR plans for a SQL_ID
-- --------------------------
-- @xplan.display_awr.sql 9vfvgsk7mtkr4
--
-- 2) All AWR plans for a SQL_ID with a non-default format
-- ----------------------------------------------------
-- @xplan.display_awr.sql 9vfvgsk7mtkr4 "" "basic +projection"
--
-- 3) AWR plan for a SQL_ID and specific PLAN_HASH_VALUE
-- --------------------------------------------------
-- @xplan.display_awr.sql 9vfvgsk7mtkr4 63301235
--
-- 4) AWR plan for a SQL_ID, specific PLAN_HASH_VALUE and non-default format
-- ----------------------------------------------------------------------
-- @xplan.display_awr.sql 9vfvgsk7mtkr4 63301235 "advanced"
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: *** IMPORTANT: PLEASE READ ***
--
-- 1) Oracle license implications
-- ---------------------------
-- The AWR functionality of XPLAN accesses a DBA_HIST% AWR view which means
-- that it requires an Oracle Tuning and Diagnostic Pack license. 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.
--
-- 2) Access to the DBA_HIST_SQL_PLAN AWR view.
--
-- Notes: An XPLAN PL/SQL package is also available. This has wrappers for all of the
-- DBMS_XPLAN pipelined functions, but requires the creation of objects.
--
-- Credits: 1) James Padfield for the hierarchical query to order the plan operations.
--
-- Disclaimer: http://www.oracle-developer.net/disclaimer.php
--
-- ----------------------------------------------------------------------------------------------
set define on
define v_xa_version = 1.2
-- Initialise variables 1,2,3 in case they aren't supplied...
-- ----------------------------------------------------------
set termout off
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3
select null as "1"
, null as "2"
, null as "3"
from dual
where 1=2;
-- Define the parameters...
-- ------------------------
column dbid new_value v_xa_dbid
column sql_id new_value v_xa_sql_id
column format new_value v_xa_format
column plan_hash_value new_value v_xa_plan_hash_value
select dbid
, '&1' as sql_id
, nvl('&2', 'NULL') as plan_hash_value
, nvl('&3', 'TYPICAL') as format
from gv$database
where inst_id = sys_context('userenv','instance');
-- Main Xplan SQL...
-- -----------------
set termout on lines 200 pages 1000
col plan_table_output format a200
prompt _ _____________________________________________________________________
prompt _
prompt _ XPlan v&v_xa_version by Adrian Billington (http://www.oracle-developer.net)
prompt _
prompt _
prompt _ *** IMPORTANT: PLEASE READ ***
prompt _
prompt _ A licence for the Oracle Tuning and Diagnostics Pack is needed to
prompt _ use this utility. Continue at your own risk: the author accepts
prompt _ no responsibility for any use of this functionality in an unlicensed
prompt _ database.
prompt _
prompt _ To cancel: press Ctrl-C
prompt _ To continue: press Enter
prompt _ _____________________________________________________________________
prompt
pause
with sql_plan_data as (
select id, parent_id, plan_hash_value
from dba_hist_sql_plan
where sql_id = '&v_xa_sql_id'
and plan_hash_value = nvl(&v_xa_plan_hash_value, plan_hash_value)
and dbid = &v_xa_dbid
)
, hierarchy_data as (
select id, parent_id, plan_hash_value
from sql_plan_data
start with id = 0
connect by prior id = parent_id
and prior plan_hash_value = plan_hash_value
order siblings by id desc
)
, ordered_hierarchy_data as (
select id
, parent_id as pid
, plan_hash_value as phv
, row_number() over (partition by plan_hash_value order by rownum desc) as oid
, max(id) over (partition by plan_hash_value) as maxid
from hierarchy_data
)
, xplan_data as (
select /*+ ordered use_nl(o) */
rownum as r
, x.plan_table_output as plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, p.phv
, count(*) over () as rc
from (
select distinct phv
from ordered_hierarchy_data
) p
cross join
table(dbms_xplan.display_awr('&v_xa_sql_id',p.phv,&v_xa_dbid,'&v_xa_format')) x
left outer join
ordered_hierarchy_data o
on ( o.phv = p.phv
and o.id = case
when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|')
then to_number(regexp_substr(x.plan_table_output, '[0-9]+'))
end)
)
select plan_table_output
from xplan_data
model
dimension by (phv, rownum as r)
measures (plan_table_output,
id,
maxid,
pid,
oid,
greatest(max(length(maxid)) over () + 3, 6) as csize,
cast(null as varchar2(128)) as inject,
rc)
rules sequential order (
inject[phv,r] = case
when id[cv(),cv()+1] = 0
or id[cv(),cv()+3] = 0
or id[cv(),cv()-1] = maxid[cv(),cv()-1]
then rpad('-', csize[cv(),cv()]*2, '-')
when id[cv(),cv()+2] = 0
then '|' || lpad('Pid |', csize[cv(),cv()]) || lpad('Ord |', csize[cv(),cv()])
when id[cv(),cv()] is not null
then '|' || lpad(pid[cv(),cv()] || ' |', csize[cv(),cv()]) || lpad(oid[cv(),cv()] || ' |', csize[cv(),cv()])
end,
plan_table_output[phv,r] = case
when inject[cv(),cv()] like '---%'
then inject[cv(),cv()] || plan_table_output[cv(),cv()]
when inject[cv(),cv()] is not null
then regexp_replace(plan_table_output[cv(),cv()], '\|', inject[cv(),cv()], 1, 2)
else plan_table_output[cv(),cv()]
end ||
case
when cv(r) = rc[cv(),cv()]
then chr(10) ||
'About' || chr(10) ||
'------' || chr(10) ||
' - XPlan v&v_xa_version by Adrian Billington (http://www.oracle-developer.net)'
end
)
order by r;
-- Teardown...
-- -----------
undefine v_xa_sql_id
undefine v_xa_plan_hash_value
undefine v_xa_dbid
undefine v_xa_format
undefine v_xa_version
undefine 1
undefine 2
undefine 3
3.4.2.4、xplan.package.sql(安装版本)
-- ----------------------------------------------------------------------------------------------
--
-- Script: xplan.package.sql
--
-- Version: 1.2
--
-- Author: Adrian Billington
-- www.oracle-developer.net
--
-- 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 the parent operation ID
-- and "order" columns in the plan output. This makes plan interpretation easier
-- for larger or more complex execution plans. 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 | PID | Order | Operation | Name |
-- --------------------------------------------------------------
-- | 0 | | 6 | SELECT STATEMENT | |
-- | 1 | 0 | 5 | MERGE JOIN | |
-- | 2 | 1 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 2 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 1 | 4 | SORT JOIN | |
-- | 5 | 4 | 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 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 below 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
-- Or:
-- SELECT_CATALOG_ROLE
--
-- 3) CREATE TYPE, CREATE PROCEDURE
--
-- Note that the SQL statements that access V$ views are all dynamic and the
-- package uses invoker rights. This means that the package will compile in
-- constrained developer environments where explicit privileges on V$ views
-- are not granted and access to the views is only available via roles such as
-- SELECT_CATALOG_ROLE.
--
-- 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;
--
-- Credits: James Padfield for the hierarchical query to order the plan operations.
--
-- Disclaimer: http://www.oracle-developer.net/disclaimer.php
--
-- ----------------------------------------------------------------------------------------------
--
-- 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 AUTHID CURRENT_USER 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_map_binds IS TABLE OF VARCHAR2(100);
TYPE rt_map IS RECORD
( ord PLS_INTEGER
, pid PLS_INTEGER );
TYPE aat_map IS TABLE OF rt_map
INDEX BY PLS_INTEGER;
g_map aat_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;
----------------------------------------------------------------------------
FUNCTION info RETURN xplan_ot IS
BEGIN
RETURN xplan_ot(CHR(10) ||
'About' || CHR(10) ||
'-----' || CHR(10) ||
' - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)' );
END info;
----------------------------------------------------------------------------
PROCEDURE build_map( p_sql IN VARCHAR2,
p_binds IN ntt_map_binds ) IS
TYPE rt_id_data IS RECORD
( id PLS_INTEGER
, pid 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
, parent_id
FROM sql_plan_data
START WITH id = 0
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id DESC
)
SELECT id
, parent_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 IDs and order data...
-- -------------------------------
FETCH v_cursor BULK COLLECT INTO aa_ids;
CLOSE v_cursor;
-- Populate the map array...
-- -------------------------
FOR i IN 1 .. aa_ids.COUNT LOOP
g_map(aa_ids(i).id).ord := aa_ids(i).ord;
g_map(aa_ids(i).id).pid := aa_ids(i).pid;
END LOOP;
-- Use the map to determine padding needed to slot in our columns...
-- -----------------------------------------------------------------
IF g_map.COUNT > 0 THEN
g_len := LEAST(LENGTH(g_map.LAST) + 13, 14);
g_pad := LPAD('-', g_len, '-');
END IF;
END build_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 new columns for the
-- the parent ID and operation order that we mapped earlier.
-- The plan output itself will be bound by dashed lines.
-- We need to add in additional dashes, column headings
-- and column values...
-- -------------------------------------------------------------
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-12, 2)) || 'PID ' ||
RPAD('|', GREATEST(g_len-13, 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(NVL(TO_CHAR(g_map(v_id).pid),' '), GREATEST(g_len-12, 4))
|| ' |' || LPAD(g_map(v_id).ord, GREATEST(g_len-13, 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_map_binds := ntt_map_binds();
BEGIN
reset_state();
-- Prepare the inputs for the parent ID and 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_map_binds(p_statement_id);
END IF;
-- Build the map...
-- --------------------------------------------------
build_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
PIPE ROW (info);
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;
PIPE ROW (info);
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 VARCHAR2(30);
v_child_no INTEGER;
v_sql VARCHAR2(256);
v_binds ntt_map_binds := ntt_map_binds();
BEGIN
reset_state();
-- Set a SQL_ID if default parameters passed...
-- --------------------------------------------
IF p_sql_id IS NULL THEN
EXECUTE IMMEDIATE 'SELECT prev_sql_id, prev_child_number
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'
INTO v_sql_id, v_child_no;
ELSE
v_sql_id := p_sql_id;
v_child_no := NVL(p_cursor_child_no,0);
END IF;
-- Prepare the inputs for the parent ID and order map...
-- -----------------------------------------------------
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_map_binds(v_sql_id, v_child_no);
-- Build the plan map from the SQL...
-- ----------------------------------------
build_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;
PIPE ROW (info);
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
TYPE rt_awr IS RECORD
( sql_id VARCHAR2(30)
, plan_hash_value INTEGER
, dbid INTEGER );
TYPE aat_awr IS TABLE OF rt_awr
INDEX BY PLS_INTEGER;
aa_awr aat_awr;
v_sql VARCHAR2(256);
v_binds ntt_map_binds := ntt_map_binds();
BEGIN
reset_state();
-- Prepare the inputs for the parent ID and order map...
-- -----------------------------------------------------
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...
-- -------------------------------------
EXECUTE IMMEDIATE 'SELECT DISTINCT
sql_id
, plan_hash_value
, dbid
FROM dba_hist_sql_plan
WHERE sql_id = :bv_sql_id
AND plan_hash_value = NVL(:bv_plan_hash_value, plan_hash_value)
AND dbid = NVL(:bv_db_id, (SELECT dbid FROM v$database))
ORDER BY
plan_hash_value'
BULK COLLECT INTO aa_awr
USING p_sql_id, p_plan_hash_value, p_db_id;
FOR i IN 1 .. aa_awr.COUNT LOOP
-- Prepare the binds and build the order map...
-- --------------------------------------------
v_binds := ntt_map_binds(aa_awr(i).sql_id,
aa_awr(i).plan_hash_value,
aa_awr(i).dbid);
-- Build the plan map from the SQL...
-- ----------------------------------
build_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(
aa_awr(i).sql_id, aa_awr(i).plan_hash_value,
aa_awr(i).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;
PIPE ROW (info);
reset_state();
RETURN;
END display_awr;
&_awr_end
END xplan;
/
UNDEFINE _awr_start
UNDEFINE _awr_end
样例
shell> sqlplus / as sysdba
SYS@o11g>@xplan.package.sql;
SYS@o11g>grant execute ,debug on xplan to scott ;
Grant succeeded.
SCOTT@o11g>desc sys.xplan
FUNCTION DISPLAY RETURNS XPLAN_NTT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TABLE_NAME VARCHAR2 IN DEFAULT
P_STATEMENT_ID VARCHAR2 IN DEFAULT
P_FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS XPLAN_NTT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SQL_ID VARCHAR2 IN DEFAULT
P_CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
P_FORMAT VARCHAR2 IN DEFAULT
SCOTT@o11g>explain plan for select /*+use_hash(e,d) */ e.ename,e.job,d.dname from emp e,dept d where e.deptno =d.deptno and e.sal < 2000 ;
Explained.
SCOTT@o11g>select * from table(sys.xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
About
-----
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
Plan hash value: 1123238657
-----------------------------------------------------------------------------------------
| Id | PID | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | | 4 | SELECT STATEMENT | | 4 | 136 | 6 (0)| 00:00:01 |
|* 1 | 0 | 3 | HASH JOIN | | 4 | 136 | 6 (0)| 00:00:01 |
|* 2 | 1 | 1 | TABLE ACCESS FULL| EMP | 4 | 84 | 3 (0)| 00:00:01 |
| 3 | 1 | 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."SAL"<2000)
About
-----
- XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)
18 rows selected.
SCOTT@o11g>explain plan for select count(*) from t_fullscan_cost ; Explained. SCOTT@o11g>select cpu_cost from plan_table where rownum<=1 ; CPU_COST ---------- 7121590
计算全表扫描成本：
SCOTT@o11g>l 1 select (0 * 12 + 1000 / 16 * 42 / 12 + 7121590 / (3074.07407 * 1000) / 12) cost 2* from dual SCOTT@o11g>/ COST ---------- 218.943055 SYS@o11g>l 1 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 2 FROM x$ksppi x, x$ksppcv y 3 WHERE x.inst_id = USERENV('Instance') 4 AND y.inst_id = USERENV('Instance') 5 AND x.indx = y.indx 6* AND x.ksppinm LIKE '%_table_scan_cost_plus_one%' SYS@o11g>/ NAME VALUE DESCRIB ------------------------- ----- ---------------- _table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one
最后修改时间：2019-10-28 12:44:05
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
