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

Charles Hooper wrote:
> 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.

thanks for the links ..
-peter Received on Fri Jan 05 2007 - 10:26:47 CST