RE: sequential read on full-table scan?

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Wed, 25 May 2005 00:23:50 -0700 (PDT)
Message-ID: <20050525072350.36130.qmail@web52604.mail.yahoo.com>

Steve,

That is exactly why Oracle did not want to put the code to check for collisions every time MBR disk I/O read is happening.

The only time this is happening is direct path reads where it is nothing to do with the cache but rather PGA removing the collisions alltogether.

This is somehow similar to some RAC cache fusion problems.
Just imagine if the block is dirty in the cache while you are getting the same block with MBR from the disk.

What is Oracle going to do?
Oracle should then put something similar as the RAC code to manage this situation, downgrade locks, .... who knows what :)
Or what if the table is said to be cached while not all blocks still put into the cache? Or million other situations that nobody knows.
And not to mention their C code.
They have a lot of bugs to fix in 9.2.0.6 anyway :)

To not mention that you did not pay for RAC :)

Joking of course, but it looks that it is a big trade off for Oracle.

I am using much more parallel 2 to do the FTS for the driving table, because it is so much faster while hopefuly you are not going to kill your "good" disk I/O subsystem with just parallel 2.
Also, it is ideal for history data you do not want to be cached anyway.
I used to work with 130G buffer cache and the difference in FTS with direct path reads (parallel)and without were drastic.

Regards,
Zoran

• Steve Rospo <srospo_at_vallent.com> wrote:
  >
  > I've known about this but it got me thinking: When
  > Oracle determines it
  > needs a multiblock read doesn't it have to calculate
  > eight hashes
  > (assuming multiblock_read=8) and probe each of the
  > lru hash chains in
  > order to determine if the block is in the cache or
  > not? Isn't this a lot
  > of overhead? Why not just read all 8 blocks and
  > just handle the colision
  > when it happens? It seems to me a single 8 block
  > read would be more
  > efficient than say a 4-block read and a 3-block
  > read.
  >
  

  ---

Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 25 2005 - 03:28:35 CDT