Re: switching to 'direct path read' should help prevent thrashing buffer cache ?

p.santos000_at_gmail.com wrote:
> Maybe I misinterpreted what you said, but I was under the impression
> that
> an update to a table with a ROWNUM <=500000 will lock only the 500000
> affected rows... likewise a parallel update with a ROWNUM <=500000 will
> also
> lock those 500K records .. but you seem to indicate that it's not the
> case.
>
> I'll have to retest, but I believe I saw tx row lock contention and I
> would have
> thought that if the whole table was locked, I would have seen a
> different wait
> event.
>
> Regarding the update frequency by customers.. some will do these update
> on regularly weekly intervals, others do it ad hoc, and others will do
> a combination
> of both.
>
> One final thing, you said
> " buffer cache was probably not being thrashed because of the way
> tablescans of large
> tables work".
>
> When I run a 10046 trace on this, the largest and most significant wait
> event
> is "db file scattered read". Can you briefly explain how tablescans
> of large tables
> work ?
>
> thanks again
> -peter

I can't answer for Jonathan. However, a search through Oracle documentation for LRU::
  http://www.oracle.com/pls/db102/search?word=LRU

The first hit returns this page:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm

Scrolling down to "Database Buffer Cache", you will find a description of how the MRU (most recently used) list and LRU (least recently used) list work in the buffer cache. The next heading describes "LRU Algorithm and Full Table Scans"

I suspect that you are seeing "db file scattered read" wait events due to the volume of table data being processed during the full table scan.  You stated that your buffer cache is 3GB in size, and the table data ranges from 1GB to 10GB in size - very likely, little of those tables' blocks were already in the buffer cache prior to the start of the updates.

Another reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jan 03 2007 - 12:47:37 CST