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

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

Don't do this to v$sql_plan this aggressively.

Even using dbms_xplan.display() against a single cursor in 9i hammers the libray cache and shared pool latches - it's not surprising that you managed to freeze your instance by trawling v$sql and v$sql_text for everything in sight.

The problem with dbms_xplan in 9i is that it does a hierarchical query (connect by) against the target table. If you've faked a view that makes V$sql_plan the target get, the impact on latching is extreme.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html