參考：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 結論
可以從統計資訊中看到，當執行的查詢僅需軟解析並且能夠從緩衝區快取中讀取資料塊時，執行任務所耗用的資源是最少的。
我們的目標應該永遠是開發出能夠更多地重用共享池和緩衝區快取中資訊的程式碼。

硬解析和物理讀取與軟解析和邏輯讀取

相關文章