Oracle database Internals FAQ
From Oracle FAQ
Oracle database Internals FAQ:
[edit] Wat is ORADEBUG and how does one use it?
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 SQL> oradebug procstat Statement processed. SQL> oradebug TRACEFILE_NAME /app/oracle/admin/orcl/bdump/pmon_1436.trc
List semaphores and shared memory segments in use:
SQL> oradebug ipc
Dump Error the Stack:
SQL> oradebug setospid <pid> SQL> oradebug event immediate trace name errorstack level 3
Dump Parallel Server DLM locks:
SQL> oradebug lkdebug -a convlock SQL> oradebug lkdebug -a convres SQL> oradebug lkdebug -r <resource handle> (i.e 0x8066d338 from convres dump)
It looks like one can change memory locations with the ORADEBUG POKE command. Anyone brave enough to test this one for us?
Previously the above functionality was available with the ORADBX command. The command sequence to link this utility was:
ls -l $ORACLE_HOME/rdbms/lib/oradbx.o; make -f oracle.mk oradbx
[edit] How does one dump internal database structures?
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 Kernel Subsystems are available?
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 online=1
Kernel Subsystems:
| OPI | Oracle Program Interface |
| KK | Compilation Layer - Parse SQL, compile PL/SQL |
| KX | Execution Layer - Bind and execute SQL and PL/SQL |
| K2 | Distributed Execution Layer - 2PC handling |
| NPI | Network Program Interface |
| KZ | Security Layer - Validate privs |
| KQ | Query Layer |
| RPI | Recursive Program Interface |
| KA | Access Layer |
| KD | Data Layer |
| KT | Transaction Layer |
| KC | Cache Layer |
| KS | Services Layer |
| KJ | Lock Manager Layer |
| KG | Generic Layer |
| KV | Kernel Variables (eg. x$KVIS and X$KVII) |
| S or ODS | Operating System Dependencies |
[edit] What are in all those X$ tables?
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 | Kernel 2 Phase Commit Global Transaction Entry Fixed Table |
| X$K2GTE | Kernel 2 Phase Commit Global Transaction Entry Fixed Table |
| 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 | File Header Fixed Table |
| X$KDNCE | SGA Cache Entry Fixed Table |
| X$KDNST | Sequence Cache Statistics Fixed Table |
| X$KDXHS | Histogram structure Fixed Table |
| X$KDXST | Statistics collection Fixed Table |
| X$KGHLU | One-row summary of LRU statistics for the shared pool |
| X$KGLBODY | Derived from X$KGLOB (col kglhdnsp = 2) |
| X$KGLCLUSTER | Derived from X$KGLOB (col kglhdnsp = 5) |
| X$KGLINDEX | Derived from X$KGLOB (col kglhdnsp = 4) |
| X$KGLLC | Latch Clean-up state for library cache objects Fixed Table |
| X$KGLPN | Library cache pin Fixed Table |
| X$KGLTABLE | Derived from X$KGLOB (col kglhdnsp = 1) |
| X$KGLTR | Library Cache Translation Table entry Fixed Table |
| X$KGLTRIGGER | Derived from X$KGLOB (col kglhdnsp = 3) |
| X$KGLXS | Library Cache Access Table |
| X$KKMMD | Fixed table to look at what databases are mounted and their status |
| X$KKSBV | Cursor Cache Bind Variables |
| X$KSMSP | Each row represents a piece of memory in the shared pool |
| X$KSQDN | Global database name |
| X$KSQST | Enqueue statistics by type |
| X$KSUCF | Cost function for each Kernel Profile (join to X$KSUPL) |
| X$KSUPL | Resource Limit for each Kernel Profile |
| X$KSURU | Resource Usage for each Kernel Profile (join with X$KSUPL) |
| X$KSQST | Gets and waits for different types of enqueues |
| X$KTTVS | indicate tablespace that has valid save undo segments |
| X$KVII | Internal instance parameters set at instance initialization |
| X$KVIS | Oracle Data Block (size_t type) variables |
| X$KVIT | Instance internal flags, variables and parameters that can change during the life of an instance |
| X$KXFPCDS | Client Dequeue Statistics |
| X$KXFPCMS | Client Messages Statistics |
| X$KZDOS | Represent an os role as defined by the operating system |
| X$KZSRO | Security state Role: List of enabled roles |
| 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 |
| X$NLS_PARAMETERS | NLS database parameters |
Some handy X$table queries:
Some handy queries based on the X$ memory tables:
Largest number of blocks one can write at any given time:
select kviival write_batch_size from x$kvii where kviitag = 'kcbswc';
See the gets and waits for different types of enqueues:
select * from x$ksqst where KSQSTSGT /*col name was ksqstget before 10g*/ > 0;
[edit] What is the difference between locks, latches, enqueues and semaphores?
A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
- semmns = sum of the "processes" parameter for each instance (see init<instance>.ora for each instance)
- semmni = number of instances running simultaneously;
- semmsl = semmns
[edit] What is a deadlock and how does one fix deadlock errors?
A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing.
Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also write out a trace file with detailed information to the database's UDUMP directory.
Multi-table deadlocks can be avoided by locking tables in same order (in all applications), thus preventing a deadlock condition. For example, session1 lock table: emp then dept; session2: emp then dept. If this is not possible, your application should check for ORA-60 errors and restart the rolled back transactions.
Here is an example of how to simulate a deadlock error:
Session 1 lock table EMP:
SQL> UPDATE emp SET sal=sal+100; 14 rows updated.
Session 2 lock table DEPT:
SQL> UPDATE dept SET loc = 'Japan'; 4 rows updated.
Session 1 now update DEPT. The session will hang waiting for a lock (not a deadlock yet!):
SQL> UPDATE dept SET loc = 'Japan';
Session 2 now update EMP, causing the deadlock:
SQL> UPDATE emp SET sal=sal+100;
Oracle will detect the deadlock and roll back one of these statements:
SQL> UPDATE emp SET sal=sal+100; UPDATE emp SET sal=sal+100 * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
Other more obscure deadlock situations one needs to be aware of:
- If you get ORA-60 errors on UPDATE and DELETE statements, where two processes wait for 'S' mode locks on each other's 'TX' enqueues, you are experiencing ITL shortage deadlocks". This cannot happen with INSERT statements, as Oracle doesn't wait on ITL (Interested Transaction List) slots for inserts, it will simply try to insert the row into the next available block.
To fix this, recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.
Note: "ITL waits" can be monitored per segments by querying the sys.v_$segment_statistics view.
- High transaction activity on tables with bitmap indexes. Bitmap indexes are only appropriate in read only/ read mostly environments. You can try to use a very high INITTRANS value for the bitmap index, however, it would be best to disable the index before heavy transactional activity, and to rebuild it when done.
Here are more info about deadlocks caused by bitmap indexes.
[edit] How does one diagnose and fix 'library cache pin' waits?
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 page 1
Users Waiting for Library Cache Pins
SID EVENT P1RAW SECONDS_IN_WAIT
---------- -------------------- ---------------- ---------------
374 library cache pin 000000051862E5F0 1531
944 library cache pin 000000051862E5F0 10383
1057 library cache pin 000000051862E5F0 10554
776 library cache pin 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 page 1
Object that is Blocking
OWNER OBJECT
-------- ----------------------------------------------------------------------
begin SP_EMP.PROC1@orcl(:a,:b,:c); end;
Identify the users that are waiting/ blocking:
SQL> tti "Blocking/Waiting Users"
SQL> col SID_SERIAL format a12
SQL> SELECT s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
2 FROM sys.x$kglpn p, sys.v_$session s
3 WHERE p.kglpnuse = s.saddr
4 AND kglpnhdl = '&P1RAW'
5 /
Enter value for p1raw: 000000051862E5F0
old 4: AND kglpnhdl = '&P1RAW'
new 4: AND kglpnhdl = '000000051862E5F0'
Wed Aug 11 page 1
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 can one get a list of all hidden Oracle parameters?
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:
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(ksppinm,1,1)='_' order by a.ksppinm;
Remember: Thou shall not play with undocumented parameters! Using undocumented parameters without the consent of Oracle Support will make your database "un-supported". You will be on your own if the parameters you've set cause problems or data corruption.
