logical reads vs physical (zt from asktom)

please correct me if my understanding is wrong.
LIO - from/to the buffer cache ( not disk ).
PIO - to/from the disk.
correct ?

in many of the threads on tuning i notice that you mention, the less the number
of PIO as well as LIO the better. i can understand the 'physical' part of it but
not the logical.
the query has to get the results and it has to do as much work as is necessary
to get it, isnt it ? so at least that much of LIO is a must. how can we reduce
that ?


Followup:
LIO = logical = buffer cache. LIO *may* have incurred a PIO in order to get
into the cache in the first place.

PIO = physical = disk read

Consider this example. Pretend I am one of those people (of which there are
many) that believe "if my query ain't using an index, it is broken". So, I have
a table and query:

big_table@ORA817DEV.US.ORACLE.COM> select object_id, object_name from big_table
order by object_id
2 /

1576192 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9086 Card=1576192
Bytes=47285760)
1 0 SORT (ORDER BY) (Cost=9086 Card=1576192 Bytes=47285760)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=658 Card=1576192
Bytes=47285760)




Statistics
----------------------------------------------------------
0 recursive calls
117 db block gets
22861 consistent gets
physical reads
0 redo size
63003739 bytes sent via SQL*Net to client
11664194 bytes received via SQL*Net from client
105081 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1576192 rows processed

Now, being that person who ferverently believes "full scan = evil, index =
goodness", I "fix" the optimizer via:

big_table@ORA817DEV.US.ORACLE.COM> select /*+ index( big_table big_table_idx2 )
*/ object_id, object_name
2 from big_table order by object_id
3 /

1576192 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1579626 Card=1576192
Bytes=47285760)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=1579626
Card=1576192 Bytes=47285760)
2 1 INDEX (FULL SCAN) OF 'BIG_TABLE_IDX2' (NON-UNIQUE) (Cost=3434
Card=1576192)




Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1684470 consistent gets
physical reads
0 redo size
63003739 bytes sent via SQL*Net to client
11664194 bytes received via SQL*Net from client
105081 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1576192 rows processe

Now, same answer -- same number of rows -- which is better? (yes, I purposely
removed PIO's -- assume they are the same).

Each consistent get is a latch, latches = contention. Over abused indexes can be
a killer for scaling. A runstats of the above showed me:

FULL SCAN INDEX
LATCH.cache buffers chains 67428 6547789 6480361

6.4 million more cbc latches - ugg.

Each consistent get is a latch, latches = contention

---Can you elaborate that a little? Thank you.


Followup:

to get a block from the cache, we have to latch (to prevent someone from
modifying the data structures we are currently reading).

A latch is a lock.

Locks are serialization devices

Serialization devices inhibit scalability, the more you use them, the less
concurrency you get.

Reviewer:  Connor McDonald  from UK

On a somewhat related note, there is an oracle 9 enhancement when probing via an
index in a nested loop.  The index probing is still done as described in this
question, but when it comes to looking up the table, Oracle can choose to defer
that read until its retrieved all of the rowid from the index.

Then it sorts the rowids so that it only has to visit each table block once.
Very nifty...


Latching and Consistent Gets  December 30, 2002
Reviewer:  Ramakrishna  from India

Dear Tom,
  I have a small doubt with the way latching, consistent gets and arraysize work
together.  Could you kindly clarify?
  If I am doing a full table scan, Oracle will do the following:
1.  Compute the hash address of the table block that is to be read.
2.  Take a latch on the chain in the cache buffers hash table where this block
resides
3.  Read upto arraysize rows from the table block (this counts as one logical
I/O)
4.  Release the latch
Is this correct?

  With an index scan, I am confused about how this happens in 8i (Connor above
has mentioned how this happens in 9i).  Suppose I have an index block that is
well clustered and all the rowids in that block point to the same data block. 
In this case, does Oracle:
1.  Compute the hash address of the table block corresponding to the first row
in the index block.
2.  Take a latch on the chain in the cache buffers hash table where this block
resides
3.  Read that first row value (one logical I/O)
4.  Does Oracle release the latch on the table block here?  Or, does it hold on
to it till it has fetched upto arraysize entries from the index block?  If the
latter, then what happens if I had a query that did a
SELECT COUNT(*) FROM (SELECT something via index range scan from table)?
In this case, since arraysize doesn't matter, are the latches held till the
entire range scan is complete?  But, if that is the case, what if there is
another query that is reading the same blocks 'backwards'?  How does it all work
without deadlocking?

It would be great if you could explain how the latching and logical IO is done
for an index range scan.

thanks in advance,
Ramakrishna



 


Followup: 
as for the full scan first example -- conceptually correct, yes.

Now, Connor was talking NL joins which is different from just an index range
scan.  NL joins are bunches of separate index range scans.


The index range scan works like this:

o if the index is very well clustered (organized) the same as the table -- then
array fetching will do the consistent gets much like a full table scan would. 
That is, if the TABLE is ordered the same as the INDEX is ordered -- we'll get
an index block that has our first value -- latch the table block that has that
and keep that table block until either we hit arraysize rows or the index entry
tells us to move onto a different database block.

o if the index is sorted very differently from the table data -- you'll see
higher consistent gets since we jump from table block to table block as we go
from row to row.

Here is a quick and dirty example:


ops$tkyte@ORA817DEV> create table t as select * from all_objects order by
object_id;

Table created.

ops$tkyte@ORA817DEV> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set autotrace traceonly explain

ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_PK' (UNIQUE)



ops$tkyte@ORA817DEV> set autotrace traceonly statistics

ops$tkyte@ORA817DEV> set arraysize 10
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.


Statistics
----------------------------------------------------------
       5083  consistent gets

ops$tkyte@ORA817DEV> set arraysize 100
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.


Statistics
----------------------------------------------------------
        846  consistent gets

ops$tkyte@ORA817DEV> set arraysize 500
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.


Statistics
----------------------------------------------------------
        472  consistent gets

ops$tkyte@ORA817DEV> set autotrace off



ops$tkyte@ORA817DEV> drop table t;
Table dropped.

ops$tkyte@ORA817DEV> create table t as select * from all_objects order by
dbms_random.random;
Table created.

ops$tkyte@ORA817DEV> alter table t add constraint t_pk primary key(object_id);
Table altered.

ops$tkyte@ORA817DEV> set autotrace traceonly explain

ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_PK' (UNIQUE)



ops$tkyte@ORA817DEV> set autotrace traceonly statistics

ops$tkyte@ORA817DEV> set arraysize 10
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.

Statistics
----------------------------------------------------------
      26065  consistent gets

ops$tkyte@ORA817DEV> set arraysize 100
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.


Statistics
----------------------------------------------------------
      23934  consistent gets

ops$tkyte@ORA817DEV> set arraysize 500
ops$tkyte@ORA817DEV> select object_name from t where object_id > 0;
23712 rows selected.

Statistics
----------------------------------------------------------
      23744  consistent gets

ops$tkyte@ORA817DEV> set autotrace off



see how in the second case the consistent gets didn't go down significantly --
regardless of the array size.  Each row caused us to hop from block to block to
block -- every row fetched via the index was another consistent get
 

[@more@]

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

转载于:http://blog.itpub.net/94317/viewspace-794503/