DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
        b=nEiGlTWIGax8e5mjWFe5MpHMpNqWB5ryfPNaRlP8NaGXGFEcQDkN8jJbYL4TehgwPm3cqTyd3EbAzh4PPdVxJ1+6P+MwXG+nKmp33Y+GnSyPFoUkXbeFBV3Q9Owjs0CTE6FrpgxH2SDk2OURP+1hk+dFXo2YJQwpjno73+f1jB4=
Message-ID: <716f7a630709190834y48edd51fv392ddccdd5a3932b@mail.gmail.com>
Date: Wed, 19 Sep 2007 10:34:44 -0500
From: "Don Seiler" <don@seiler.us>
To: jonathan@jlcomp.demon.co.uk
Subject: Re: Weird database hanging
Cc: oracle-l@freelists.org
In-Reply-To: <E1IXzkf-000CBa-75@pr-webmail-2.demon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Disposition: inline
References: <716f7a630709181520p7ee8f2f4m9d4bfa7207c65d47@mail.gmail.com>
	 <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: don@seiler.us

Jonathan the symptoms you describe on that article describe what I'm
seeing to a tee.

Confio Ignite says that they, in fact, poll the x$ tables "every
second" in gathering their data.  They've assured me that it isn't a
factor.  I'm thinking I'll shut down the polling for now.

We are using JBOSS 4.0.5GA.  Does anyone have handy any configuration
options I should look at in terms of these connection pool issues?

My shared_pool_reserved_size is currently ~ 100mb (107374182).  The
SESSIONS parameter is set to 775, so if my math serves me, 775 * 26kb
= 19mb.  It would seem that I'm well stocked in reserved memory,
assuming I'm interpreting the parameter value correctly.

Don.

On 9/19/07, jonathan@jlcomp.demon.co.uk <jonathan@jlcomp.demon.co.uk> wrote:
>
> 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
>
>
>


-- 
Don Seiler
oracle: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l