oracle执行计划结果分析,解析Oracle执行计划的结果

对于得到的SQL执行计划，一般如下：

执行计划

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

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

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

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

|   0 | SELECT STATEMENT   |              |     1 |    19 |    57   (2)|

|   1 |  SORT AGGREGATE    |              |     1 |    19 |            |

|   2 |   TABLE ACCESS FULL| T_ORGANISE | 17228 |   319K|    57   (2)|

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

统计信息

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

0  recursive calls

0  db block gets

253  consistent gets

0  physical reads

0  redo size

520  bytes sent via SQL*Net to client

469  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

其中，上半部分，包括表的连接访问方式等，这里先不过多阐述，我们来看看下面的统计信息：

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是DML 的读取，其看到的数据可能不具有时间点的一致性。

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.

当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

consistent gets是select 这样的读取，无论是否来自回滚段的数据都是 consistents 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.

这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产生了一致性读。

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.

redo size 该操作产生的redo日志大小。

redo block size is platform. specific. There is a method to determine the size by dumping the redo header, refer to note 154864.1. Redo blocks written does not include archive writes or multiplexed writes.

sorts (memory)  该操作所产生的数据在内存上的排序次数。

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.

rows processed 操作返回的记录条数。

总结：

这其中主要涉及到了Oracle读取数据的consistent mode和current mode这两个模式，对于db block gets是在current mode下读取的block数目(单位应该是“块次”，同一个block读取了两个算做2)，而consistent gets是在consistent mode下读取的block数目(单位同上)。

current mode下读取数据是为了保证读取到的数据是当前时间点上最新的数据，这样做的目的一般都是为了DML语句的需求，比如需要更新，自然需要知道最新的数据才行；consistent mode呢主要是为了保证Oracle数据一致读的特性，一般都是在select情况下发生，读到的数据可能是一个实际存在的block，也有可能需要根据scn信息以及transaction相关信息以及回滚段中数据来构造。

而physical reads是与logical reads相对的一个概念，两者的区别是读取的数据是从buffer中读取到还是从disk上的db file中取到。通过v$sysstat也可以看到，里面还有db block gets from cache以及consistent gets from cache两项，且这两项的数值与db block gets和consistent gets并不相同且小于后两者。所以不管是db block gets还是consistent gets，都可能出现了physical reads和logical reads两种情况(由buffer中的是否已经存在需要的数据)，也即是说，db block gets与consistent gets两者已经构成了一次数据库操作中读取的所有block的总次数了。因此，logical reads自然也就可以通过如下公式算的：logical reads = (db block gets + consistent gets) - physical reads。

由此，自然也就得出了cache命中率的公式：

Hit Ratio = (db block gets + consistent gets - physical reads) / (db block gets + consistent gets)

OR

Hit Ratio = 1 – (physical reads/(db block gets + consistent gets))