Database Reference
In-Depth Information
$ top
top - 22:04:20 up 54 min, 5 users, load average: 0.77, 0.76, 0.83
Tasks: 149 total, 2 running, 147 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.8% us, 41.0% sy, 0.0% ni, 34.9% id, 13.0% wa, 0.3% hi, 0.0% si
Mem: 1034584k total, 796000k used, 238584k free, 39272k buffers
Swap: 524280k total, 0k used, 524280k free, 502048k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13756
oracle 16 0 394m 68m 64m R 50 6.8 0:05.95 oracle
24887 oracle 15 0 391m 30m 27m S 14 3.0 0:21.68 oracle
SQL> ORADEBUG SETOSPID
13756
Oracle pid: 19, Unix process pid: 13756, image:
oracle@dbserver1.oradbpro.com (
TNS
V1- V3)
SETORAPID
SETORAPID
is useful in a scenario where you already know the session identifier (
V$SESSION.SID)
of a process that exhibits performance problems or hangs. By joining
V$SESSION
with
V$PROCESS
,
you obtain the ORACLE process identifier (
V$PROCESS.PID)
for attaching to the process. The
operating system process identifier
SPID
for use with
SETOSPID
may also be retrieved from
V$PROCESS
. This next example assumes that the process with
V$SESSION.SID=151
is of concern:
SQL> SELECT pid, spid
FROM v$process p, v$session s
WHERE s.sid=151 and s.paddr=p.addr;
PID SPID
---------- ------
19 15365
SQL> ORADEBUG SETORAPID 19
Unix process pid: 15365, image:
oracle@dbserver1.oradbpro.com (
TNS V1-V3)
Top Sessions in Oracle Enterprise Manager may be used to identify database sessions with
high resource consumption. Once you know
V$SESSION.SID
, you may use either
V$PROCESS.PID
with
SETORAPID
or
V$PROCESS.SPID
with
SETOSPID
. This next example is from a Windows system:
SQL> ORADEBUG SETOSPID 4172
Oracle pid: 16, Windows thread id: 4172, image: ORACLE.EXE (SHAD)
SQL> ORADEBUG SETORAPID 16
Windows thread id: 4172, image: ORACLE.EXE (SHAD)
When trace data collection is complete, switch off extended SQL trace with
ORADEBUG
EVENT
10046
TRACE
NAME
CONTEXT
OFF
.
ORADEBUG IPC
ORADEBUG
IPC
may be used to find out which UNIX shared memory segment(s) an ORACLE
instance uses. On platforms where ORACLE instances use semaphores for synchronization,
2
2.
On AIX the postwait kernel extension is used instead of semaphores.
