oracle 执行计划（一）如何查看执行计划 - 数据库编程

sql优化是DBA的日常工作，sql的性能好坏极大的影响了数据库的性能。一条性能很差的sql很有可能导致数据库宕机。为了满足sql的量化分析和优化，oracl提供了大量的工具。如：explain、autotrace、sql trace、sql profile、sql access advisor、sql tuning advisor、ADDM、ASH、AWR等。
sql的性能好坏，通常情况下我们有几个量化指标：
时间消耗：elapsed time、cpu time
内存消耗：db block gets、consistent gets
I/O消耗：physical reads，physical writes
解析次数：parses、hard prases、soft prases

如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。

查看执行计划的方法：
一、explain plan：
（1）最古老的查看方法：
创建plan_table表：
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

分析sql语句执行计划
explain plan for select * from emp;

查看sql语句执行计划
@$ORACLE_HOME/rdbms/admin/utlxpls.sql或者@$ORACLE_HOME/rdbms/admin/utlxplp.sql

（2）dbms_xplan：
在10g之后，我通过dbms_xplan包查看执行计划更加方便，功能也更强大了。
dbms_xplan.display --查看v$sql中的执行计划
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT

dbms_xplan.display_cursor --显示任何加载到corsor cache的执行计划
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT

dbms_xplan.display_awr --显示awr中语句的执行计划
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT

分析sql语句执行计划

explain plan [SET STATEMENT_ID=''] for ...

查看sql语句执行计划
select * from table(dbms_xplan.display);
或者
select PLAN_TABLE_OUTPUT from table(dbms_xplan.display());

二、autotrace
autotrace的语法：
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

一般sys和system用户set autotrace不会出现问题，但是其他普通用户set autotrace的时候可能会遇到SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled的错误，解决办法参照：http://blog.csdn.net/dbaheng/article/details/16826719

SCOTT @oemrep>set autotrace traceonly
SCOTT @oemrep>select * from emp;

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1630 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

三、sql trace
产生sql trace的方法有很多种。根据我们的需求，可以执行不同的语句来产生我们所需要的sql trace。

（1）alter system set
当前会话的sql trace
alter session set sql_trac

oracle 执行计划（一） 如何查看执行计划(一)

oracle 执行计划（一）如何查看执行计划(一)