oracle的执行计划

今天在一台机器中想分析下执行计划，但是在sqlplus中输入set autotrace on 报cannot set autotrace 错误

解决方法如下：

首先必须采用用Oracle的sqlplus登陆sys账号

sqlplus " sys/vion@XXX as sysdba "

然后执行如下脚本：

@$ORACLE_HOME\sqlplus\admin\plustrce.sql （创建plustrace角色并授权）   

@$ORACLE_HOME\dbms\admin\utlxplan.sql （创建执行计划的表）

 

然后执行： grant all on plan_table to public; （也可以授权给某一个单独的用户）

               grant plustrace to public ; 

然后就可以进行 set autotrace了  但是只能在sqlplus中运行相关命令，在pl/sql developer等工具中仍然报错

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告

SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

查询一个测试表test_gel1

SQL> select * from CENTER.TEST_GEL1;


        ID NAME       JGSJ
---------- ---------- ------------
         1 ge
         2 ge
         3 ge
         4 ge
         5 ge
         6 ge
         7 ge
         8 ge
         9 ge
        10 ge
        10 ge


        ID NAME       JGSJ
---------- ---------- ------------
        10 ge
        10 ge


13 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 3551214153


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    13 |   377 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST_GEL1 |    13 |   377 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
        131  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        705  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         13  rows processed

字段说明

一下内容是参照网络上的：

执行计划中字段解释：

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

       Operation： 当前操作的内容。

       Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

       Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

       Time：Oracle 估计当前操作的时间。

 

3.2 谓词说明：

Predicate Information (identified by operation id):

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

   4 - access("A"."EMPNO"="B"."MGR")

       filter("A"."EMPNO"="B"."MGR")

   5 - filter("B"."MGR" IS NOT NULL)

 

       Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

       Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

 

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

 

 

3.3 统计信息说明：

 

 

db block gets ： 从buffer cache中读取的block的数量    

consistent gets： 从buffer cache中读取的undo数据的block的数量    

physical reads： 从磁盘读取的block的数量    

redo size： DML生成的redo的大小    

sorts (memory) ：在内存执行的排序量    

sorts (disk) ：在磁盘上执行的排序量    

 

       Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。

 

关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：

       数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

 

用以下语句可以查看数据缓冲区的命中率：

       SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

       查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。

 

Recursive Calls： Number of recursive calls generated at both the user and system level.    

Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。

 

DB Block Gets：Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets:请求的数据块在buffer能满足的个数)
       当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

 

Consistent Gets： Number of times a consistent read was requested for a block.

This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets: 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)
       这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产生了一致性读。

 

Physical Reads：Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)

就是从磁盘上读取数据块的数量，其产生的主要原因是：
       （1） 在数据库高速缓存中不存在这些块
       （2） 全表扫描
       （3） 磁盘排序
它们三者之间的关系大致可概括为：
       逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'physical reads'。

 

Sorts(disk):

    Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

bytes sent via SQL*Net to client:
    Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client:
    Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client:
    Total number of Oracle Net messages sent to and received from the client.

 

 

更多内容参考Oracle联机文档：

       Statistics Descriptions

       http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/stats002.htm#i375475

 

3.4 动态分析

       如果在执行计划中有如下提示：

              Note

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

                     -dynamic sampling used for the statement

      

       这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可能没有做过分析。

 

 

这里会出现两种情况：

（1）       如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。

（2）       如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划