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

最新推荐文章于 2019-09-03 16:58:00 发布

Wuaner

最新推荐文章于 2019-09-03 16:58:00 发布

阅读量65

点赞数

分类专栏： Oracle 文章标签： Oracle SQL SQL Server .net VC++

本文链接：https://blog.csdn.net/Wuaner/article/details/83709129

版权

Oracle 专栏收录该内容

68 篇文章 0 订阅

订阅专栏

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

[size=large][color=red]Oracle EXPLAIN PLAN 执行计划[/color][/size]
Using EXPLAIN PLAN：
[url]http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#PFGRF009[/url]

通过分析SQL语句的执行计划优化SQL(总结)：
[url]http://www.itpub.net/thread-478999-1-11.html#[/url][quote]读笔：
1 初始化参数optimizer_mode：：
[url]http://blog.csdn.net/whqcfp/archive/2006/12/05/1430653.aspx[/url] google more
2 Oracle 绑定变量：
[url]http://www.ezloo.com/2009/04/oracle_bind_variable.html[/url] google more
ACCESS PATH(访问路径/存取路径)：
[url]http://space.itpub.net/6517/viewspace-145522[/url]
[url]http://lzfhope.blog.163.com/blog/static/6363992201072465310995/[/url]
[/quote]

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

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

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

SQLPlus中查看解释计划：

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

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

[size=large][color=red]TKPROF[/color][/size]
关于tkprof：[quote]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.[/quote]
TKPROF使用实例：
[url]http://www.blogjava.net/wxqxs/archive/2009/04/12/265343.html[/url]
使用TKPROF的一个例子：
[url]http://unix-cd.com/vc/www/16/2010-09/16229.html[/url]

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

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

How to fetch all the records ：
[url]http://forums.oracle.com/forums/thread.jspa?threadID=447344[/url][quote]
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...
[/quote]