硬解析和物理读取与软解析和逻辑读取

参考：http://www.cnblogs.com/chinhr/archive/2009/03/14/1412100.html
001 预备知识
·Recursive Calls：有时为了执行用户发出的一条SQL语句，Oracle必须执行额外的语句。这样的额外的语句被称为递归调用或者递归SQL语句。例如，如果你想往表中插入一行，但是该表没有足够的空间来容纳这行，这个时候Oracle就会使用递归调用来自动分配空间。

·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. 

·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. 

·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. 

·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. 

002 
    本文通过在3种不同场景下多次执行同一语句的区别。第一个例子在语句执行之前清空了共享池和缓冲区缓存，这意味着该语句将会被硬解析，包含该查询所需数据的块（以及为了完成硬解析所需的所有关于系统对象的查询）需要从磁盘上物理读取。第二个例子展示了如果仅清空缓冲区缓存将会发生什么。最后一个例子展示的是共享池和缓冲区都不清空的场景。

2-1 在语句执行之前清空共享池和缓冲区缓存
SYS@PROD1> alter system flush buffer_cache;

System altered.

SYS@PROD1> alter system flush shared_pool;

System altered.

SYS@PROD1> set autotrace traceonly statistics
SYS@PROD1> select * from hr.employees where department_id=60;

Statistics
----------------------------------------------------------
161  recursive calls
 0  db block gets
239  consistent gets
26   physical reads
 0  redo size
1647  bytes sent via SQL*Net to client
524  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 9  sorts (memory)
 0  sorts (disk)
 5  rows processed

SYS@PROD1> set autotrace off

2-2 仅清空缓冲区缓存
SYS@PROD1> alter system flush buffer_cache;

System altered.

SYS@PROD1> set autotrace traceonly statistics
SYS@PROD1> select * from hr.employees where department_id=60;

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


2-3 共享池和缓冲区都不清空
SYS@PROD1> select * from hr.employees where department_id=60;

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

SYS@PROD1> set autotrace off

003 结论
     可以从统计信息中看到，当执行的查询仅需软解析并且能够从缓冲区缓存中读取数据块时，执行任务所耗用的资源是最少的。
我们的目标应该永远是开发出能够更多地重用共享池和缓冲区缓存中信息的代码。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29067253/viewspace-2054658/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29067253/viewspace-2054658/