SQL性能的度量 - 通过explain解释执行计划

该工具需要访问一张特殊的表plan_table，该表用于存储执行计划，在Oracle 10g之前需要用脚本utlxplan.sql创建：

建表：

@?\rdbms\admin\utlxplan.sql

建同义词：

create public synonym plan_table for plan_table;

授权：

grant all on plan_table to public;

 

Oracle 10g之后不再需要创建表plan_table，而是增加了数据字典表plan_table$，然后基于plan_table$创建了公共同义词供用户使用。

 

explain基本语法：

explain plan [set statement_id = 'stmt_id'] for sql_statement;

 

explain指令的执行结果存储于表plan_table中，有几种方法获取执行计划的详细信息：

 

1、直接查询plan_table表

 

解释计划

explain plan for select count(*) from scott.emp;

查询结果

col id for 999

col operation for a50

col options for a20

col object_name for a20

select id,

       lpad(' ', 2 * depth) || operation || ' ' || options ||

       decode(id, 0, substr(optimizer, 1, 6) || ' Cost = ' || to_char(cost)) operation,

       options,

       object_name,

       position

  from plan_table

 where plan_id = (select max(plan_id) from plan_table)

 order by id;

 

  ID OPERATION                                          OPTIONS              OBJECT_NAME            POSITION

---- -------------------------------------------------- -------------------- -------------------- ----------

   0 SELECT STATEMENT ALL_RO Cost = 1                                                                   1

   1   SORT AGGREGATE                                   AGGREGATE                                       1

   2     INDEX FULL SCAN                                FULL SCAN            PK_EMP                     1

 

或者用以下查询，包含了执行计划树的level层次关系

col "Execute Plan" for a100

select id || '     ' || parent_id || '     ' || lpad(' ', 2 * level - 1) ||

       operation || ' ' || options || ' ' || object_name || ' (Cost=' || cost || ')' as "Execute Plan"

  from plan_table

 start with id = 0

connect by prior id = parent_id;

 

Execute Plan

----------------------------------------------------------------------------------------------------

0           SELECT STATEMENT   (Cost=1)

1     0        SORT AGGREGATE  (Cost=)

2     1          INDEX FULL SCAN PK_EMP (Cost=1)

 

2、通过程序包dbms_xplan获得执行计划

 

1）获得最近一次explain的执行计划

col "PLAN_TABLE_OUTPUT" for a100

select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2937609675

 

-------------------------------------------------------------------

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------

 

2）通过指定的语句ID来查询

select * from table(dbms_xplan.display('plan_table', 'stmt_id'));

 

3）通过SQL_ID和子游标来查询，该函数并不要求先做explain，显示的信息也较详细

select * from table(dbms_xplan.display_cursor('sql_id', 'child_number'));

 

可以通过在SQL语句中加入注释，方便的获取SQL_ID和CHILD_NUMBER信息，如以下SQL，先执行一次

select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2

  from cmes.c_material_t m

 where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or

       regexp_like('641234HNGA080001A', m.validaterule2, 'c'))

   and m.deleted_flag = '0'

   and rownum = 1;

 

查出它的ID：

select sql_id, child_number from v$sql where sql_text like '%12345%';

 

SQL_ID        CHILD_NUMBER

------------- ------------

9jk2r7a64s470            0

cc274s1r7ab6w            0

 

因为以上包含"12345"注释的语句被执行了两条，所以因取先执行的第一条为实际的ID。

 

查看执行计划：

col PLAN_TABLE_OUTPUT for a100;

select * from table(dbms_xplan.display_cursor('9jk2r7a64s470', 0));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID  9jk2r7a64s470, child number 0

-------------------------------------

select /*12345*/ m.material_id, m.material_no, m.part_no,

m.material_name, m.validaterule, m.validaterule2   from

cmes.c_material_t m  where (regexp_like('641234HNGA080001A',

m.validaterule, 'c') or        regexp_like('641234HNGA080001A',

m.validaterule2, 'c'))    and m.deleted_flag = '0'    and rownum = 1

 

Plan hash value: 1524529232

 

-----------------------------------------------------------------------------------

| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |              |       |       |     2 (100)|          |

|*  1 |  COUNT STOPKEY     |              |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| C_MATERIAL_T |     2 |   116 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(ROWNUM=1)

   2 - filter(("M"."DELETED_FLAG"='0' AND ( REGEXP_LIKE

              ('641234HNGA080001A',"M"."VALIDATERULE",'c',<not feasible>)

 

dbms_xplan程序包还有一个函数display_awr可以获取AWR报告中指定SQL_ID的执行计划

col PLAN_TABLE_OUTPUT for a100;

select * from table(dbms_xplan.display_awr('9jk2r7a64s470'));

 

该函数获取的执行计划来自dba_hist_sql_plan视图，通过历史数据记录，甚至一些被老化的SQL执行计划仍然可以被查询到。

 

3、通过脚本utlxpls.sql或utlxplp.sql获得执行计划，该方法实际是调用了方法2中的程序包

@?\rdbms\admin\utlxpls.sql

或者

@?\rdbms\admin\utlxplp.sql

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2937609675

 

-------------------------------------------------------------------

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------

 

看一下这两个脚本的内容

get ?\rdbms\admin\utlxpls.sql

......

 41* select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

 42

 

get ?\rdbms\admin\utlxplp.sql

......

 40* select * from table(dbms_xplan.display());

 41

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2150271/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2150271/