| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: No one could logon to productio database for a while
Jonathan Lewis wrote:
> <dbaplusplus_at_hotmail.com> wrote in message
> news:1159533043.098151.159990_at_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.
> >
>
>
>
>
>
>
>
To add when I do v$sql, I am doing only for my stamenets, i.e, satements for a specifc schema., i.e., parsing_user_id = 'myschema', so Oracle is probably running out of latches and unless latch is relased everything is hung including my shadow process. Received on Sun Oct 01 2006 - 11:26:24 CDT
 |
 |