`
longxiaoyan
  • 浏览: 77507 次
  • 性别: Icon_minigender_1
  • 来自: 桂-京
社区版块
存档分类
最新评论

Oracle -- 用EXPLAIN PLAN 分析SQL语句(转载)

阅读更多

From: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 /*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.        Explain使用
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        VARCHAR2(30)        explain PLAN 语句中所指定的最优STATEMENT_ID 参数值, 如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID,那么此值会被设为NULL。  
REMARKS        VARCHAR2(80)        与被解释规划的各步骤相关联的注释最长可达80 字节
OPERATION        VARCHAR2(30)        各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
OPTIONS        VARCHAR2(30)        对OPERATION 列中所描述操作的变种
OBJECT_NODE        VARCHAR2(128)        用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序
OBJECT_OWNER        VARCHAR2(30)        对于包含有表或索引的架构schema 给出其所有者的名称
OBJECT_NAME        VARCHAR2(30)        表或索引的名称
OBJECT_INSTANCE        INTEGER        根据对象出现在原始original 语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩张view
OBJECT_TYPE        VARCHAR2(30)        用于提供对象描述性信息的修饰符例如索引的NON-UNIQUE
OPTIMIZER        VARCHAR2(255)        当前优化程序的模式
ID        INTEGER        分配给执行规划各步骤的编号
PARENT_ID        INTEGER        对ID 步骤的输出进行操作的下一个执行步骤的ID
POSITION        INTEGER        对于具有相同PARENT_ID 的步骤其相应的处理次序
COST        INTEGER        根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值
CARDINALITY        INTEGER        根据基于开销的方法对操作所访问行数的估计值
BYTES        INTEGER        根据基于开销的方法对操作所访问字节的估计

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  
  2     SELECT a.soctermbegin,
  3            a.soctermend,
  4            a.dealserialno,
  5            a.levydataid,
  6            a.dealtotal,
  7            e.categoryitemcode,
  8            row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow
  9       FROM tb_soc_packdealdata   a,
10            tb_Lvy_TaxDataBillMap c,
11            Tb_lvy_BillData       d,
12            tb_soc_levydetaildata e
13      WHERE a.levydataid = c.datafrompointer(+)
14        AND c.billdataid = d.billdataid(+)
15        AND a.levydataid = e.levydataid
16        AND a.packdealstatuscode = '10'
17        AND (a.datastatus <> '9' OR a.datastatus is NULL)
18        AND (d.billstatus IS NULL OR
19            (d.billstatus <> '2' AND d.billstatus <> '8'))
20        AND a.Insurcode = '6010952'
21  ;
Explained

 

执行下面语句就可以查看该语句执行的执行计划:

SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
  2  FROM PLAN_TABLE  a
  3  WHERE STATEMENT_ID='test1'
  4  ORDER BY Id;
OPERATION        OPTIONS        OBJECT_NAME                    OBJECT_TYPEID  PARENT_ID
---------------- --------------------------------------------- ------------- ----------
SELECT STATEMENT                                                           0 
WINDOW           SORT                                                      1          0
FILTER                                                                     2          1
NESTED LOOPS     OUTER                                                     3          2
NESTED LOOPS     OUTER                                                     4          3
NESTED LOOPS                                                               5          4
TABLE ACCESS     FULL           TB_SOC_PACKDEALDATA                        6          5
TABLE ACCESS     BY INDEX ROWID TB_SOC_LEVYDETAILDATA                      7          5
INDEX            RANGE SCAN     IND_DATAID_LEVSOC              NON-UNIQUE  8          7
TABLE ACCESS     BY INDEX ROWID TB_LVY_TAXDATABILLMAP                      9          4
INDEX            RANGE SCAN     TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10          9
TABLE ACCESS     BY INDEX ROWID TB_LVY_BILLDATA                           11          3
INDEX            UNIQUE SCAN    TBLVYBILLDATA_BILLDATAID       UNIQUE   

 

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)
  2             from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b
  3            where a.dossiercode = 'SB02041108'
  4              and a.pages = 123
  5              and a.remarkid = b.remarkid
  6              AND A.REMARKID IS NOT NULL
  7              and b.declaredocid = t.declaredocid;
NVL(SUM(T.TAXDUE),0)
--------------------
                   0
已用时间:  00: 00: 04.07
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=110)
   3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74)
   4    3         TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)
   5    3         BUFFER (SORT) (Cost=2 Card=1 Bytes=43)
   6    5           TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)
   8    7         INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls --循环递归次数
          0  db block gets―请求的数据块在buffer能满足的个数
       6675  consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中
         45  physical reads �C从磁盘读到Buffer Cache数据块数量
          0  redo size �C产生的redo日志大小
        217  bytes sent via SQL*Net to client
        276  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>

 

如果6675  consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中
45        physical reads �C从磁盘读到Buffer Cache数据块数量
的数值比较小则该语句对对数据库的性能比较高。

2.2.3.        PL/SQL和TOAD中使用
如果在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会返回一个错误。 

分享到:
评论

相关推荐

    ORACLE EXPLAIN PLAN的总结

    Oracle的Explain Plan是数据库管理员和开发人员用来分析SQL查询执行计划的重要工具。它能提供关于如何执行SQL语句的详细信息,包括哪些操作(如全表扫描、索引访问等)将被使用,以及这些操作的预计成本、时间及资源...

    Oracle中explain_plan的用法

    Oracle 中的 explain_plan 是一个强大的分析工具,能够帮助用户了解 SQL 语句的执行计划,通过它可以了解 Oracle 是如何连接表、使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称。下面详细讲解 ...

    oracle explain plan总结

    ### Oracle Explain Plan 总结 #### 一、Oracle 执行计划的重要性 在Oracle数据库中,为了提高查询效率和...无论是使用`EXPLAIN PLAN`还是`AUTOTRACE`,都能够帮助我们更好地理解SQL语句的执行流程,并据此进行优化。

    Oracle中EXPLAIN PLAN的使用技巧

    其中,EXPLAIN PLAN是一个不可或缺的诊断工具,它用于分析和展示SQL语句的具体执行计划,从而让开发者和数据库管理员能够窥探SQL语句在执行过程中的每一步操作。通过对执行计划的深入理解,可以有效地诊断查询瓶颈,...

    压测Oracle的SQL语句的性能情况

    在Oracle环境中,LR脚本可以用于执行SQL语句,分析响应时间、事务速率等关键性能指标。 "直连Oracle执行SQL进行压测"意味着测试者直接与数据库交互,不通过中间层如应用程序服务器,这能更准确地反映出数据库本身的...

    ORACLE-SQL性能优化大全.pdf

    - **共享SQL区域**:Oracle会在内存的共享池中缓存已执行过的SQL语句,以便后续执行时可以直接使用而无需重新解析。 - **SQL语句处理的阶段**:包括解析、执行、归还等阶段。 - **共享游标**:多个SQL语句如果...

    oracle-explain.rar_oracle

    - 分析慢查询:当遇到查询速度慢的问题时,先用`EXPLAIN PLAN`找出问题原因,然后针对性地调整索引或SQL语句。 - 预测性能:在新查询或表结构调整前,通过执行计划预估其对系统的影响。 - 教程学习:对于初学者,...

    oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.rar

    - **性能监控**:通过`EXPLAIN PLAN`等命令分析SQL执行计划,优化查询性能。 - **用户管理和权限控制**:管理用户账户,分配角色和权限。 在使用Oracle Instant Client时,需要注意以下几点: - **兼容性**:确保...

    ORACLE-SQL优化

    优化者可以通过EXPLAIN PLAN语句来获取SQL语句的执行计划,并使用DBMS_XPLAN等工具深入分析。 五、SQL语句优化的过程 SQL语句优化的过程包括定位问题语句、检查执行计划、分析优化器统计信息、检查相关表的记录数和...

    通过分析SQL语句的执行计划优化SQL(总结)

    在不同的数据库系统中,如MySQL、Oracle、SQL Server等,都有相应的命令或工具用于查看SQL语句的执行计划,例如SQL Server的`SET SHOWPLAN_ALL`或`SET SHOWPLAN_TEXT`,Oracle的`EXPLAIN PLAN`。 4. **执行计划的...

    Oracle中SQL语句执行效率的查找与解决

    - **EXPLAIN PLAN使用**:EXPLAIN PLAN是一个强大且常用的工具,用于展示SQL语句的执行计划。通过设置STATEMENT_ID,可以为SQL语句标识唯一的执行计划,便于后续比较和分析。在执行计划中,OPERATION字段描述了具体...

    Oracle SQL语句性能优化

    - 分析和调整执行计划:使用EXPLAIN PLAN分析SQL执行路径,通过ALTER SYSTEM或ALTER SESSION指令调整优化器行为。 - 优化子查询和联接操作:考虑子查询的替代方案,如使用连接或临时表。 - 使用绑定变量避免硬...

    Oracle-Perf-check-sql.rar_oracle

    使用EXPLAIN PLAN语句或者DBMS_XPLAN包可以查看SQL的执行路径,分析是否存在全表扫描、索引使用不当等问题。 6. **资源使用情况**:V$INSTANCE和V$PGA Aggregate Views等视图可以展示CPU、内存、I/O等资源的使用...

    Oracle-SQL性能优化及案例分析.ppt

    Oracle数据库提供了多种工具和技术来帮助优化SQL语句。 首先,Oracle有两种主要的优化器:规则优化器(RBO)和成本优化器(CBO)。RBO基于预定义的规则和硬编码的优先顺序来决定执行计划,例如,它倾向于先使用...

    数据库调优:ORACLE EXPLAIN PLAN的总结

    在数据库管理领域,Oracle数据库的性能优化是一项至关重要的任务,其中`EXPLAIN PLAN`是数据库管理员和开发人员常用的工具,用于分析SQL查询的执行计划,从而理解查询如何在数据库中运行,找出潜在的性能瓶颈。...

    Oracle的SQL语句执行效率问题查找与解决方法文.pdf

    Oracle SQL 语句执行效率问题查找与解决方法 一、 Oracle SQL 语句执行效率问题查找方法 Oracle 数据库系统中, SQL 语句的...通过对 Explain Plan 的分析,我们可以找到问题 SQL 语句的执行计划,从而优化执行效率。

    ORACLE DBA常用SQL语句

    在Oracle数据库管理(DBA)工作中,SQL语句扮演着至关重要的角色,它们是DBA进行数据查询、数据库维护和性能优化的主要工具。以下是一些ORACLE DBA常用的SQL语句,这些语句涵盖了数据库管理和优化的核心方面: 1. *...

    oracle常用sql语句大全 注释完整 放心使用

    本资料集包含了Oracle数据库中常用SQL语句的大全,注释完整,非常适合初学者和有经验的开发者参考使用。 1. **数据查询(SELECT语句)**: - `SELECT * FROM table_name;`:查询表table_name中的所有列。 - `...

    oracle的SQL语句的一些经验总结

    11. **性能优化**:使用EXPLAIN PLAN分析查询执行计划,通过绑定变量减少硬解析,以及使用DBMS_STATS收集表的统计信息,都是提升SQL性能的方法。 12. **并发控制**:Oracle的锁定机制,如行级锁定,用于在多用户...

    oracle常用监控SQL语句集合

    在Oracle数据库管理中,了解和掌握一些常用的监控SQL语句是至关重要的,这些语句能够帮助DBA(数据库管理员)实时了解数据库的运行状态、性能瓶颈以及资源使用情况。"oracle常用监控SQL语句集合"提供了这样的工具集...

Global site tag (gtag.js) - Google Analytics