Re: Buffer Cache Hit ratio and Full Table scans

Probably the best book I've read that explains this stuff is:

Advanced Oracle Tuning and Administration by Oracle Press.

Details on
http://www.amazon.com/exec/obidos/ASIN/0078822416/o/qid=934598960/sr=2-2/002 -0957806-1355623

I recommend you buy a copy.

Frank Siegel wrote in message <7ouvba$rnk$1_at_bgtnsc01.worldnet.att.net>...
>This is a very in-depth question and quite long.
>Thank you for coming into this posting.
>I really appreciate your long and/or short answers.
>Thanks again so much in advance.
>
> **** **** *****
>
>My question is about where Oracle stores data blocks in a
>full table scan accessing multiple millions of rows and
>if operations like NESTED LOOP and MERGE/SORT SCANS store
>these blocks in the same place. Here are 2 quotes from
>my class material from the class Oracle8 performace and
>tuning. (By the way, for now my questions are concerning
>version 7.3.4, 32k block size, 450M buffer cache,
>8 blocks during multi block reads).
>
>
>Quote 1:
>"Do not continue increaseing DB_BLOCK_BUFFERS even
>when the last increase made no significant difference
>in the cache hit ratio. This may be because of the way
>you are accessing your data, or there may be other
>operations that do not even use the buffer pool. ie
>the Oracle server bypasses the buffer cache for sorting."
>
>Quote 2:
>"Also, when looking at the cache hit ratio, bear in
>mind that blocks encountered during a full table scan are
>not put to the head of the LRU list: therefore, repeated
>scanning does not cause blocks to be cached."
>
>
>QUESTIONS
>1. is the 'buffer pool' the same as the buffer cache?
>2. what are operations that do not use the buffer pool?
> such as sorts? full table scans?
>3. True or False? Full tablescans HAVE NO IMPACT in the
>cache hit ratio?, Please explain...they are put on the low
>end of the LRU.
>4. If blocks in a full tablescan are NOT put in cache:
> a. where are these blocks put? (I'm dealing with
>scanning millions of rows in a data warehouse app).
> b. If I'm in a NESTED LOOP where are the blocks stored?
> c. If I'm processing a SORT/MERGE where are the blocks
> stored? TEMP segment?
>
>
>Once again, Thanks for your help.
>Frank S.
>Keene, New Hampshire
>
>
>
Received on Fri Aug 13 1999 - 21:54:08 CDT