Re: db_cache_size and cache buffer chain waits

[Glenn Stauffer]

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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).