大量逻辑读会影响数据库性能么

Yong Huang

A logical read literally is indeed a read from a buffer in the buffer cache:
http://docs.oracle.com/database/122/CNCPT/glossary.htm#CNCPT94718
"logical read
A read of a buffer in the database buffer cache."

But in practice, as in calculating statistics, the count of logical reads includes the number of normal physical reads. (In most contexts, this physical read refers to the number of blocks, not the number of times of reading, from the datafile. The two values may differ because of multiblock reads.) This is because if Oracle doesn't find a block in the buffer cache, it copies the block from the datafile into buffer cache and then reads it. My earlier statement "Logical reads are sum of buffer cache reads and physical reads, not just buffer cache reads." posted in 2014 was not clear about this, or can be considered misleading. It's only correct when interpreted as saying, e.g. "Out of 1000 logical reads of this table, 800 are directly from buffer cache and 200 are from the datafile first, followed by reading (i.e. actually examining the contents of) these 200 memory buffers."

Tom Kyte said
https://asktom.oracle.com/pls/as ... ON_ID:6643159615303
"LIO = logical = buffer cache. LIO *may* have incurred a PIO in order to get into the cache in the first place. "
That's exactly what I meant. Later in the thread, we see him say
--- begin quote ---
If I ask a query like:
select * from emp where empno = :x
I would expect 3 logical IOs for the index and one for the table. I would expect 4 logical IO's
--- end quote ---
I don't think he was making a distinction between buffer cache read and physical read when he says 3 or 4 "logical IOs" here.

Also, according to
https://docs.oracle.com/cd/E2462 ... abase.htm#EMDBM1534
"The data [for calculating Buffer Cache Hit (%)] is derived from the ((DeltaLogicalGets - (DeltaPhysicalReads - DeltaPhysicalReadsDirect)) / DeltaLogicalGets) * 100 formula"
We see that the word "logical" includes "physical", at least in the phrase "logical get" as compared to "physical get". If "logical" and "physical" were mutually exclusive in this formula, logical gets minus physical gets would make no sense.

bnso

首先，不管逻辑读还是物理读，多了都不好。
逻辑读多通常反应为cpu负载高、物理读多io会忙。

YongHuang说的那些，我觉得是在创新概念。
逻辑读要么理解为一致性读+当前模式读，一般意义上的就是buffercache读也没有问题。

小颜Kevin

学习了，我也一直以为逻辑读就是buffer读。