ITPub博客

首页 > 数据库 > Oracle > Oracle Database Internals FAQ

Oracle Database Internals FAQ

原创 Oracle 作者:jolly10 时间:2008-12-12 17:04:56 0 删除 编辑
http://www.orafaq.com/faqdbain.htm[@more@]

Oracle Database Internals FAQ

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

WARNING: This FAQ is for informational purposes only. Check with Oracle Support before using the information below.

  • What is the difference between locks, latches, enqueues and semaphores?
  • Where can one get a list of all hidden Oracle parameters?
  • What is a database EVENT and how does one set it?
  • What database events can be set?
  • How can one dump internal database structures?
  • How does one use ORADEBUG from Server Manager/ SQL*Plus?
  • Are there any undocumented commands in Oracle?
  • What is in all those X$ tables?
  • Handy X$table queries
  • Oracle Kernel Subsystems

  • A latch is an internal Oracle mechanism used to protect data structuresin the SGA from simultaneous access. Atomic hardware instructions likeTEST-AND-SET are used to implement latches. Latches are more restrictive thanlocks in that they are always exclusive. Latches are never queued, but willspin or sleep until they obtain a resource, or time out.

    Enqueues and locks are different names for the same thing. Bothsupport queuing and concurrency. They are queued and serviced in afirst-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" semmni="number" semmsl="semmns">
  • Back to top of file

    What is a database EVENT and how does one set it?

    Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.

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

    	 event='1401 trace name errorstack, level 12'
    
    ... or, by issuing an ALTER SESSION SET EVENTS command: E.g.
    	 alter session set events '10046 trace name context forever, level 4';
    
    The alter session method only affects the user's current session, whereas changes to the INIT.ORA file will affect all sessions once the database has been restarted.

  • Back to top of file

    What database events can be set?

    The following events are frequently used by DBAs and Oracle Support to diagnose problems:
    • 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.

    The following list of events are examples only. They might be version specific, so please call Oracle before using them:
    • 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.

  • Back to top of file

    How can 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
    -- 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'
    /
    
    

  • Back to top of file

    How does one use ORADEBUG from Server Manager/ SQL*Plus?

    Execute the "ORADEBUG HELP" command from svrmgrl or sqlplus to obtain a list of valid ORADEBUG commands. Look at these examples:
    
            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 
            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  (i.e 0x8066d338 from convres dump)
    

  • Back to top of file

    Are there any undocumented commands in Oracle?

    Sure there are, but it is hard to find them. Look at these examples:
    • 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;

  • Back to top of file

    What is in all those X$ tables?

    The following list attempts to describe some 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 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
  • Back to top of file

    Handy X$table queries

    Some handy queries based on the X$ memory tables:

  • Largest # blocks you 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  ksqstget > 0;

  • Back to top of file

    Oracle Kernel Subsystems

    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
  • Back to top of file
  • Back to top of file

    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!

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-1014575/,如需转载,请注明出处,否则将追究法律责任。

    请登录后发表评论 登录
    全部评论

    注册时间:2008-02-20

    • 博文量
      263
    • 访问量
      769119