`
拓子轩
  • 浏览: 210693 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

使用Oracle执行计划分析SQL性能

    博客分类:
  • db
阅读更多

执行计划可以用来分析SQL的性能

 

一、查看执行计划的方法

1. 设置autotrace

    set autotrace off: 此为默认值,即关闭autotrace

    set autotrace on explain: 只显示执行计划

    set autotrace on statistics: 只显示执行的统计信息

    set autotrace on: 既显示执行计划,又显示执行的统计信息

    set autotrace traceonly: 与on相似,但不显示语句的执行结果

 

    示例:

        set autotrace on;

        select 1 from dual;

 

    注意:如果在执行set autotrace时出现以下错误提示:

             SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled

             SP2-0611: Error enabling STATISTICS report 

             可尝试如下方式解决:

             conn / as sysdba;

             执行@$ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql,或执行一下$ORACLE_HOM\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql文件的内容.

             执行@$ORACLE_HOME/sqlplus/admin/plustrce.sql,或执行一下$ORACLE_HOM\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql文件的内容.

             grant plustrace to public;

 

2. 使用SQL

    执行:explain plan for <sql语句>

    查看:SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

             或 select * from table(dbms_xplan.display);

 

    示例:

        explain plan for select 1 from dual;

        select * from table(dbms_xplan.display);

 

3. 使用PL/SQL Developer、Toad等工具

    在PL/SQL Developer中,选中SQL语句,然后点击菜单“工具”-“解释计划”或按快捷键F5即可。

 

二、执行计划结果信息说明

 

    上面执行计划示例在运行之后可能会输出如下信息,接下来对这些信息进行进一步说明

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1388734953

--------------------------------------------------------------------------------

| Id  | Operation                | Name | Rows  | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |           |     1    |     2   (0)      | 00:00:01 |

|   1 |  FAST DUAL              |           |     1    |     2   (0)      | 00:00:01 |

--------------------------------------------------------------------------------

 

1. 执行计划中字段的说明

    Id: 一个序号,但不是执行的先后顺序。执行的先后根据缩进来判断。

    Operation: 当前操作的内容。

    Name: 操作的对象名称。

    Rows: 当前操作的基数,Oracle估计当前操作的返回结果集。

    Cost(%CPU): Oracle 计算出来的一个数值(代价),用于说明SQL执行的代价。

    Time: Oracle估计当前操作的时间

 

2. 执行计划中内容的说明

 

    table access full: 全表扫描,对所有表中记录进行扫描。使用多块读操作,一次I/O能读取多块数据块。表字段不涉及索引时往往采用这种方式。

                             较大的表不建议使用全表扫描,除非结果数据超出全表数据总量的10%。

    table access by index rowid: 通过ROWID的表存取,一次I/O只能读取一个数据块。通过rowid读取表字段,rowid可能是索引键值上的rowid。

 

    4种类型的索引扫描(index scan)

    index unique scan: 索引唯一扫描,如果表字段有UNIQUE 或PRIMARY KEY 约束,Oracle实现索引唯一扫描,这种扫描方式条件比较极端,出现比较少。

    index range scan: 索引范围扫描,最常见的索引扫描方式。在非唯一索引上都使用索引范围扫描。

 1 ) 在唯一索引列上使用了以下圈定范围的操作符(> < <> >= <= between等)

        2 ) 在组合索引上,只使用部分列进行查询,导致查询出多行

        3 ) 对非唯一索引列上进行的任何查询

    index full scan:  索引全扫描,这种情况下,是查询的数据都属于索引字段,一般都含有排序操作。

    index fast full scan: 索引快速扫描,如果查询的数据都属于索引字段,并且没有进行排序操作,那么是属于这种情况。条件比较极端,出现比较少。

    

    表之间的连接方式

    nested loops: 嵌套循环,该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好。

                         如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,

                         或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。

    hash join: 哈希连接,在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。

    sort merge join: 排序 - 合并连接,该种排序限制较大,出现比较少

                            内部连接过程:

        1) 首先生成表1需要的数据,然后对这些数据按照连接操作关联列进行排序;

        2) 随后生成表2需要的数据,然后对这些数据按照与表1对应的连接操作关联列进行排序;

        3) 最后两边已排序的行被放在一起执行合并操作,即将2个表按照连接条件连接起来。

 

三、表连接方法

  1. 排序 - - 合并连接(Sort Merge Join, SMJ):

  a) 对于非等值连接,这种连接方式的效率是比较高的。

  b) 如果在关联的列上都有索引,效果更好。

  c) 对于将2个较大的row source做连接,该连接方法比NL连接要好一些。

  d) 但是如果sort merge返回的row source过大,则又会导致使用过多的rowid在表中查询数据时,数据库性能下降,因为过多的I/O.

 

  2. 嵌套循环(Nested Loops, NL):

  a) 如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。

  b) NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。

 

  3. 哈希连接(Hash Join, HJ):

  a) 这种方法是在oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在CBO优化器中,而且需要设置合适的hash_area_size参数,才能取得较好的性能。

  b) 在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。

  c) 只能用于等值连接中

 

 四、执行计划统计信息

1. 统计信息含义

    recursive calls: 递归调用次数; 

    db block gets: 当期操作时从内存读取的当前最新块数据,并不是在一致性读的情况的块数,即通过update/delete/select for update读的块数; 

    consistent gets: 当期操作时在一致性读状态下读取的块数,即通过不带for update的select 读的块数; 

    physical reads: 物理读,Oracle从磁盘读的数据块数量, 其产生的主要原因是:在数据库高速缓存中不存在这些块;全表扫描;磁盘排序。其中逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取,于是就产生了'phsical reads'。 

    redo size: 执行SQL的过程中产生的重做日志; 

    519 bytes sent via SQL*Net to client: 通过网络发送给客户端的数据 

    524 bytes received via SQL*Net from client: 通过网络从客户端接收到的数据 

    SQL*Net roundtrips to/from client:通过网络客户端发送或接收的数量

    sorts (memory): 在内存中发生的排序

    sorts (disk): 在硬盘中发生的排序

    rows processed:处理的行数

 

2. 统计信息示例



 

 

参考资料:

http://www.cnblogs.com/jianggc/articles/2029854.html

http://www.cnblogs.com/fqw1987815/archive/2010/08/18/1802657.html

http://blog.csdn.net/zongrongna/article/details/51580415

http://blog.chinaunix.net/uid-21187846-id-3022916.html

  • 大小: 5.9 KB
分享到:
评论

相关推荐

    性能调优分析之:Oracle SQL执行计划报告生成器

    Oracle SQL执行计划分析器功能的创建3步曲: 1 首先,编译XYG_ALD_SESS_PKG的Package头。 (XYG_ALD_SESS_PKG.sql) 2 接着要建立好下面的4个视图对象。因为XYG_ALD_SESS_PKG包体会用到。(View Create Script v...

    ORACLE执行计划和SQL调优

    【Oracle执行计划和SQL调优】是数据库管理中至关重要的环节,主要涉及到如何高效地运行SQL语句,提高数据库性能。下面将详细讲解执行计划的相关概念以及SQL调优的策略。 1. **Rowid的概念**:Rowid是Oracle数据库中...

    ORACLE执行计划和SQL调优.pptx

    Oracle执行计划和SQL调优是数据库管理中至关重要的主题,主要涉及如何优化SQL查询以提高数据库性能。在Oracle数据库系统中,SQL语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细...

    oracle 执行计划 详解

    执行计划的解读方法可以使用 `V$SQLAREA`、`V$SQL_PLAN`、`V$SQLSTATS` 等性能视图来获取执行计划的信息。 执行计划的优化 执行计划的优化可以通过调整索引、访问路径、联接顺序等方法来实现。 索引扫描 索引...

    Oracle执行计划与SQL优化实例.pptx

    综上所述,《Oracle执行计划与SQL优化实例》不仅提供了理论知识,还通过具体实例展示了如何运用执行计划来优化SQL查询,是数据库管理员和开发人员提升Oracle数据库性能不可或缺的指南。理解和应用这些知识,将有助于...

    oracle执行计划详解

    Oracle 将 SQL 语句及解析后得到的执行计划存放在内存中,以便下次执行相同的 SQL 语句时可以直接获取执行计划,提高了 SQL 的执行性能和节省了内存的使用。这块位于系统全局区域 SGA(system global area)的共享池...

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

    执行计划是数据库管理系统在执行SQL语句之前预先生成的一种详细步骤,它描述了如何访问数据、如何连接表、使用哪些索引等。理解执行计划有助于我们找出可能导致性能瓶颈的操作。 2. **查询优化器**: 数据库的...

    ORACLE执行计划和SQL调优.ppt

    ORACLE执行计划和SQL调优 ORACLE执行计划是指数据库管理系统在执行SQL语句时,数据库管理系统如何选择执行计划,...ORACLE执行计划和SQL调优非常重要,对于提高数据库管理系统的执行效率和性能有着至关重要的作用。

    ORACLE SQL性能优化系列

    ORACLE SQL性能优化系列 ORACLE SQL性能优化是数据库管理员和开发者非常关心的一个话题。为了提高数据库的性能,ORACLE 提供了多种优化技术。下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一...

    Oracle数据库性能分析

    - **选择执行计划**: Oracle会选择最有效的执行路径来执行SQL语句。 - **装载共享SQL区**: 将SQL语句装载到共享池中的共享SQL区。 - **生成编译版本**: 生成SQL语句的可执行版本。 2. **执行(Execution)**: 一旦...

    oracle优化sql执行效率-表分析

    为提高oracle的sql执行效率,优化性能,通过oracle表分析功能进行调整,能加快sql查询效率2倍以上

    oracle怎么查看执行计划

    不同于TKPROF需要格式化跟踪文件才能查看执行计划,AUTOTRACE可以在执行SQL语句的同时生成执行计划,无需额外步骤。同样,与EXPLAIN PLAN相比,AUTOTRACE不仅展示了执行计划,还执行了SQL语句,从而能够提供更加准确...

    ORACLE执行计划和SQL调优知识概述.pptx

    Oracle 执行计划和 SQL 调优是数据库性能优化的重要组成部分。了解执行计划和 SQL 调优的相关概念是数据库管理员和开发人员的必备知识。本文将对 Oracle 执行计划和 SQL 调优的相关概念进行概述。 一、背景知识 在...

Global site tag (gtag.js) - Google Analytics