Oracle Database Internals FAQ
Oracle Database Internals FAQ
Oracle Database Internals FAQ
[edit]What
ORADEBUG is a command that can be executed from SQL*Plus (or svrmgrl) to expose internal
information.
To obtain a list of valid ORADEBUG commands, execute ORADEBUG HELP. Here are some examples Trace SQL statements with bind variables:
SQL> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
SQL> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
SQL> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc
Trace Process Statistics:
SQL> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
The following (mostly undocumented) commands can be used to obtain information about internal
database structures.
Dump control file contents:
alter session set events 'immediate trace name CONTROLF level 10';
Dump file headers:
alter session set events 'immediate trace name FILE_HDRS level 10';
Dump redo log headers:
alter session set events 'immediate trace name REDOHDR level 10';
Dump the system state. One should take 3 successive SYSTEMSTATE dumps, with 10 minute intervals:
alter session set events 'immediate trace name SYSTEMSTATE level 10';
Dump process state:
alter session set events 'immediate trace name PROCESSSTATE level 10';
Dump Library Cache details:
alter session set events 'immediate trace name library_cache level 10';
Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool):
alter session set events '10053 trace name context forever, level 1';
Dump a database block (File/ Block must be converted to DBA address):
-- Convert file and block number to a DBA (database block address). Eg:
variable x varchar2;
exec :x := dbms_utility.make_data_block_address(1,12);
print x
alter session set events 'immediate trace name blockdump level 50360894';
Note: if you get ORA-02194, use this command instead:
alter system dump datafile 4 block 20;
[edit]What
Listed below are some of the important subsystems in the Oracle kernel. This table might help you to read
those dreaded trace files and internal messages. For example, if you see messages like this, you will at
least know where they come from:
OPIRIP: Uncaught error 447. Error stack:
KCF: write/open error block=0x3e800 >Kernel Subsystems:
OPI
KK
KX
K2
NPI
KZ
KQ
Query Layer
RPI
KA
Access Layer
KD
Data Layer
KT
Transaction Layer
KC
Cache Layer
KS
Services Layer
KJ
KG
Generic Layer
KV
The following list attempts to describe some of the x$ tables. The list may not be complete or accurate,
but represents an attempt to figure out what information they contain. One should generally not write
queries against these tables as they are internal to Oracle, and Oracle may change them without any
prior notification.
X$K2GTE2
X$K2GTE
X$BH
Buffer headers contain information describing the current contents of a piece of the buffer
cache.
X$KCBCBH
Cache Buffer Current Buffer Header Fixed Table. It can predict the potential oss of
decreasing the number of database buffers. The db_block_lru_statistics parameter has to be
set to true to gather information in this table.
X$KCVFH
X$KDNCE
X$KDNST
X$KDXHS
X$KDXST
X$KGHLU
X$KGLBODY
X$KGLCLUSTER
X$KGLINDEX
X$KGLLC
X$KGLPN
X$KGLTABLE
X$KGLTR
X$KGLTRIGGER
X$KGLXS
X$KKMMD
Fixed table to look at what databases are mounted and their status
X$KKSBV
X$KSMSP
X$KSQDN
X$KSQST
X$KSUCF
X$KSUPL
X$KSURU
X$KSQST
X$KTTVS
X$KVII
X$KVIS
X$KVIT
Instance internal flags, variables and parameters that can change during the life of an
instance
X$KXFPCDS
X$KXFPCMS
X$KZDOS
X$KZSRO
X$LE
Lock Element : each PCM lock that is used by the buffer cache (gc_db_locks)
X$MESSAGES
Displays all the different messages that can be sent to the Background processes
semmns = sum of the "processes" parameter for each instance (see init<instance>.ora for each
instance)
semmsl = semmns
[edit]What
A deadlock occurs when two or more users are waiting for data locked by each other. See ORA-00060 for
more information.
[edit]How
Oracle uses library cache pins to manage library cache concurrency. This FAQ outlines a method to deal
with "library cache pin" wait events that are blocking other users:
The first step is to see who is waiting for Library Cache Pins:
SQL> COL event FORMAT a20 TRUNC
SQL>
SQL> tti "Users Waiting for Library Cache Pins"
SQL> SELECT sid, event, p1raw, seconds_in_wait, wait_time
2
FROM sys.v_$session_wait
3
WHERE event = 'library cache pin'
4
AND state = 'WAITING'
5 /
Wed Aug 11
1
page
Users Waiting for Library Cache Pins
SID
---------374
944
1057
776
EVENT
-------------------library cache pin
library cache pin
library cache pin
library cache pin
P1RAW
SECONDS_IN_WAIT
---------------- --------------000000051862E5F0
1531
000000051862E5F0
10383
000000051862E5F0
10554
000000051862E5F0
2405
4 rows selected.
P1raw is the "Handle Address" of the object that is blocking. Execute the following query to get the
object's owner and name:
SQL> tti "Object that is Blocking"
SQL> COL owner format a8
SQL> COL object format a70
SQL> SELECT kglnaown AS owner, kglnaobj as Object
2
FROM sys.x$kglob
3
WHERE kglhdadr='&P1RAW'
4 /
Enter value for p1raw: 000000051862E5F0
old
3: WHERE kglhdadr='&P1RAW'
new
3: WHERE kglhdadr='000000051862E5F0'
Wed Aug 11
1
page
Object that is Blocking
OWNER
OBJECT
----------------------------------------------------------------------------begin SP_EMP.PROC1@orcl(:a,:b,:c); end;
Identify the users that are waiting/ blocking:
page
Blocking/Waiting Users
SID_SERIAL
Mode Held
Request
------------ ---------- ---------374,1390
0
2
776,2906
0
2
944,2193
0
2
991,59496
3
0
1057,1966
0
2
5 rows selected.
In the above example, session 991 is blocking the other sessions listed. Killing session 911 should solve
the problem. However, before killing the session, you may want to collect evidence of the problem so you
can log a TAR. To do so, collect 3 SYSTEMSTATE dumps at 30 seconds intervals, then submit them to
Oracle support for further analysis.
[edit]Where
Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported
parameters. One can get a list of all hidden parameters by executing this query:
select *
from
SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';
The following query displays parameter names with their current value: