Memory Dumps(ORACLE内存结构转储)
dump 2010-02-26 23:11:57 阅读13 评论0 字号:大中小 订阅
The following command can be used to dump the global area
ALTER SESSION SET EVENTS 'immediate trace name global_area level level';
Levels are (bitmask)
|
The following ORADEBUG command has the same effect
ORADEBUG DUMP GLOBAL_AREA level
The structure of the fixed SGA is externalised in X$KSMFSV
|
This can be queried using
SELECT SUBSTR (ksmfsnam,1,20) AS "Name", SUBSTR (ksmfstyp,1,20) AS "Type", ksmfsadr AS "Address", ksmfssiz AS "Size" FROM x$ksmfsv;
LIBRARY_CACHE
The following command can be used to dump the library cache
ALTER SESSION SET EVENTS 'immediate trace name library_cache level level';
where level is one of the following
|
For example
ALTER SESSION SET EVENTS 'immediate trace name library_cache level 1';
The following ORADEBUG command has the same effect
ORADEBUG DUMP LIBRARY_CACHE level
Dumping the Row (Dictionary) Cache
Dumps
ROW_CACHE
The row cache is also known as the dictionary cache
The following command can be used to dump the row cache
ALTER SESSION SET EVENTS 'immediate trace name row_cache level level';
where level is one of the following
|
For example
ALTER SESSION SET EVENTS 'immediate trace name row_cache level 1';
The following ORADEBUG command has the same effect
ORADEBUG DUMP ROW_CACHE level
Multiple Buffers
To dump buffer headers and buffer contents for buffers currently in the cache
ALTER SESSION SET EVENTS 'immediate trace name buffers level level';
where level is one of the following
|
Individual Buffers
In Oracle 8.0 and above is is possible to dump buffer all buffers currently in the cache for a specific block
For example where a block has been modified and is subject to consistent read from a number of transactions, there may be more than one copy of the block in the buffer cache
First identify the tablespace number for the block e.g for tablespace TS01
SELECT ts# FROM sys.ts$ WHERE name = 'TS01';
Set the tablespace number using
ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level level';
where level is the tablespace number + 1
Identify the relative DBA for the block
This is equal to
RelativeFileNumber * 4194304 + BlockNumber
e.g. for a block with relative file number of 5 and a block number of 127874
5 * 4194304 + 127874 = 21099394
Dump the buffer using
ALTER SESSION SET EVENTS 'immediate trace name buffer level level';
where level is the relative DBA e.g.
ALTER SESSION SET EVENTS 'immediate trace name buffer level 21099394';
Dumps
Memory Heaps
To dump the top-level heap in a memory area
ALTER SESSION SET EVENTS 'immediate trace name heapdump level level';
Levels are
|
Levels are correct to Oracle 10.2.0.1
The levels can be combined. For example a level 3 dump will contain both the PGA and SGA
Dumps
Memory Subheaps
Each heap may have one or more subheaps. These can be dumped using the command
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level level';
where the level is the address of the subheap.
The syntax for this command changed in Oracle 9.2
Oracle 9.0.1 and below |
Oracle 9.2.0 and above |
Oracle 9.0.1 and below
For Oracle 9.0.1 and below a summary dump is obtained by setting the level to the decimal value of the address. A detailed dump is obtained by adding 1 to the decimal value of the address.
For example to dump the subheap at address 0x8057eb78, first convert the address to decimal (2153245560)
The subheap address can be found in the heapdump, for example
ds=0x8057eb78
For a summary dump use
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2153245560';
For a detailed dump, add 1 to the address e.g.
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2153245561';
Oracle 9.2.0 and above
In Oracle 9.2 and above, for a summary dump
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 1, addr 2153245560';
The following ORADEBUG command has the same effect
ORADEBUG DUMP HEAPDUMP_ADDR 1 2153245560
In Oracle 9.2 and above, for a detailed dump
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2, addr 2153245560';
Note that in Oracle 9.2 it is no longer necessary to add 1 to the address
The following ORADEBUG command has the same effect
ORADEBUG DUMP HEAPDUMP_ADDR 2 2153245560
Dumps
Process State
To dump the process state use:
ALTER SESSION SET EVENTS 'immediate trace name processstate level level';
For example
ALTER SESSION SET EVENTS 'immediate trace name processstate level 10';
The following ORADEBUG command has the same effect
ORADEBUG DUMP PROCESSSTATE level
The amount of library cache dump output for state object dumps can be limited using event 10065
Dumps
System State
A system state dump contains the process state for every process.
Every state object for every process is dumped.
A state object represents the state of a database resource including
- processes
- sessions
- enqueues (locks)
- buffers
State objects are held in the SGA
A system state dump does not represent a snapshot of the instance because the database is not frozen for the duration of the dump. The start point of the dump will be earlier than the end point.
Oracle recommends the use of system state dumps to diagnose
- hanging databases
- slow databases
- database errors
- waiting processes
- blocking processes
- resource contention
To dump the system state use
ALTER SESSION SET EVENTS 'immediate trace name systemstate level level';
For example
ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';
The following ORADEBUG command has the same effect
ORADEBUG DUMP SYSTEMSTATE level
A system state dump can be triggered by an error, for example the following init.ora parameter
event = "60 trace name systemstate level 10"
will generate a systemstate dump whenever a deadlock is detected (ORA-00060)
The amount of library cache dump output for state object dumps can be limited using event 10065
Dumps
Error Stack
An error stack describes the current state of a process. It includes the current SQL statement and the process state for the process.
Oracle recommends taking an error stack dump to diagnose
- what the process is doing
- a problem identified by a systemstate dump
- processes looping or hanging
Error stack dumps can also be triggered by an error
To dump an error stack use
ALTER SESSION SET EVENTS 'immediate trace name errorstack level level';
where level is one of the following
|
The following ORADEBUG command has the same effect
ORADEBUG DUMP ERRORSTACK level
An alternative syntax for the same command is
ORADEBUG EVENT IMMEDIATE TRACE NAME ERRORSTACK level
To dump a level 3 errorstack when ORA-00942 (table or view does not exist) use
ALTER SESSION SET EVENTS '942 trace name errorstack level 3';
A conditional errorstack dump can also be specified without a level e.g.
ALTER SESSION SET EVENTS '604 trace name errorstack';
Alternatively a conditional errorstack dump can be specified in the init.ora file
event = "942 trace name errorstack level 3"
Errorstacks can also be dumped conditionally using ORADEBUG
ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3
A level 3 errorstack contains the following sections
Dumps Hang Analysis This dump is available in Oracle 8.0.6 and Oracle 8.1.6 and above. It is not available in Oracle 8.1.5. It contains
The HANGANALYZE dump is more selective than a SYSTEMSTATE dump in that it only includes details of processes of interest. It is particularly intended for situations where a database is hanging. For example ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 5'; The following ORADEBUG command has the same effect ORADEBUG DUMP HANGANALYZE level e.g. ORADEBUG DUMP HANGANALYZE 5 A HANGANALYZE dump can also be generated on an error e.g. when a deadlock is detected (ORA-00060) using the init.ora file event = "60 trace name hanganalyze level 5" or in the current session using ALTER SESSION SET EVENTS '60 trace name hanganalyze level 5'; Dumps Work Areas To dump the current workareas use ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level level'; Levels are (bitmask)
The following ORADEBUG command has the same effect ORADEBUG DUMP WORKAREATAB_DUMP level Dumps Enqueues To dump the current enqueue states use ALTER SESSION SET EVENTS 'immediate trace name enqueues level level'; Levels are
The following ORADEBUG command has the same effect ORADEBUG DUMP ENQUEUES level Dumps Latches To dump the current latch status use ALTER SESSION SET EVENTS 'immediate trace name latches level level'; Levels are
The following ORADEBUG command has the same effect ORADEBUG DUMP LATCHES level
Dumps Events To dump the events set for the current session ALTER SESSION SET EVENTS 'immediate trace name events level level'; Levels are
The following ORADEBUG command has the same effect ORADEBUG DUMP EVENTS level
Dumping Shared Server (MTS) Processes Dumps Shared Server (MTS) Processes To dump buffer headers and buffer contents for buffers currently in the cache ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level level'; where level is in the range 1 to 14 The following ORADEBUG command has the same effect ORADEBUG DUMP SHARED_SERVER_STATE level In Oracle 8.1.7 and below, this dump is called MTSSTATE Dumps Background Messages Background process messages can be traced using ALTER SESSION SET EVENTS 'immediate trace name bg_messages level level'; where level identifies the background process (+1) For example to find the level of SMON, first identify the index number using the following query SELECT indx + 1 FROM x$ksbdp WHERE ksbdpnam = 'SMON'; The following ORADEBUG command has the same effect ORADEBUG DUMP BG_MESSAGES level |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9606200/viewspace-671512/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9606200/viewspace-671512/