select * from table(dbms_xplan.display_cursor('21pfh9hu7z136',null,null));
2、从awr中获取sql执行计划
select * from table(dbms_xplan.display_awr('68qxrf4dn4ckx'));
3、获取真实的执行计划
alter session set statistics_level=all
-- 或者执行sql中加入hint,/*+ gather_plan_statistics*/
select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last peeked_binds');
4、Adrian编写的获取执行计划脚本 ,可显示出执行计划的执行顺序,脚本如下:
-- ----------------------------------------------------------------------------------------------
--
-- 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;
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => 'g9rtj389t0g66',
TYPE => 'TEXT',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;
二、执行计划中的专业术语
(一)、访问表方式
1、table access full:全表扫描。它会访问表中的每一条记录(读取高水位线以内的每一个数据块)。
2、table access by user rowid:输入源rowid来自于用户指定。
3、table access by index rowid:输入源rowid来自于索引。
4、table access by global index rowid:全局索引获取rowid,然后再回表。
5、table access by local index rowid:分区索引获取rowid,然后再回表。
6、table access cluster:通过索引簇的键来访问索表。
7、external table access:访问外部表。
8、result cache:结果集可能来自于缓存。
9、mat_view rewrite access:物化视图。
(二)、访问索引路径
1、index unique scan:只返回一条rowid的索引扫描,或者unique索引的等值扫描。
2、index range scan:返回多条rowid的索引扫描。
3、index full scan:顺序扫描整个索引。
4、index fast full scan:多块读方式扫描整个索引。
5、index skip scan:多应用于组合索引中,引导键值为空的情况下索引扫描。
6、and-equal:合并来自于一个或多个索引的结果集。
7、domain index:应用域索引。
(三)、访问BIT-MAP索引路径
1、bitmap conversion:将位转换为rowid或相反。
2、bitmap index:从位图中取一个值或一个范围。
3、bitmap merge
4、bitmap minus:
5、bitmap or:
(四)、与表连接相关的连接方式
1、merge join:排序合并连接。
2、nested loops:嵌套循环连接。
3、hash join:哈希连接。
4、cartesian:笛卡尔积连接。
5、connect by:层次查询索引,多来自于start with子句。
6、outer:外链接。
1)merge join outer:
2)nested loops outer:
3)hash join outer:
7、anti:反连接。
1)merge join anti:
2)nested loops anti:
3)hash join anti:
8、semi:半连接。
1)merge join semi:
2)nested loops semi:
3)hash join semi:
(五)、与集合相关的执行计划
1、union-all:
2、union(union-all,sort unique):
3、concatenation:
4、intersection:
5、minus:
(六)、与分区相关的索引
1、partition single:访问单个分区。
2、partition iterator:访问多个分区。
3、partition all:访问所有分区。
4、partition inlist:基于in列表中的值来访问分区。
(七)、与sort相关的执行计划
1、sort unique:排序、去重。
2、sort join:为merge join的第一步,排序操作,一般与merge join联合使用。
3、sort aggregate:当分组好的数据上使用分组函数时。
4、sort order by:单纯的排序
5、sort group by:排序并分组
6、buffer sort:对临时结果进行一次内存排序。
(八)、其他执行计划
1、view:
2、count:
3、stopkey:目标sql中存在rownum<10这种情况。
4、hash group by:
5、inlist iterator:
6、filter:过滤,相当于处理过的排序合并连接。
7、remote:与dblink相关的执行计划。
8、for update:
9、sequence:使用了oracle序列。
10、collection iterator:使用了表函数提取记录。
11、fast dual:访问dual表。
12、first row:获取查询的第一条记录。
13、load as select:使用select进行直接路径insert操作,通常加/+append/提示。
14、fixed table:访问固定的(X/V/V)表。
15、fixed index:访问固定的索引。
16、window buffer:支持分析函数的内部操作。