Memory Dumps（ORACLE記憶體結構轉儲）

jinqibingl發表於2010-08-20
Oracle記憶體

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)

Level Description
1 Include PGA
2 Include SGA
4 Include UGA
8 Include indirect memory dumps

The following ORADEBUG command has the same effect 

    ORADEBUG DUMP GLOBAL_AREA level

The structure of the fixed SGA is externalised in X$KSMFSV

Name Type Description
ADDR RAW(4)  
INDX NUMBER  
INST_ID NUMBER  
KSMFSNAM VARCHAR2(64) Name
KSMFSTYP VARCHAR2(64) Type
KSMFSADR RAW(4) Address
KSMFSSIZ NUMBER Size in Bytes

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
Level Description
1 Dump library cache statistics
2 Include hash table histogram
3 Include dump of object handles
4 Include dump of object structures (heap 0)

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

 

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

Level Description
1 Dump row cache statistics
2 Include hash table histogram
8 Include dump of object structures

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

Level Description
1 Buffer headers only
2 Level 1 + block headers
3 Level 2 + block contents
4 Buffer headers only + hash chain
5 Level 1 + block headers + hash chain
6 Level 2 + block contents + hash chain
8 Buffer headers only + hash chain + users/waiters
9 Level 1 + block headers + hash chain + users/waiters
10 Level 2 + block contents + hash chain + users/waiters

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

Level Description
1 PGA summary
2 SGA summary
4 UGA summary
8 Callheap (Current)
16 Callheap (User)
32 Large pool
64 Streams pool
128 Java pool
1025 PGA with contents
2050 SGA with contents
4100 UGA with contents
8200 Callheap with contents (Current)
16400 Callheap with contents (User)
32800 Large pool with contents
65600 Streams pool with contents
131200 Java pool with contents

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

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

Level Description
0 Error stack only
1 Error stack and function call stack
2 As level 1 plus the process state
3 As level 2 plus the context area

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

Call Stack Trace
Files Currently Opened
Process State
Pinned Buffer History
Cursor Dump
Fixed PGA
Fixed SGA
Fixed UGA
In memory trace dump


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

  • systemstate level 1 dump
  • processes currently waiting, blocking or spinning
  • errorstacks


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)

Level Description
1 Global SGA Info
2 Workarea Table (Summary)
3 Workarea Table (Detail)

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

Level Description
1 Dump a summary of active resources and locks, the resource free list and the hash table
2 Include a dump of resource structures
3 Include a dump of lock structures

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

Level Description
1 Latches
2 Include statistics

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

Level Description
1 Session
2 Process
4 System

The following ORADEBUG command has the same effect

ORADEBUG DUMP EVENTS level

 

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章