使用dbms_xplan工具查看执行计划9i有一个新的包 dbms_xplan，对查询plan_table表是一个很有用的工具，相对于以前写一个复杂的SQL语句，然后从plan_table看执行计划，......
使用dbms_xplan工具查看执行计划
9i有一个新的包 dbms_xplan，对查询plan_table表是一个很有用的工具，相对于以前写一个复杂的SQL语句，然后从plan_table看执行计划，不如调用 dbms_xplan 包，还可以显示格式，这个工具的使用也非常方便。
调用的语法类似
select * from table(dbms_xplan.display(format=>'BASIC'))
使用 TABLE() 操作符，或者 CAST 操作。
DISPLAY 函数有三个参数
TABLE_NAME 指出优化计划放在哪个表里面，默认是 PLAN_TABLE.
STATEMENT_ID 指的是plan table中的statement_id字段，默认是last ID 或者 NULL.
FORMAT 指的是显示的格式
FORMAT参数有三个可选值，原文如下
BASIC It provides only the minimum amount of information, as in
case of the example above, similar to a query from
PLAN_TABLE directly.
TYPICAL This is the default value. It provides a variety of the
information useful for understanding how the optimizer
works for this statement. For instance, in case of partitioned
table operation, the columns PARTITION_START,
PARTITION_STOP, PARTITION_ID, and
FILTER_PREDICATES are displayed in addition to COST
for that step, the number of rows expected to be retrieved,
and number of bytes those rows may have. This provides
the information to understand statements involving
partitioned objects.
ALL This setting displays all the information displayed for the
BASIC and TYPICAL values, and also displays parallel
query operations and the related SQL statements, if those
are involved.
SERIAL This setting gets results similar to those retrieved by the
TYPICAL setting, but the queries are explained serially even
if a parallel query will be used.
一般推荐使用typical 参数，把SQLPLUS的linesize 参数调整到至少 120
下面是测试步骤
一
用sys用户建立
PLUSTRACE 角色
$ORACLE_HOME\E:\oracle\ora92\sqlplus\admin\Plustrce.sql
二：把权限授予某个人
grant plustrace to mjs;
三：建立表
建表SQL脚本为在${ORACLE_HOME}/rdbms/admin/下的utlxplan.sql。
1/5 1 2 3 4 5 下一页 尾页 |
