utlxplan

最新推荐文章于 2023-05-22 12:31:58 发布

congwu8076

最新推荐文章于 2023-05-22 12:31:58 发布

阅读量234

点赞数

这几天帮一个朋友优化一个后台JOB，发现需要使用UTLXPLAN。自己测试使用，记录如下。

utlxplan是Oracle提供的查看SQL语句执行计划的工具，相对于AUTOTRACE使用UTLXPLAN不需要真实执行完该SQL语句，对于长查询的语句选择使用UTLXPLAN尽快获得执行来分析，使

用UTLXPLAN是基于数据库收集的统计数据，所以此时如果想获得更准确地执行计划，就需要统计数据的精确了，这点要注意。
下面是使用UTLXPLAN的步骤。
1、创建PLAN_TABLE，存储执行计划。

SQL> connect /as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> grant all on sys.plan_table to public;

Grant succeeded.

在11G中plan_table已经创建好了，同时创建了同义词同义词。
SQL> select synonym_name,table_name from dba_synonyms
where synonym_name='PLAN_TABLE';

     SYNONYM_NAME                   TABLE_NAME
     ------------------------------ ------------------
     PLAN_TABLE                     PLAN_TABLE$

使用UTLXPLAN。
SQL> CONNECT scott/oracle
Connected.
SQL>
SQL> explain plan for
2 select *
3 from emp e,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3625962092

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | | | | |

| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

从执行计划可以看出，访问EMP表使用了全表扫描，但是有一个明显的过滤条件filter("E"."ENAME"='SMITH')，所以在优化该语句时可以考虑在该列创建索引（小表有可能走全表

扫描）。

如果有多个用户执行相同的SQL语句，但是二者的执行计划不同，此时可以设置STATEMENT_ID标示该语句。如下所示。

SQL> explain plan set statement_id='TSH' for
2 select *
3 from emp e ,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';

Explained.

SQL> set line 120
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','BASIC'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3625962092

------------------------------------------------
| Id | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 | NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | EMP     |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
12 rows selected.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','TYPICAL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 | NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.
这里表DISPLAY函数接受三个参数。
TABLE_NAME:'PLAN_TABLE'
STATEMENT_ID:默认是NULL，查询最近的一个SQL语句，或者指定一个ID。
FORMAT:控制显示的详细程度,TYPICAL,BASIC,ALL,SERIAL,(advanced 没有记录在文档）。以下是ADVANCED参数的查询结果。

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH','ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3625962092

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Outline Data

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
/*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1" "E"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
       "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

61 rows selected.

我们再执行一次查询。此时我们在表EMP上创建一个索引。
SQL> create index idx_emp_ename on emp(ename);

Index created.

SQL> explain plan set statement_id='TSH1' for
2 select *
3 from emp e,dept d
4 where e.deptno=d.deptno
5 and e.ename='SMITH';

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','TSH1','TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2977454843

-----------------------------------------------------------------------------------------------
| Id | Operation                     | Name          | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    58 |     3   (0)| 00:00:01 |
|   1 | NESTED LOOPS                 |               |       |       |            |          |
|   2 |   NESTED LOOPS                |               |     1 |    58 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    38 |     2   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | IDX_EMP_ENAME |     1 |       |     1   (0)| 00:00:01 |
|* 5 |    INDEX UNIQUE SCAN          | PK_DEPT       |     1 |       |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("E"."ENAME"='SMITH')
5 - access("E"."DEPTNO"="D"."DEPTNO")

19 rows selected.
我们指定查询STATEMENT_ID='TSH1'在PLAN_TABLE中的执行计划。可以看出，此时表EMP的访问使用了索引。COST下降。

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

转载于:http://blog.itpub.net/26155341/viewspace-763494/