http://lzfhope.blog.163.com/blog/static/63639922008271139360/?suggestedreading&wumii
整理ORACLE中查看执行计划plan
2008-03-07 11:39:36| 分类： ORACLE管理 | 标签：sql*plus 执行计划 oracle 性能 |字号大中小 订阅
能执行并看查看sql的执行计划应该是PL/SQL开发人员的基本功。
必须声明，以下是基于oracle10g的，对8i及其更早的版本不再讨论。
一：执行形式
通常我们在sql*plus中就可以执行了。在形式上，如果按照输出结果方式主要有两个不同，按照执行方式也有两个不同。
至于第三方，目前暂时不讨论，其它的，它们也是通过调用oracle函数实现的。
至于如何使用dbms_xplan包裹，不在此详述，我自己一般也不用。
1)执行方式1 －－ set autotrace traceonly..
sql>set serveroutput on
sql>set autotrace traceonly
完整格式是：
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
然后运行即可直接的查看结果，如例子（已经手工删除一些空白）：
SQL> select * from tab;
已选择442行。
执行计划
----------------------------------------------------------
Plan hash value: 457676135
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1066 | 90610 | 204 (4)| 00:00:03 |
| 1 | NESTED LOOPS OUTER | | 1066 | 90610 | 204 (4)| 00:00:03 |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1066 | 83148 | 152 (5)| 00:00:02 |
| 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("O"."TYPE#"<=5 AND "O"."OWNER#"=USERENV('SCHEMAID') AND
"O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
4 - access("O"."OBJ#"="T"."OBJ#"(+))
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1684 consistent gets
0 physical reads
0 redo size
11810 bytes sent via SQL*Net to client
704 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
442 rows processed
（注：墨绿色部分是EXPLAIN PLAN输出所不具有的)
该命令参考见<<sql * plus user's guide and reference release 10.2>> B14357-01.
如果想不起来，可以用sql> help set来查看可用SET命令。
2)EXPLAIN PLAIN FOR
explain plan 的标准语法如下：
explan plan [set statement_id= ] [into table] for statement ;
红色字体部分是可以不要的。
使用select * from table(dbms_xplan.display())来查看结果.
display 函数有有四个参数，但都可以不要，这样默认是上次执行计划的典型显示。
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
详细的太多，不能一一介绍。总之，作为一般开发，可以把如下执行:
select * from table(dbms_xplan.display(NULL,NULL,'all')),或者更多是使用默认的 select * from table(dbms_xplan.display);
直接执行EXPLAIN PLAN FOR SELECT * FROM TAB;
SQL> set autotrace off;
SQL> explain plan for select * from tab;
已解释。
SQL> select * from table(dbms_xplan.display);
具体结果略。
该命令必须参考<<Oracle Database SQL Reference 10g Release 2 (10.2) >> B14200-02
其它的可以参考视图：V$SQL_WORKAREA，V$SQL_PLAN，V$SQL_PLAN_STATISTICS，V$SQL_PLAN_STATISTICS_ALL。
可以参考的其它书籍是： Oracle Database Performance Tuning Guide （有关explain输出),Oracle Database Reference(前面提到的动态性能视图,那几个v$开头的）.
3)两种方式的比较
a) 前面一种方式更加简单，而且只有一次设置，次次有效（SQL*PLUS环境下),输出的结果也更详细(相对于display 的典型输出)。
b)后面一种方式稍微麻烦一些，需要为每个独立的SQL执行，但优点是输出的结果可以存储到表格中，因为
dbms_xplan.display是一个管道表函数，输出的每一行都是varchar2类型。
它们的共同点在于，都需要用到表格plan_table ,有关plan_Table的脚本执行脚本ORACLE_HOME\RDBMS\ADMIN\UTLXPLAN.SQL
就我个人而言，还是更喜欢用set的方式--比较方便，不需要每次都去查询结果。
二：可用的参考书籍
1)sql * plus user's guide and reference release 10.2
2)Oracle Database SQL Reference 10g Release 2 (10.2)
3)Oracle Database
http://lzfhope.blog.163.com/blog/static/63639922007731104819232/?suggestedreading&wumii
Oracle sqlplus中autotrace错误SP2-0611
2007-08-31 10:48:19| 分类： sql解析与技巧 | 标签： |字号大中小 订阅
经过验证,我今日遇到问题的确可以用这个方法来解决掉.
但是是否需要用sys这个用户还值得商榷.其次就是为什么 plustrace 会消失还没有正确的解析.
Grant plustrace to X (X是某个用户)是一定要运行的.
下文摘录于http://h1yn.itpub.net/post/2084/245634
autotrace是一项 SQL*Plus 功能，自动跟踪为 SQL 语句生成一个执行计划并且提供与该语句的处理有关的统计。
SQL*Plus AUTOTRACE 可以用来替代 SQL Trace 使用，AUTOTRACE 的好处是您不必设置跟踪文件的格式，并且它将自动为 SQL 语句显示执行计划。然而，AUTOTRACE 分析和执行语句；而EXPLAIN PLAN仅分析语句。
使用AUTOTRACE不会产生跟踪文件。
一、启用Autotrace功能。任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。
1、报错示例：SQL :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
*******************************************************************************
SQL> set autotrace on;
SP2-0613: 无法验证 PLAN_TABLE 格式或实体
SP2-0611: 启用EXPLAIN报告时出现错误
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用STATISTICS报告时出现错误
该错误的的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。
2、解决方法：
A．以SYS用户登录：
oracle>sqlplus '/ as sysdba';
B．运行utlxplan.sql(rdbms/admin下) 脚本创建 PLAN_TABLE；
SQL>@ D:oracleora81RDBMSADMINutlxplan.sql
C．通过执行 plustrce.sql(ORACLE_HOME/sqlplus/admin/plustrce.sql)脚本创建 plustrace 角色，这将V$ 视图上的选择权限授予该角色，也将 plustrace 角色授予 DBA 角 色，脚本部份内容如下：
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;D．将 plustrace 角色授予当前用户(无 DBA 角色的用户)。
或grant plustrace to public;
二、设置Autotrace的命令。
|
序号 |
命令 |
解释 |
|
1 |
SET AUTOTRACE OFF |
此为默认值，即关闭Autotrace |
|
2 |
SET AUTOTRACE ON |
产生结果集和解释计划并列出统计 |
|
3 |
SET AUTOTRACE ON EXPLAIN |
显示结果集和解释计划不显示统计 |
|
4 |
SETAUTOTRACE TRACEONLY |
显示解释计划和统计，尽管执行该语句但您将看不到结果集 |
|
5 |
SET AUTOTRACE TRACEONLY STATISTICS |
只显示统计 |
Eg：SET AUTOTRACE ON, set timing on, alter session set time_statistics=true;
三、Autotrace执行计划的各列的涵义
|
序号 |
列名 |
解释 |
|
1 |
ID_PLUS_EXP |
每一步骤的行号 |
|
2 |
PARENT_ID_PLUS_EXP |
每一步的Parent的级别号 |
|
3 |
PLAN_PLUS_EXP |
实际的每步 |
|
4 |
OBJECT_NODE_PLUS_EXP |
Dblink或并行查询时才会用到 |
四、AUTOTRACE Statistics常用列解释
|
序号 |
列名 |
解释 |
|
1 |
db block gets |
从buffer cache中读取的block的数量 |
|
2 |
consistent gets |
从buffer cache中读取的undo数据的block的数量 |
|
3 |
physical reads |
从磁盘读取的block的数量 |
|
4 |
redo size |
DML生成的redo的大小 |
|
5 |
sorts (memory) |
在内存执行的排序量 |
|
7 |
sorts (disk) |
在磁盘上执行的排序量 |