看到报表里庞大的sql觉得sql高深复杂.
查询树的概念不多.plsql里 的explain plan 好像很有用
先利其器
摘自:http://blog.csdn.net/kj021320/archive/2006/08/19/1096021.aspx
如何生成explain plan?
解答:运行utlxplan.sql. 建立plan 表
针对特定SQL语句,使用 explain plan set statement_id = 'tst1' into plan_table
运行utlxplp.sql 或 utlxpls.sql察看explain plan
EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.
你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.
NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.
译者按:
通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便.
举例:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
30 consistent gets
0 physical reads
0 redo size
2598 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
通过以上分析,可以得出实际的执行步骤是:
1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)
注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.
----------------------------------------------------------------------------
对于sql执行的小量高低.我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间.连接顺序及浪费的数据库资源等信息,从而判断该SQL语句执行的效率如何,下面就简单的介绍一下执行计划的使用
2.
Oracle RDBMS执行每一条SQL语句,都必须经过Oracle优化器的评估。所 以,了解优化器是如何选择(搜索)路径以及索引是如何被使用的,对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。
2.1.
要使用执行计划首先需要执行相应的脚本。
使用Explain工具需要创建Explain_plan表,这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序,例如:
ORA_RDBMS: XPLAINPL.SQL (VMS)
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)
该脚本后会生成一个表这个程序会创建一个名为plan_table的表,表结构如下:
我们简单的介绍一下主要的字段含义:
字段名
STATEMENT_ID
REMARKS
OPERATION
OPTIONS
OBJECT_NODE
OBJECT_OWNER
OBJECT_NAME
OBJECT_INSTANCE
OBJECT_TYPE
OPTIMIZER
ID
PARENT_ID
POSITION
COST
CARDINALITY
BYTES
2.2.
2.2.1.
常规使用语法:
explain PLAN [ SET STATEMENT_ID [=] < string literal > ]
[ INTO < table_name > ]
FOR < sql_statement >
其中:
STATEMENT_ID是一个唯一的字符串,把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。
TABLE_NAME是plan表名,它结构如前所示,你可以任意设定这个名称。
SQL_STATEMENT是真正的SQL语句。
如:
SQL> explain plan set statement_id='test1' for
10
11
12
13
14
15
16
17
18
19
20
21
Explained
执行下面语句就可以查看该语句执行的执行计划:
SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
OPERATION
---------------- --------------------------------------------- ------------- ----------
SELECT STATEMENT
WINDOW
FILTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
TABLE ACCESS
INDEX
TABLE ACCESS
INDEX
TABLE ACCESS
INDEX
2.2.2.
在SQLPLUS中自动跟踪显示执行计划及相关信息
SQL>set timing on
SQL>set autorace on �C显示执行计划
SQL>set autorace on �C显示执行计划
SQL>set autotrace traceonly �C只显示执行计划即不显示查询出来的数据
设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)
SQL> select nvl(sum(t.taxdue), 0)
NVL(SUM(T.TAXDUE),0)
--------------------
已用时间:
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
SQL>
如果6675
45
的数值比较小则该语句对对数据库的性能比较高。
2.2.3.
如果在PL/SQL中使用选择要查询语句显示执行计划,则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句,然后选择按键F5或者在菜单TOOLS�D�D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。
在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。
2.3.
虽然任何SQL语句都可以用explain解释,但对于没有查询的INSERT,UPDATE,DELETE操作来说,这个工具并没有太大的用处。没有子查询的INSERT操作不会创建执行计划,但没有WHERE子句或子查询的UPDATE和DELETE操作会创建执行计划,因为这些操作必须先找出所要的记录。
另外,如果你在SQL语句中使用其它类型如sequence等,explain也能揭示它的用法。
explain真正的唯一的限制是用户不能去解释其它用户的表,视图,索引或其它类型,用户必须是所有被解释事物的所有者,如果不是所有者而只有select权限,explain会返回一个错误。