From: jonathan@jlcomp.demon.co.uk
To: oracle-l@freelists.org
In-Reply-To: <716f7a630709181520p7ee8f2f4m9d4bfa7207c65d47@mail.gmail.com>
Subject: Re: Weird database hanging
Date: Wed, 19 Sep 2007 14:38:01 +0100
User-Agent: Demon-WebMail/2.0
MIME-Version: 1.0
Content-Type: text/plain
Message-Id: <E1IXzkf-000CBa-75@pr-webmail-2.demon.net>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: jonathan@jlcomp.demon.co.uk


All the symptoms suggest that something is shutting
you out of the shared pool - so the comments from 
other posters about web applications and creating
sessions might be highly relevant.

There's a tale of woe here:
http://www.jlcomp.demon.co.uk/kiddy_scripts.html#_Always_be_cautious_about_x$_and_v$
that might be of interest.

If this is relevant, then check the shared_pool_reserved_size.
A connection needs a continguous chunk of about 26KB for 
"session parameters" - so it is a good idea to create a 
reserve that is set to a minimum of "sessions * 26KB" (plus
a few, or few dozen, MB for whatever you currently have in there) 
so thatthese chunk eventually end up being allocated in the 
reserve rather than causing a massive flushed on the rest of the pool.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http:/www.jlcomp.demon.co.uk


don@seiler.us wrote:
> I have opened an SR with Oracle, as it has hung 3 times today and
> actually crashed once.
> 
> When the database hang, Ignite is showing "latch: shared pool" and
> "latch: library cache" waits.  Otherwise I don't see these at all.
> 
> Oracle has had me up OPEN_CURSORS and SESSION_CACHED_CURSORS, but I
> did that last night (with instance restart) and, as I said, it has
> hung 3 times and crashed once since then.  Oracle's also telling me
> that this is largely due to application coding.  My problem with that
> is that the application code has been in place for a while.
> 
> To recap: we migrated to the 64-bit machine on Sep 1.  Hanging has
> occurred since Sep 13, seemingly during bulk load activity.  Our SGA
> is quite a bit larger (16 GB) than on our 32 bit box (1.5 GB).
> 
> db_cache_size                        big integer 12G
> shared_pool_size                     big integer 2G
> 
> I haven't seen the "block change tracking buffer space" wait since
> yesterday morning, thankfully.
> 
> Any tips would be appreciated.
> 
> Thanks,
> Don.
> 
> -- 
> Don Seiler
> oracle: http://ora.seiler.us
> ultimate: http://www.mufc.us
> 

--
http://www.freelists.org/webpage/oracle-l