Re: db_cache_size and cache buffer chain waits

This looks symptomatic of a problem with using a temporary table in a join. Because of anomalies with statistics on temporary tables, Oracle has chosen a completely mad execution plan - e.g. involving the temporary table as the inner table of a nested loops join.

(Not guaranteed, but reasonably likely).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 26 March 2003 19:24

>
> >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: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).