| 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.
> >
>
>
>
>
>
>
>
Jonathan:
I am looking at metalink note 260942.1 which provides another way of
getting explain plan without using dbms_xplan.display. I wnat to make
sure this does not create any latches problem. I do want to get plan's
from v$sql_plan but obviously cannot make productin database hung. My
program is very simple,
it does a loop of v$sql, then goes to v$sql_test and finally to
v$sql_plan (now using note 260942.1), Thanks a lot:
+++++++++++++++++++++++++++++++ Note 260942.1 ***************
Execution Environment:
SQL_PLUS
Access Privileges:
SELECT permission on V$SQL,V$SQL_PLAN and
V$SQL_PLAN_STATISTICS_ALL
Usage:
sqlplus <user>/<pw> @SCRIPTFILE
Instructions:
The script ask for the hash value of the SQL statement.
You can find this value in V$SQL or V$SQL_PLAN
The child_number is set to 0. When you get the wrong SQL statement you
have
also to change this number.
editors, e-mail packages, and operating systems handle text formatting
(spaces,
tabs, and carriage returns), this script may not be in an executable
state
when you first receive it. Check over the script to ensure that errors
of
this type are corrected.The script will produce an output file named
[outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
This script displays the SQL and the SQL execution plan that is in the
librarycache. This script does not work on 9.0 because the view
v$sql_plan_statistics_all does not exits. This part can be removed and
than the script works also on 9.0.
References
None
Script
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on numwidth
10;
alter session set "_complex_view_merging"=false;
select hash_value||decode(child_number, 0, '', '/'||child_number)
sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;
select '| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads
| Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */
select
'------------------------------------------------------------------------------------------------------------------------'from dual
select rpad('| '||substr(lpad(' ',1*(depth))||operation||
decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'||
rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'||
lpad(decode(starts,null,' ',
decode(sign(starts-1000), -1, starts||' ',
decode(sign(starts-1000000), -1,
round(starts/1000)||'K',
decode(sign(starts-1000000000), -1,
round(starts/1000000)||'M',
round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1,
cardinality||' ',
decode(sign(cardinality-1000000), -1,
round(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1,
round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(outrows,null,' ',
decode(sign(outrows-1000), -1, outrows||' ',
decode(sign(outrows-1000000), -1,
round(outrows/1000)||'K',
decode(sign(outrows-1000000000), -1,
round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(crgets,null,' ',
decode(sign(crgets-10000000), -1, crgets||' ',
decode(sign(crgets-1000000000), -1,
round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
lpad(decode(reads,null,' ',
decode(sign(reads-10000000), -1, reads||' ',
decode(sign(reads-1000000000), -1,
round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(writes,null,' ',
decode(sign(writes-10000000), -1, writes||' ',
decode(sign(writes-1000000000), -1,
round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(etime,null,' ',
decode(sign(etime-10000000), -1, etime||' ',
decode(sign(etime-1000000000), -1,
round(etime/1000000)||'M',
round(etime/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan" from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value = &hashvalue
and p.CHILD_NUMBER= 0
and p.hash_value = pa.hash_value(+)
and pa.child_number(+) = 0 )
union all
select
'------------------------------------------------------------------------------------------------------------------------'
from dual;
decode(object_node,null,'',
substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other "SLAVE SQL"
and hash_value = &hash_value
and CHILD_NUMBER= 0;
Received on Mon Oct 02 2006 - 20:11:40 CDT
 |
 |