ql执行计划，研判Sql执行性能

mahan

一、获取sql执行计划

1、从库缓存中获取sql执行计划

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;


-- 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
5、通过sql monitor获取执行计划

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：支持分析函数的内部操作。