Oracle 性能工具 : Explain plan、Autotrace、Tkprof

Oracle: 三个内置的性能工具包 Explain plan、Autotrace、Tkprof:
http://vitas08xy.blog.sohu.com/108168502.html

Oracle EXPLAIN PLAN 执行计划
Using EXPLAIN PLAN：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#PFGRF009

通过分析SQL语句的执行计划优化SQL(总结)：
http://www.itpub.net/thread-478999-1-11.html#

引用

读笔：
1 初始化参数optimizer_mode：：
http://blog.csdn.net/whqcfp/archive/2006/12/05/1430653.aspx google more
2 Oracle 绑定变量：
http://www.ezloo.com/2009/04/oracle_bind_variable.html google more
ACCESS PATH(访问路径/存取路径)：
http://space.itpub.net/6517/viewspace-145522
http://lzfhope.blog.163.com/blog/static/6363992201072465310995/

Oracle's explain plan：
http://www.adp-gmbh.ch/ora/explainplan.html

Oracle执行计划解释：
http://space.itpub.net/14075938/viewspace-471205

怎样看懂Oracle的执行计划:
http://hi.baidu.com/edeed/blog/item/73c46538d2614d2796ddd864.html
原文：http://kr.forums.oracle.com/forums/thread.jspa?messageID=4537207  以后再找最初的原文

SQLPlus中查看解释计划：

SQL> explain plan for <需做解释计划的语句>;
Explained
SQL> select * from table(dbms_xplan.display);

关于dbms_xplan.display：
dbms_xplan是SYS用户的一个package，display是该package下的一个function。







TKPROF
关于tkprof：

引用

You   can   run   the   TKPROF   program   to   format   the   contents   of   the   trace   file   and   place   the   output   into   a   readable   output   file.   Optionally,   TKPROF   can   also:  
Determine   the   execution   plans   of   SQL   statements  
Create   a   SQL   script   that   stores   the   statistics   in   the   database  
TKPROF   reports   each   statement   executed   with   the   resources   it   has   consumed,   the   number   of   times   it   was   called,   and   the   number   of   rows   which   it   processed.   This   information   lets   you   easily   locate   those   statements   that   are   using   the   greatest   resource.   With   experience   or   with   baselines   available,   you   can   assess   whether   the   resources   used   are   reasonable   given   the   work   done.

  
TKPROF使用实例：
http://www.blogjava.net/wxqxs/archive/2009/04/12/265343.html
使用TKPROF的一个例子：
http://unix-cd.com/vc/www/16/2010-09/16229.html








Oracle alert.log这个文件放在哪里?
Unix: $ORACLE_BASE/admin/sid/bdump下
Windows: %ORACLE_BASE%/admin/sid/bdump下
另外trace文件在udump文件夹下


Oracle Table Access Methods:
http://www.remote-dba.net/t_op_sql_table_access.htm
How to avoid the full table scan:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968


How to fetch all the records ：
http://forums.oracle.com/forums/thread.jspa?threadID=447344

引用

There's no setting to fetch all records. You wouldn't like SQL Developer to fetch for minutes on big tables anyway.
If, for 1 specific table, you want to fetch all records, you can do Control-End in the results pane to go to the last record. You could time the fetching time yourself, but that will vary on the network speed and congestion, the program (SQL*Plus will be quicker than SQL Dev because it's more simple), etc.
Now, what I think you really want here, is getting the execution time. If you really want to analyze the query's performance, you'd have to use the explain plan, autotrace and tkprof (see the manual for tuning and performance).
To make us life easier, the makers of SQL Developer included support of the explain plan, and as of v1.1, autotrace!

I'm sure you've got enough to work things out from here...