The Oracle Magician
July 2003 Volume II, number 1
From the Editor
Welcome to the fourth is-
Diagnosing & Resolving
sue of the magazine, The
Oracle Magician! This Orphaned Shared Memory
Segment Issues
occasional newsletter fo-
cuses on various ―tricks of
the trade‖ in the Oracle
world--from DBAs, archi-
tects, developers, design-
ers, and report writers. By Brian Keating
Thank you to the many
notes and suggestions When an oracle instance starts up, processes, then sometimes the
from readers. They are it allocates a set amount of shared instance is not able to remove its
most appreciated!
memory, to be used for its SGA. shared memory segment when it
This area of shared memory is shuts down. In that case, the
In this issue we present an known as the "shared memory shared memory segment of the
article by Brian Keating segment" of the instance. (In rare instance remains on the system,
on how to identify and cases, an instance may actually even though the instance that
correct problems with have to create multiple shared created it is no longer running.
shared memory ―orphans.‖ memory segments, if the in-
In another brief article, we stance’s SGA is too large to fit
into a single segment.) Shared Memory “Orphans”
also describe a mysterious
phenomenon called ―Null
Normally, when an instance shuts This results in a so-called "or-
Events.‖
down, the instance will remove its phaned" shared memory segment.
The new Oracle tuning shared memory segment. Howev- This is important, because an or-
book, ―The Art and er, if an instance is shut down by phaned shared memory segment
Science of Oracle Perfor- executing the kill -9 command on from an instance will prevent you
mance Tuning,‖ was re- the instance’s background from restarting that instance. If
leased in February, and Continued on page 2
has met with favorable re-
views.
We are also looking for Table of Contents
writers to submit articles From the Editor ................................................................................................... Page 1
that explore interesting
Diagnosing Orphaned Shared Memory Segment Issues ........ Page 1
ideas of use to DBAs or
designers. The Mystery of “Null Events” ..................................................................... Page 4
Please send all ideas to Update: The Art & Science of Oracle Performance Tuning ... Page 4
Editor@OracleMagician.com
The Oracle Magician
Chris Lawson
Editor
Continued from page 1
you try to restart an instance while an removing an orphaned segment is
orphaned segment from it exists, you identifying which segment you need
will receive ―cannot attach to seg- to remove. You can display all of the
ment‖ errors. shared memory segments that exist on
a system with the "ipcs -m" com-
mand, but that command does not tell
An Example
you which instance owns each shared
memory segment.
Let's say you have an instance called
"abc123", that has a 50 meg SGA.
When the abc123 instance starts up, it There are some rather arcane oracle
will create a 50 meg shared memory debugging utilities that can be used to
segment to hold its SGA. If you shut determine which shared memory
down abc123 normally, then abc123 segment is owned by a particular in-
stance; but those utilities are difficult
The only non- will remove its shared memory seg-
(and time consuming) to use – espe-
ment during its shutdown process.
disruptive method cially on servers that have multiple
instances on them.
that can be used to However, if you run a kill -9 on
abc123's background processes, then
resolve the prob- it might not be able to remove its
A Better Way
lem is to manually shared memory segment when it shuts
down - which would leave that seg-
remove that seg- ment on the system, in an "orphaned" I have written a script that will dis-
play all of the oracle account’s shared
ment, with operat- state. If that segment is left in an or-
memory segments on a system, as
phaned state, then you will not be
ing system com- able to restart abc123 - because well as which instance owns each
shared memory segment. This script
mands abc123 will try to connect to the or-
is available on this web site by click-
phaned segment, rather than creating
a new segment. ing below.
Download Script
Removing the Orphans
The name of this script is "dsm" (for
If you get into an orphaned shared
"display shared memory").
memory segment situation, the only
non-disruptive method that can be
When you run the script, the ID num-
used to resolve the problem is to ma-
bers of all of the shared memory seg-
nually remove that segment, with op-
ments on the server are displayed,
erating system commands. (Reboot-
along with the instance that owns
ing the host machine will also remove
each segment.
orphaned segments, but rebooting a
whole server just to remove a single
The script also displays semaphores
orphaned segment is not a very viable
on the system. An orphaned sema-
solution.)
phore will not prevent an instance
The only difficult part about manually
Continued on page 3
2 The Oracle Magician
Continued from page 2
from starting; but it is a good idea to remove
orphaned semaphores as well. If you find an orphaned shared memo-
ry segment, you can manually remove
Here is an example of the output of that that segment with the following com-
command run on a server having many in- mand:
stances running:
ipcrm -m
$ dsm ALL
Dumping instance ipc informa- Orphaned semaphores can be removed
tion.........done with this command:
Shared memory segments:
ipcrm -s
Segment ID: Instance: For example, if the segment with the
1034 salff ID number of 228878 was in an or-
phaned state, you could remove that
4107 salcclp
segment with this command:
7173 clmstng
ipcrm -m 228878
140297 clmsprod
165900 comdb
If the semaphore with the ID of 38555
228878 dnbprod were in an orphaned state, it could be
removed with this command:
Semaphores:
ipcrm -s 3855
Semaphore ID: Instance:
38555 salff A Word of Caution
577832 clmsprod
Of course, you must not try to remove
821791 clmstng a segment if an instance is actually
using that segment - because that
2837673 salcclp
would almost certainly cause the in-
108703474 dnbprod stance to crash. Only remove a seg-
41486076 comdb ment (or semaphore) if the instance
for it is listed as UNKNOWN.
If a shared memory segment (or a sema- Brian Keating is a Senior Oracle DBA
phore) is in an "orphaned" state, then the consultant for Database Specialists, Inc.,
string "UNKNOWN" will be displayed un- of San Francisco. Brian can be reached
der the instance column for that segment. at Briankeating@Juno.com.
Oct, 2002 3
Oracle) will refer to the File# and Block ID
THE MYSTERY OF for a specific database object. Therefore, if
“NULL EVENTS” the P1 and P2 parameters for a given Null
Event correlate with an actual file # and
block ID of a table that you are reading, it is
Many DBAs are becoming familiar with the likely that the Null Event is really a disk-read
Oracle ―Wait Event‖ facility. Using some spe- event. (Use the DBA_EXTENTS table to
cial views, it is possible to find out what the look up File# and BLOCK_ID.)
database is waiting for. This has proven to be
an excellent diagnostic tool; in fact, many Your preliminary identification of the Null
DBAs begin their performance analysis by us- Event as a disk-read can gain further confi-
ing the Wait Event views. dence if different Null Events point to differ-
ent blocks within the same database object.
Unfortunately, there are some problems in In the case of the Scattered Read event, the
Oracle 9i that makes this facility a little harder P1 parameter will point to the file number,
for DBAs to use. To see why, let’s review how and the P2 parameter will indicate a different
the Wait facility is supposed to work. Block ID.
Ideally, any database process that is holding
things up is supposed to be listed by name in a
Book Update!
certain view, so that the DBA can easily identi-
fy problems. Thus, if the database is waiting on Chris’ new book, The Art and Science of Oracle
disk I/O, there will be an entry showing this
Performance Tuning was released in February of
―event,‖ along with statistics such as ―Wait
Time.‖ Clearly, this is extremely valuable in- this year. The reviews so far have been very en-
formation for the performance specialist. couraging, with each reviewer giving the book a
In Oracle 9i, however, it is very common for an ―5-STAR‖ rating.
event to be titled ―Null Event.‖ No, this
doesn’t mean that Oracle is waiting on ―noth-
ing.‖ Instead, this simply means that the Oracle The Art and Science of Oracle Performance
programmers goofed—they forgot to put a title Tuning is available at most large bookstores, or
on some events. So, instead of seeing the Wait
Event called ―Scattered Reads,‖ you might see online at Amazon.com
one called ―Null Event.‖ Obviously, this makes
it very difficult to diagnose performance prob-
lems, since the DBA has to guess on what the
event really is. The Oracle Magician
Fortunately, we have some extra information Editor: Chris Lawson
that can help unravel the puzzle. Although the
―mystery‖ event is not named properly, the pa- Contributing Editor:
rameters associated with the event (called P1, Brian Keating
P2, and P3) appear to still be correct. The exact
value of these parameters can give you clues on Layout: Wizard Press
what the event probably is.
For instance, the P1 and P2 parameters for a
multi-block read (called ―Scattered Read‖ by Copyright 2002 The Oracle Magician
4 The Oracle Magician