Buffer Cache Hit ratio and Full Table scans

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:

1. where are these blocks put? (I'm dealing with scanning millions of rows in a data warehouse app).
2. If I'm in a NESTED LOOP where are the blocks stored?
3. 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 Thu Aug 12 1999 - 12:08:01 CDT