|
HP OpenVMS Systemsask the wizard |
|
The Question is: Is it possible to prevent PAGEDYN becoming to fragmented during Oracle processing. LNM-blocks in PAGEDYN growing to more than 70% and would never be released again. The Answer is :
The OpenVMS Wizard suspects you might be accumulating logical name
tables -- please contact Oracle for a simple fix for this (known)
leak in Oracle. Please also contact Oracle for any issues that
might be specific to the use and the operation of Oracle software.
Here is a command procedure that can clean up the stale logical name
tables that can accumulate:
$!
$! Command procedure to clean up logical name tables left by Oracle
$! processes in order to recover lost PAGEDYN
$!
$! Reported against Oracle V7.1.4
$!
$! When an Oracle process is created, a logical name table is also created
$! to pass information to the new process. Since this is a permanent,
$! shareable table, it is not deleted when the process terminates. Over
$! time, the accumulation of logical names will deplete PAGEDYN to the point
$! that the system hangs or crashes.
$!
$! The logical name tables have names of the form TNS_xxxxxxxx where xxxxxxxx
$! is the PID of the process for which it was created. It is therefore quite
$! straightforward to detect "orphaned" tables by testing to see if the
$! owner process still exists.
$!
$! WARNING - The SHOW LOGICAL/STRUCTURE command is used to generate a list
$! of all shareable logical name tables on the system. Since the command
$! takes out the LNM spinlock for the duration of the command, it will
$! hang most user processes for its duration. If there is a large accumulation
$! of logical name tables, this can be a significant time. One report was
$! for a hang of 20mins when displaying 10MB of logical names.
$!
$! If this procedure is executed frequently, the duration of the hang should
$! be minimal. Note that the size/response time of the command is non-linear.
$!
$! Requires SYSPRV and/or BYPASS privilege
$!
$! Author: John Gillings
$! Systems Software Consultant
$! Compaq Customer Support Centre
$! Sydney, Australia
$!
$! email: John.Gillings@Compaq.com
$!
$! PROCEDURE
$!
$! First list all shareable logical name tables
$!
$ SHOW LOGICAL/STRUCTURE/OUT=LNMLIST.TMP
$!
$! Filter for Oracle logical name tables
$!
$ SEARCH LNMLIST.TMP "(TNS_" /OUT=ORACLE_TABLES.TMP
$ DELETEXX LNMLIST.TMP;
$ OPEN/READ in ORACLE_TABLES.TMP
$!
$! Scan Oracle tables
$!
$ loop: READ/END=Finished/ERROR=Finished in Table
$!
$! Get the table name by removing all whitespace and brackets
$!
$ Table=F$EDIT(Table,"COLLAPSE")-"("-")"
$!
$! Extract PID from table name
$!
$ pid=F$ELEMENT(1,"_",Table)
$!
$! Ignore if process still exists. If process does not exist, the
$! F$GETJPI call will fail with %SYSTEM-W-NONEXPR, nonexistent process
$! and thus fall through. We will also fall through if there is any
$! mismatch between the input and output PIDs. This should never
$! happen, but it's safer code.
$!
$ ON WARNING THEN CONTINUE
$ IF F$GETJPI(pid,"PID").EQS.pid THEN GOTO loop
$!
$! Sanity check PID format, make sure it's a valid, non-zero, 8 digit
$! hex string. F$INTEGER will return 0 if there are any non-numeric
$! characters in the input string
$!
$ IF F$INTEGER("%X''pid'").EQ.0.OR.F$LENGTH(pid).NE.8
$ THEN
$!
$! Don't know what this table is, output message about it
$!
$ WRITE SYS$OUTPUT "Unrecognised table name ''Table'"
$ ELSE
$!
$! Passed all tests, this must be an orphaned logical name table.
$!
$! Delete the logical names in table and the table itself.
$! Note that the DEASSIGN/ALL should be redundant, but again, it's
$! safer to be explicit
$!
$ WRITE SYS$OUTPUT "Deleting table ''Table'"
$ DEASSIGN/ALL/TABLE='Table'
$ DEASSIGN/USER/TABLE=LNM$SYSTEM_DIRECTORY 'Table'
$ ENDIF
$ GOTO LOOP
$ Finished:
$ CLOSE in
$ DELETE ORACLE_TABLES.TMP;
$ EXIT
|