Re: db_cache_size and cache buffer chain waits

Jonathan Lewis wrote:

>However, it is possible that you managed to
>change the number of db block buffers available
>quite dramatically - which could have an effect
>on the size at which a table is considered to be
>a 'small' table, which could have an impact on
>the actual resource usage for the execution of
>a specific plan.
>

Didn't make much of a difference whether the buffer cache was set to the default (too small) 48m or 150m or 300m.

>An alternative explanation - given the change
>in performance when you changed from a
>temporary table to a permanent table is the
>issue of getting statistics generated for a
>temporary table. If there are significant
>differences in the execution plans involving
>the critical table, you could investigate the
>use of the dynamic_sampling hint (level 2
>is probably appropriate for the temp table)
>to improve Oracle's knowledge of the contents
>of the temporary table.
>

I'm going to run some tests on this to see the change in execution plans under a variety of scenarios from change in table type to the change in buffer cache parameters. Would the way Oracle treated the temporary table differ significantly merely from the change in the buffer cache configuration?

>NB - when you say 'hanging' does you mean:
> The CPU is working hard but the process
> is not completing
>

The above is the condition I observed. There was a select cursor that joined the temp table to several others that was showing the cache buffer chain waits; the update cursor, that updates rows in the temp table did not exhibit waits.

--Glenn Stauffer

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Glenn Stauffer
  INET: stauffer_at_swarthmore.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).