AUTOTRACE与EXPLAIN PLAN,ANALYZE与DBMS_STATS

AUTOTRACE与EXPLAIN PLAN,ANALYZE与DBMS_STATS


SET AUTOTRACE TRACEONLY与EXPLAIN PLAN FOR
AUTOTRACE可以在SQL执行完后打印出执行计划,
EXPLAIN PLAN FOR执行后需要SELECT * FROM TABLE(dbms_xplan.display)才能得出执行计划,但是它不需要真正的去执行SQL.
如果某段SQL需要很久才能完成,使用EXPLAIN PLAN去得出执行计划会节省很多时间.
set autotrace traceonly explain的效果等价于EXPLAIN PLAN FOR,而且不用查询dbms_xplan.display就可得出执行计划( http://www.itpub.net/thread-478999-1-2.html).
在SQL正式执行时增加hint /*+ gather_plan_statistics */可在执行计划中显示真实的执行次数(Starts)、真实的返回行数(A-Rows:Actual Rows)和真实的执行时间(A-Time:Actual Time).

ANALYZE与DBMS_STATS.GATHER_TABLE_STATS
ANALYZE是一个比较老的收集CBO统计信息的工具,已被Oracle deprecated,
DBMS_STATS不仅可以收集表的统计信息,还可以收集schema的,更新,功能也更多.


Oracle EBS R12.1中如何在apps用户下查看执行计划
copy from apps/apps@DORA1i to apps/apps@DORA1i create PLAN_TABLE_bak using select * from PLAN_TABLE;
drop table PLAN_TABLE;
sqlplus apps/apps @$ORACLE_HOME/rdbms/admin/utlxplan.sql


1. EXPLAIN PLAN Usage
http://www.oracle-base.com/articles/8i/explain-plan-usage.php

2. ANALYZE TABLE command ?
https://forums.oracle.com/forums/thread.jspa?threadID=2388218

3. ORA-00997: illegal use of LONG datatype
http://space.itpub.net/20750200/viewspace-696200

4. Old PLAN_TABLE in EBS Vision
http://only4left.jpiwowar.com/2010/05/old-plan_table-in-ebs-vision/

5. 执行计划中各字段各模块描述
http://blog.csdn.net/robinson_0612/article/details/6860007

6. 索引叶子块里被删除的entry有用吗？
http://www.itpub.net/thread-1793233-1-2.html