Re: No one could logon to productio database for a while

From: <dbaplusplus_at_hotmail.com>
Date: 29 Sep 2006 05:30:43 -0700
Message-ID: <1159533043.098151.159990@m7g2000cwm.googlegroups.com>

DA Morgan wrote:
> dbaplusplus_at_hotmail.com wrote:
> > dbaplusplus_at_hotmail.com wrote:
> >> Sybrand Bakker wrote:
> >>> On 22 Sep 2006 21:50:24 -0700, dbaplusplus_at_hotmail.com wrote:
> >>>
> >>>> DA Morgan wrote:
> >>>>> dbaplusplus_at_hotmail.com wrote:
> >>>>>> DA Morgan wrote:
> >>>>>>> dbaplusplus_at_hotmail.com wrote:
> >>>>>>>> dbaplusplus_at_hotmail.com wrote:
> >>>>>>>>> I am using 9.2.0.5 on HP UNIX. I have an after logon trigger which
> >>>>>>>>> executes quickly - all it does it set some stored outline parameters
> >>>>>>>>> (trigger is provided below).
> >>>>> Nothing in inexplicable. I just built the trigger and procedure and it
> >>>>> leads me to wonder whether your assumption as to the root cause of your
> >>>>> problem is correct. I certainly don't see any issue.
> >>>>> --
> >>>>> Daniel Morgan
> >>>>> University of Washington
> >>>>> Puget Sound Oracle Users Group
> >>>>
> >>>> Are your saying trigger may not be the issue?. I also think so, but I
> >>>> do not know what else could cause this problem unless one is hitting in
> >>>> some internal oracle bug which happens once in blue moon.
> >>> Yeah nicely put, 'some internal oracle bug', on a patch release that
> >>> is more than a year old. Do you really think that this 'internal
> >>> oracle bug' wouldn't have surfaced?
> >>>
> >>> Obviously you can't login anymore when the number of processes has
> >>> been exceeded. Even SYS can't login anymore in that situation (this
> >>> didn't apply BTW to version 6, internal could *always* login).
> >>> Also, you need to check whether your number of semaphores on Unix
> >>> level is adequate ( 2 * #processes + 10). If it isn't, you can't
> >>> login.
> >>>
> >>> --
> >>> Sybrand Bakker, Senior Oracle DBA
> >> No of process was not a factor, because then one gets an Oracle error
> >> message instead of getting hung. I need to look into semaphores.
> >
> > My HP UNIX semaphore parameters are:
> > semmni 8000
> > semmns 12000
> > far higher than semmni 2 *1200 + 10
> > so I do not think semaphore is an issue.

>

> Not sure what docs you are using as the source of your numbers
> but mine is:
> http://download-west.oracle.com/docs/html/A96167_01/pre.htm#sthref106

>

> There the requirement is listed as:

>


> SEMMNI 	4096 	Defines the maximum number of semaphore sets in the entire

> system.

> SEMMNS 	(SEMMNI * 2) 	Defines the maximum number of semaphores in the

> system. The default value of SEMMNS is 128, which is, in most cases, too
> low for Oracle9i software.

>

> You might want to review the other kernel settings in SAM.
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

I noticed this problem all over again today. It probably comes from a process which uses a statement like:

select plan_table_output from TABLE( dbms_xplan.display('dynamic _plan_table','" + address + "_" + child_number + "'

Most of the time everything works fine, but sometime it seems to hang everything. This is a wild guess, does any on know any issues with this statement in the evrsion of Oracle 9.2.0.5 used.

code for script is quite simple, it does a v$sql to find all sql satemenmts in SGA,
then does v$sqltext to get complete sql satement and then calls dbms_xplan.display to show
the plan. Is there any other way to get the plan in a pretty fashion. Received on Fri Sep 29 2006 - 07:30:43 CDT