Re: Buffer Cache Hit ratio and Full Table scans

Hi Frank,

Let's try to answer your questions

1 yes
2 sorts are most likely to not use the buffer cache. The sort_area_size is (not using MTS) not in SGA, it is in the backend server process (Oracle is always two-task). If using MTS, the sort_area is in the shared pool, which is not the same as the buffer cache
3 False. The results of a full table scan are placed low on the LRU list to prevent this. This placement means depending on database activity, they will almost immediately get aged out/thrown away. 4 b They are placed in the buffer cache

   c as soon as the size of your sort exceeds the sort_area_size parameter, yes they do.

Also bear in mind that increasing DB_BLOCK_BUFFERS may induce the O/S to excessive paging. I had this experience on a NT server once. It look liked the database was running smoothly, however this was reversed by the paging of the O/S

Hth,

Sybrand Bakker, Oracle DBA

Frank Siegel <NorthernSnow_at_worldnet.att.net> wrote in message news: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 Thu Aug 12 1999 - 13:46:57 CDT