Re: serial direct reads

From: joel garry <>
Date: Thu, 28 Aug 2014 08:43:03 -0700 (PDT)
Message-ID: <>

On Thursday, August 28, 2014 1:41:02 AM UTC-7, vsevolod afanassiev wrote:
> I have seen several examples of the direct reads issue. This is what typically happens:
> - there is a medium-sized table, let's say 500 MB. It is less than 10% of the db_cache_size (7 GB).
> - the table is frequently accessed through full table scan (FTS). It takes 0.5 second as most blocks are in the buffer cache
> - as this table grows its size exceeds 10% of the buffer cache and Oracle switches to direct reads. Now the same query takes 15 seconds

Imagine what would happen if the storage was intelligent and winnowed down the results before delivering them to the pga. Imagine how silly it would be to assume that capability and not have it.

Big fan of Tanel here.

> If this was small table then the blocks were sent to the most recently used end of the LRU list and so they were kept in the buffer for longer. If this was large table then the blocks were sent to another end of the list and expired sooner.

blocks go to middle of lru list jonathan lewis and you are likely to find explanations that this hasn't been true since 8.1. (apologies if there is something that makes it true for your case that I'm not thinking of, this is of course something for test cases to show)


-- is bogus.
A billion here, a billion there, pretty soon you're talking about real money. Everett Dirkson
Received on Thu Aug 28 2014 - 17:43:03 CEST

Original text of this message