Oracle Database Internals FAQ

$Date: 26-Apr-2002 $
$Revision: 1.02 $
$Author: Frank Naudé $

Topics

select *
from   SYS.X$KSPPI
where  substr(KSPPINM,1,1) = '_';

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!

Events can be activated by either adding them to the INIT.ORA parameter file. E.g.

	 event='1401 trace name errorstack, level 12'

	 alter session set events '10046 trace name context forever, level 4';

• 10046 trace name context forever, level 4

  Trace SQL statements and show bind variables in trace output.

• 10046 trace name context forever, level 8

  This shows wait events in the SQL trace files

• 10046 trace name context forever, level 12

  This shows both bind variable names and wait events in the SQL trace files

• 1401 trace name errorstack, level 12
  1401 trace name errorstack, level 4
  1401 trace name processstate

  Dumps out trace information if an ORA-1401 "inserted value too large for column" error occurs. The 1401 can be replaced by any other Oracle Server error code that you want to trace.

• 60 trace name errorstack level 10

  Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.

• 10210 trace name context forever, level 10
  10211 trace name context forever, level 10
  10231 trace name context forever, level 10

  These events prevent database block corruptions

• 10049 trace name context forever, level 2

  Memory protect cursor

• 10210 trace name context forever, level 2

  Data block check

• 10211 trace name context forever, level 2

  Index block check

• 10235 trace name context forever, level 1

  Memory heap check

• 10262 trace name context forever, level 300

  Allow 300 bytes memory leak for connections

Note: You can use the Unix oerr command to get the description of an event. On Unix, you can type "oerr ora 10053" from the command prompt to get event details.

-- 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
-- NOTE: Take 3 successive SYSTEMSTATE dumps, with 10 minute intervals
alter session set events 'immediate trace name SYSTEMSTATE level 10'
/

-- Dump the 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'
/

        SQLPLUS> REM Trace SQL statements with bind variables
        SQLPLUS> oradebug setospid 10121
        Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
        SQLPLUS> oradebug EVENT 10046 trace name context forever, level 12
        Statement processed.
        SQLPLUS> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc

        SQLPLUS> REM Trace Process Statistics
        SQLPLUS> oradebug setorapid 2
        Unix process pid: 1436, image: ora_pmon_orcl
        SQLPLUS> oradebug procstat
        Statement processed.
        SQLPLUS> oradebug TRACEFILE_NAME
        /app/oracle/admin/orcl/bdump/pmon_1436.trc

        SQLPLUS> REM List semaphores and shared memory segments in use
        SQLPLUS> oradebug ipc

        SQLPLUS> REM Dump Error Stack
        SQLPLUS> oradebug setospid <pid>
        SQLPLUS> oradebug event immediate trace name errorstack level 3

        SQLPLUS> REM Dump Parallel Server DLM locks
        SQLPLUS> oradebug lkdebug -a convlock
        SQLPLUS> oradebug lkdebug -a convres
        SQLPLUS> oradebug lkdebug -r <resource handle> (i.e 0x8066d338 from convres dump)

• From Server Manager (Oracle7.3 and above): ORADEBUG HELP

  It looks like one can change memory locations with the ORADEBUG POKE command. Anyone brave enough to test this one for us?

  Previously this functionality was available with ORADBX (ls -l $ORACLE_HOME/rdbms/lib/oradbx.o; make -f oracle.mk oradbx)

• SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS;

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 loss 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

	select kviival write_batch_size
	from   x$kvii where kviitag = 'kcbswc';

	select * from x$ksqst
	where  ksqstget > 0;

        OPIRIP: Uncaught error 447. Error stack:
        KCF: write/open error block=0x3e800 online=1

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

HOME | ASK QUESTION | ADD FAQ | SEARCH | E-MAIL US