http://www.orafaq.com/faqdbain.htm[@more@]
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.
Back to top of file
Back to top of file
Back to top of file
Oracle Database Internals FAQ
$Date: 26-Apr-2002 $
$Revision: 1.02 $
$Author: Frank Naudé $
|
|
- 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 processstateDumps 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 10These 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 fileHow can one dump internal database structures?
The following (mostly undocumented) commands can be used to obtain information about internal database structures.
alter session set events 'immediate trace name CONTROLF level 10'
/
alter session set events 'immediate trace name FILE_HDRS level 10'
/
alter session set events 'immediate trace name REDOHDR level 10'
/
-- NOTE: Take 3 successive SYSTEMSTATE dumps, with 10 minute intervals
alter session set events 'immediate trace name SYSTEMSTATE level 10'
/
alter session set events 'immediate trace name PROCESSSTATE level 10'
/
alter session set events 'immediate trace name library_cache level 10'
/
alter session set events '10053 trace name context forever, level 1'
/
-- 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'
/
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: , image: oracleorcl
SQLPLUS> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
SQLPLUS> ! vi /app/oracle/admin/orcl/bdump/ora_.trc
SQLPLUS> REM Trace Process Statistics
SQLPLUS> oradebug setorapid 2
Unix process pid: , image: ora_pmon_orcl
SQLPLUS> oradebug procstat
Statement processed.
SQLPLUS> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_.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)
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 HELPIt 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;
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 |
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 |
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/271283/viewspace-1014575/
4712
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
