【翻译自mos文章】在tkroof 中为什么物理读要比逻辑读要多

 

参考自：

Why Physical Read is greater than Logical Read in Tkprof (文档 ID 211700.1)

PURPOSE 

In some circumstances, you can find that tkprof report shows more physical reads than logical reads, which isn't the current result as the physical reads are normally included in logical reads. 

SCOPE & APPLICATION

This article will be useful for the DBA's and customers who are concerned by the tuning of Requests. 

Why Physical reads are greater than Logical reads


Sometimes, you can find the following content in tkprof report: 

•  Physical Reads = Disk (total)
•  Logical Reads = Query (total) + Current (total)

call	count	cpu	elapsed	disk	query	current	rows
-------	------	--------	----------	----------	----------	----------	----------
Parse	1	0.67	1.10	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	2202	167.48	678.70	579441	283473	17418	33014
-------	------	--------	----------	----------	----------	----------	----------
total	2204	168.15	679.81	579441	283473	17418	33014

The 'disk' column is then greater than the 'query' + 'current' columns. This isn't usual. 

To find the root cause of the problem, you must generate a 10046 event trace file level 8 and check for direct read waits in it. 
In 10046 raw trace, you will find " direct path read " and " direct path write " waits like the example below: 

WAIT #1: nam='direct path read' ela= 10076 p1=4 p2=29035 p3=1 

with  P1 = file# ,  P2 = start block# ,  P3 = num blocks

The " direct path read " waits account explains the difference between logical and physical reads. 

In Oracle 9.2 and above, TKProf will print waits associated with each SQL statement in the output file. 

Explanation:

The reason for more physical reads than logical reads is due to the number of direct reads block access. Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).
 
They may happen on different actions:  

•  Sort IO on disk.
•  Read by parallel Query Slaves when scanning is done.
•  Blocks read by anticipation (readahead).

Such reads are done without loading blocks into the Buffer Cache. They can be single or multiblock reads. 
Utilizing Direct Path Reads in this manner prevents the Oracle Buffer cache from beeing overloaded. 
Oracle uses this optimisation when it considers that its not necessary to share the blocks between different sessions.

References:

Note:32951.1  Tkprof Interpretation