Transcript Oracle Diagnostics

Oracle
Diagnostics
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Warning
2

Much of the content of this presentation is undocumented
and unsupported by Oracle

Check with Oracle support before using any of these
features in a production environment
© 2005 Julian Dyke
juliandyke.com
Trace Parameters

To include timed statistics in trace files
timed_statistics = TRUE

To specify the log file destination
user_dump_dest = '<directory_name>'
background_dump_dest = '<directory_name'>

To specify maximum trace file size
max_dump_file_size = <size>

To allow other users to read trace files
_trace_files_public = TRUE
3
© 2005 Julian Dyke
juliandyke.com
Trace File Identifier

In Oracle 8.1.7 and above, a trace file identifier can be
specified
tracefile_identifier = '<identifier>'

e.g. in Oracle 9.2 if a trace file is called
ss92001_ora_1760.trc

then the statement
ALTER SESSION
SET tracefile_identifier = 'test';

will change the file name to
ss92001_ora_1760_test.trc
4
© 2005 Julian Dyke
juliandyke.com
Trace File Names



In Oracle 9.2 foreground process trace file names are in the
following formats
Process Type
Name
ss92001_ora_1234.trc
Foreground Process
ss92001_p000_1234.trc
Parallel execution slave
ss92001_j000_1234.trc
Job queue process
ss92001_d000_1234.trc
Dispatcher process
ss92001_s000_1234.trc
Shared Server process
These trace files are written to the USER_DUMP_DEST
directory
In Oracle 9.2 background process trace file names are in the
format
ss92001_<process_name>_1234.trc

5
These trace files are written to the
BACKGROUND_DUMP_DEST directory
© 2005 Julian Dyke
juliandyke.com
Events
6

There are four types of numeric events
 Immediate dumps
 Conditional dumps
 Trace dumps
 Change database behaviour

Each event has 1 or more level which can be
 range e.g. 1 to 10
 bitmask e.g. 0x01 0x02 0x04 0x08 0x10 etc
 flag e.g. 0 = off; 1 = on
 identifier e.g. object id, memory address, etc
© 2005 Julian Dyke
juliandyke.com
Events

To enable a numeric event at instance level
# In init.ora file
event = '<event> trace name context forever, level <level>';
ALTER SYSTEM SET EVENTS
'<event> trace name context forever, level <level>';

To enable a numeric event at session level
ALTER SESSION SET EVENTS
'<event> trace name context forever, level <level>';

Alternatively use


7
ORADEBUG
DBMS_SYSTEM.SETEV
© 2005 Julian Dyke
juliandyke.com
Events

To dump all event messages
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
DBMS_OUTPUT.PUT_LINE (err_msg);
END IF;
END LOOP;
END;
/
8
© 2005 Julian Dyke
juliandyke.com
Events

On Unix systems event messages are in the formatted text file
$ORACLE_HOME/rdbms/mesg/oraus.msg

To print detailed event messages (Unix only)
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
9
© 2005 Julian Dyke
juliandyke.com
Events

To check which events are enabled in the current session
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF (l_level > 0) THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event) ||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
10
© 2005 Julian Dyke
juliandyke.com
SQL Trace



11
SQL_TRACE is event 10046 level 1
Other levels are
Level
Description
0
Disabled
1
Same as ALTER SESSION SET sql_trace = TRUE
4
Include bind information
8
Include event wait statistics
12
Include bind information and event wait statistics
See Metalink Note 39817.1 for details of trace output
© 2005 Julian Dyke
juliandyke.com
Optimiser Decisions

To trace the computations performed by the CBO when
optimising SQL statements use
ALTER SESSION SET EVENTS
'10053 TRACE NAME CONTEXT FOREVER, LEVEL <level>';

12
Level
Description
1
Print statistics and computations
2
Print computations only
See "A Look under the Hood of CBO : The 10053 Event"
Wolfgang Breitling - www.centrexcc.com
© 2005 Julian Dyke
juliandyke.com
Events


13
Tracing SQL Execution
10032
Sorts
10
10033
Intermediate Sort Runs
10
10104
Hash Joins
10
10128
Partition Pruning
10224
Index Blocks Splits / Deletes
1
10270
Shared Cursors
1
10299
Prefetching
1
10357
Direct Path Load
1
10730
VPD/FGAC/RLS predicates
1
10731
CURSOR expressions
1
Tracing Parallel Execution
10390
Parallel Query Slave Execution
10391
Parallel Query Granule Assignment
10393 Parallel Query Statistics
© 2005 Julian Dyke
juliandyke.com
Events


Tracing Bitmap Indexes
10608
Bitmap Index Creation
10710
Bitmap Index Access
1
10711
Bitmap Index Merge
1
10712
Bitmap Index Or
1
10713
Bitmap Index And
1
10714
Bitmap Index Minus
1
10715
Bitmap Index Conversion to ROWIDs
1
10716
Bitmap Index Compress/Decompress
1
10717
Bitmap Index Compaction
1
10719
Bitmap Index DML
1
10608
Bitmap Index Creation
1
10710
Bitmap Index Access
1
Tracing Remote Processing
10079
14
10
Data sent/received via SQL*Net
10241 Remote SQL Execution
© 2005 Julian Dyke
1
10
juliandyke.com
Events


15
Tracing Space Management
10045
Free List Management
10081
High Water Mark Changes
10225
Extent Management (Dictionary Managed)
Tracing Undo/Read Consistency
10013
Monitor transaction recovery during startup
10015
Dump Undo Segment Headers before and
after transaction recovery
10200
Consistent Read
10201
Consistent Read Undo Application
10220
Changes to Undo Header
© 2005 Julian Dyke
juliandyke.com
Enabling SQL Trace

At the session level
-- Enable SQL trace
ALTER SESSION SET sql_trace = TRUE;
-- Disable SQL trace
ALTER SESSION SET sql_trace = FALSE;

For extended trace use
-- Enable SQL trace with binds
ALTER SESSION SET EVENTS
'10046 trace name context forever, level 4';
-- Disable SQL trace with binds
ALTER SESSION SET EVENTS
'10046 trace name context off';
16
© 2005 Julian Dyke
juliandyke.com
Enabling SQL Trace

To enable at instance level
# Enable SQL trace
sql_trace = TRUE
# Enable SQL*trace with binds
event = '10046 trace name context forever, level 4';

The SQL_TRACE parameter cannot be modified directly using
ALTER SYSTEM. Instead use
-- Enable SQL trace for instance
ALTER SYSTEM SET EVENTS
'10046 trace name context forever, level 1;
-- Disable SQL trace for instance
ALTER SYSTEM SET EVENTS
'10046 trace name context off';
17
© 2005 Julian Dyke
juliandyke.com
Editing a Trace File from SQL*Plus

Example (Oracle 9.2.0 on Windows 2000)
SET SUFFIX TRC
COLUMN filename NEW_VALUE filename
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid filename
FROM
v$process p, v$session s,
v$parameter p1, v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID');
EDIT &&filename
SET SUFFIX SQL
COLUMN filename CLEAR
18
© 2005 Julian Dyke
juliandyke.com
Circular Trace Buffers

To enable circular tracing
ALTER SESSION SET EVENTS
'immediate trace name trace_buffer_on level <level>';

where <level> is the size of the trace buffer in bytes

To dump the contents of the circular trace buffer
ALTER SESSION SET EVENTS
'immediate trace name trace_buffer_off';
19
© 2005 Julian Dyke
juliandyke.com
DBMS_SESSION

Event 10046 level 1 trace can be enabled using
DBMS_SESSION.SET_SQL_TRACE
(
FLAG BOOLEAN
-- TRUE to enable;
-- FALSE to disable
);


20
Useful within PL/SQL blocks
ALTER SESSION privilege not required
© 2005 Julian Dyke
juliandyke.com
Using System Triggers

Login as SYS (AS SYSDBA)
CREATE OR REPLACE TRIGGER us01_logon
AFTER LOGON ON us01.SCHEMA
BEGIN
dbms_session.set_sql_trace (TRUE);
END;
CREATE OR REPLACE TRIGGER us01_logoff
BEFORE LOGOFF ON us01.SCHEMA
BEGIN
dbms_session.set_sql_trace (FALSE);
END;
ALTER TRIGGER us01_login ENABLE;
ALTER TRIGGER us01_login DISABLE;
21
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM



Undocumented package
Installed in all versions
Owned by SYS user
$ORACLE_HOME/rdbms/admin/dbmsutil.sql
GRANT EXECUTE ON DBMS_SYSTEM TO <user>;
CREATE PUBLIC SYNONYM dbms_system
FOR sys.dbms_system;
22
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To enable trace in another session use
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
(
SI NUMBER,
-- SID
SE NUMBER,
-- Serial Number
SQL_TRACE BOOLEAN
-- TRUE to enable;
-- FALSE to disable
);

23
SID and Serial number can be found in V$SESSION (SID
and SERIAL#)
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To set a Boolean parameter in another session use
DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION
(
ORADEBUG
SUSPEND
SID
NUMBER,
-- SID
SERIAL#
NUMBER,
-- Serial Number
PARNAM
VARCHAR2,
-- Parameter Name
SET_BOOL_PARAM_IN_SESSION
BVAL
BOOLEAN
-- Value
);

For example
EXECUTE DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION
(9, 27, 'hash_join_enabled', TRUE);

24
Note: does not work with SQL_TRACE
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To set an integer parameter in another session use
DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION
(
ORADEBUG
SUSPEND
SID
NUMBER,
-- SID
SERIAL#
NUMBER,
-- Serial Number
PARNAM
VARCHAR2,
-- Parameter Name
SET_BOOL_PARAM_IN_SESSION
INTVAL
INTEGER
-- Value
);

For example
EXECUTE DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION
(9, 27, 'sort_area_size', 131072);
25
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To set an event in another session use
DBMS_SYSTEM.SET_EV
(
SI NUMBER,
SE NUMBER,
EV NUMBER,
LE NUMBER,
NM VARCHAR2
);

26
-- SID
-- Serial Number
-- Event Number e.g. 10046
-- Level e.g. 1
-- Action Name – can be ''
Disable using same SID, serial number and event with
level 0
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To write to trace files and/or alert log use
DBMS_SYSTEM.KSDWRT
(
DEST NUMBER,
TST VARCHAR2
);

-- 1 = Trace File, 2 = Alert Log
-- Message
Example
BEGIN
DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’);
DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);
END;
/
27
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM

To write the date and time to a trace file use
EXECUTE DBMS_SYSTEM.KSDDDT;

To flush the contents of the trace buffer to disk use
EXECUTE DBMS_SYSTEM.KSDFLS;

To indent output in the trace file use
EXECUTE DBMS_SYSTEM.KSDIND (<level>);

28
This will prefix KSDWRT output with <level> colons
© 2005 Julian Dyke
juliandyke.com
DBMS_SUPPORT




Available in Oracle 7.2 and above
Requires dbmssupp.sql and prvtsupp.plb
See Metalink Note 62294.1
Install using SYS AS SYSDBA
$ORACLE_HOME/rdbms/admin/dbmssupp.sql
GRANT EXECUTE ON DBMS_SUPPORT TO <user>;
CREATE PUBLIC SYNONYM dbms_support
FOR sys.dbms_support;

To get SID of current session use
FUNCTION DBMS_SUPPORT.MYSID
RETURN BOOLEAN;

29
This function executes the query
© 2005 Julian Dyke
SELECT sid FROM v$mystat
WHERE ROWNUM = 1;
juliandyke.com
DBMS_SUPPORT

To enable SQL trace in the current session use
DBMS_SUPPORT.START_TRACE
(
WAITS BOOLEAN,
-- Include waits (default FALSE)
BINDS BOOLEAN
-- Include binds (default FALSE)
);

To disable use
DBMS_SUPPORT.STOP_TRACE;
30
© 2005 Julian Dyke
juliandyke.com
DBMS_SUPPORT

To enable SQL trace in another session use
DBMS_SUPPORT.START_TRACE_IN_SESSION
(
SI NUMBER,
-- SID
SE NUMBER,
-- Serial Number (can be 0)
WAITS BOOLEAN,
-- Include waits (default FALSE)
BINDS BOOLEAN
-- Include binds (default FALSE)
);

To disable use
DBMS_SUPPORT.STOP_TRACE_IN_SESSION
(
SI NUMBER,
-- SID
SE NUMBER
-- Serial Number (can be 0)
);
31
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR


Introduced in Oracle 10.1
To enable trace in another session use
DBMS_MONITOR.SESSION_TRACE_ENABLE
(
SESSION_ID NUMBER,
-- SID
SERIAL_NUM NUMBER,
-- Serial Number
WAITS BOOLEAN,
-- Include Waits
BINDS BOOLEAN
-- Include Binds
);

To disable trace in another session use
DBMS_MONITOR.SESSION_TRACE_DISABLE
(
SESSION_ID NUMBER,
-- SID
SERIAL_NUM NUMBER
-- Serial Number
);
32
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR


Trace can be enabled using client identifiers
To set a client identifier use
DBMS_SESSION.SET_IDENTIFIER
(
CLIENT_ID VARCHAR2
);

33
-- Client ID
The client identifier for a specific session can be found by
querying V$SESSION.CLIENT_IDENTIFIER
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR

Trace can be enabled using client identifiers

To enable trace for a specific client use
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
(
CLIENT_ID NUMBER,
-- Client ID
WAITS BOOLEAN,
-- Include Waits
BINDS BOOLEAN
-- Include Binds
);

34
Trace can be disabled using
DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR
35

Trace can be enabled for a specific
 service
 service and module
 service, module and action

To add a service in a RAC database use
 DBCA
 Enterprise Manager (Oracle 10.2 and above)
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR


To add a service in a single instance environment
Set the SERVICE_NAMES parameter e.g.
service_names = 'LX101001, SERVICE1'

Add the service to TNSNAMES.ORA e.g.
SERVICE1 =
(DESCRIPTON =
(ADDRESS =
(PROTOCOL=TCP)(HOST=server1)(PORT=1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SERVICE1)
)
)
36
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR

To specify a module and action use
DBMS_APPLICATION_INFO.SET_MODULE
(
MODULE_NAME VARCHAR2,
ACTION_NAME VARCHAR2 -- Action
);

-- Module
To specify subsequent actions use
DBMS_APPLICATION_INFO.SET_ACTION
(
ACTION_NAME VARCHAR2 -- Action
);
37
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR

To enable trace for a specific module and action use
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
(
SERVICE_NAME VARCHAR2,
-- Service Name
MODULE_NAME VARCHAR2,
-- Module
ACTION_NAME VARCHAR2,
-- Action
WAITS BOOLEAN,
-- Waits
BINDS BOOLEAN,
-- Binds
INSTANCE_NAME VARCHAR2
-- Instance
);
38

If ACTION_NAME is not specified, entire module will be traced

Tracing can be disabled using
SERV_MOD_ACT_TRACE_DISABLE
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR

To enable statistics collection for a specific client
DBMS_MONITOR.CLIENT_ID_STAT_ENABLE
(
CLIENT_ID VARCHAR2
-- Client ID
);

Statistics externalized in V$CLIENT_STATS
Disable using DBMS_MONITOR.CLIENT_ID_STAT_DISABLE

To enable statistics collection for a specific module/action

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE
(
SERVICE_NAME VARCHAR2,
-- Service Name
MODULE_NAME VARCHAR2,
-- Module
ACTION_NAME VARCHAR2 -- Action
);


39
Statistics externalized in V$SERV_MOD_ACT_STATS
Disable using DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE
© 2005 Julian Dyke
juliandyke.com
trcsess



Introduced in Oracle 10.1
Conditionally extracts trace data
Merges trace files
trcsess
[output = <output_file_name>]
[session = <session_id>]
[clientid = <client_id>]
[service = <service_name>]
[module = <module_name>]
[action = <action_name>]
<trace_file_names>


where trace_file_names can be space separated list of file
names or '*' wildcard
service, action and module names are case sensitive
trcsess service=APP1 module=MODULE1 action=ACTION1 *
40
© 2005 Julian Dyke
juliandyke.com
DBA_ENABLED_TRACES


Introduced in Oracle 10.1



41
Type
TRACE_TYPE
VARCHAR2(21)
PRIMARY_ID
VARCHAR2(64)
QUALIFIER_ID1
VARCHAR2(48)
QUALIFIER_ID2
VARCHAR2(32)
WAITS
VARCHAR2(5)
BINDS
VARCHAR2(5)
INSTANCE_NAME
VARCHAR2(16)
Trace type can be


Name
CLIENT_ID
SERVICE
SERVICE_MODULE
SERVICE_MODULE_ACTION
Based on WRI$_TRACING_ENABLED
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

Undocumented debugging utility available





as a standalone utility on Unix (oradbx)
as a standalone utility on VMS (orambx)
within Server Manager (svrmgr)
within SQL*Plus (8.1.5 and above)
To use ORADEBUG within SQL*Plus login using
SQLPLUS /NOLOG
SQL> CONNECT SYS/password AS SYSDBA

To list the available options
ORADEBUG HELP
42
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

There are three ways of selecting a process using ORADEBUG

Use current process
SQL> ORADEBUG SETMYPID

Use Oracle PID (V$PROCESS.PID)
SQL> ORADEBUG SETORAPID <pid>

Use Operating System PID (V$PROCESS.SPID)
SQL> ORADEBUG SETOSPID <spid>

43
This is the PID in Unix and the Thread ID in Windows NT/2000
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To display the name of the current trace file use
ORADEBUG TRACEFILE_NAME

To set the maximum size of the current trace file to UNLIMITED
use
ORADEBUG UNLIMIT

To flush the current trace file use
ORADEBUG FLUSH

To close the current trace file use
ORADEBUG CLOSE_TRACE
44
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To list the available dumps
ORADEBUG DUMPLIST

To perform a dump
ORADEBUG DUMP <dumpname> <level>

E.g. for a level 4 dump of the library cache
ORADEBUG SETMYPID
ORADEBUG DUMP LIBRARY_CACHE 4
45
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To suspend the current process
ORADEBUG SUSPEND

To resume the current process
ORADEBUG RESUME
46

While the process is suspended ORADEBUG can be used to
dump perform memory/state dumps

Can be also used to temporarily suspend long running
processes
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To dump the events currently set use
ORADEBUG DUMP EVENTS <level>


47
where level is
Level
Description
1
Session
2
Process
3
System
Output is written to the current trace file
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To enable events in another process

For foreground processes Oracle Process ID can be obtained
from Session ID using
SELECT pid FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.sid = <sid>;

e.g. to set event 10046 level 12 in Oracle process 8 use
SQL> ORADEBUG SETORAPID 8
SQL> ORADEBUG EVENT 10046
TRACE NAME CONTEXT FOREVER, LEVEL 12
48
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

To dump the value of an SGA variable use
ORADEBUG DUMPVAR SGA <variable name>

For example
ORADEBUG DUMPVAR SGA kcbnhb



prints the number of buffer cache hash buckets
The names of SGA variables can be found in
X$KSMFSV.KSMFSNAM
Variables in this view are suffixed with an underscore e.g.
kcbnhb_
49
© 2005 Julian Dyke
juliandyke.com
ORADEBUG

In some versions it is possible to dump the entire SGA to file

Freeze the instance using
ORADEBUG FFBEGIN

Dump the SGA to file using
ORADEBUG SGATOFILE '<directory name>'

Unfreeze the instance using
ORADEBUG FFRESUMEINST
50
© 2005 Julian Dyke
juliandyke.com
Immediate Dumps

There are three ways of taking an immediate dump

In the current session
ALTER SESSION SET EVENTS
'immediate trace name <dump> level <level>';

In ORADEBUG
ORADEBUG DUMP <dump> <level>

Using DBMS_SYSTEM
EXECUTE DBMS_SYSTEM.SET_EV
(sid, serial#, 65535, <level>, '<dump'>);

51
Cannot be invoked from init.ora
© 2005 Julian Dyke
juliandyke.com
Conditional Dumps


Invoked when an error occurs
In the init.ora file
event = "<error> trace name <dump> level <level>"

In the current session
ALTER SESSION SET EVENTS
'<error> trace name <dump> level <level>';

In ORADEBUG
ORADEBUG EVENT <error> trace name <dump> level <level>
ORADEBUG SESSION_EVENT
<error> trace name <dump> level <level>
52
© 2005 Julian Dyke
juliandyke.com
Dumping Columns

To dump the internal representation of columns use the DUMP
built-in function
DUMP (COLUMN_VALUE, FORMAT)

53
where FORMAT is
© 2005 Julian Dyke
Format
Description
8
Octal
10
Decimal
16
Hexadecimal
17
Single Character
juliandyke.com
Dumping Columns

For example
SELECT DUMP (1001,16) FROM dual;

returns
Typ=2 Len=3: c2,b,2

To output a column in hexadecimal use the 'XXXXXXXX'
format mask e.g.
SELECT TO_CHAR (65536,'XXXXXXXX')
FROM dual;

returns
10000
54
© 2005 Julian Dyke
juliandyke.com
Dumping a Database Block


To dump a database block in Oracle 7
File number / block number must be converted into a data
block address
COLUMN decimalDBA new_value decimalDBA
SELECT
dbms_utility.make_data_block_address
(&file,&block) decimalDBA
FROM dual;
ALTER SESSION SET EVENTS
'immediate trace name blockdump level &decimalDBA';
55
© 2005 Julian Dyke
juliandyke.com
Dumping a Database Block

To dump a database block in Oracle 8.0 or above
ALTER SYSTEM DUMP DATAFILE <absolute_file_number>
BLOCK <block_number>;

To dump a range of database blocks
ALTER SYSTEM DUMP DATAFILE <absolute_file_number>
BLOCK MIN <minimum_block_number>
BLOCK MAX <maximum_block_number>;

To dump a block from a datafile in a closed database
ALTER SYSTEM DUMP DATAFILE '<file_name>'
BLOCK <block_number>;
56
© 2005 Julian Dyke
juliandyke.com
Dumping a Database Block (Hex)

To dump a database block in hexadecimal enable event 10289
ALTER SESSION SET EVENTS
'10289 trace name context forever, level 1';

Then dump the block using
ALTER SYSTEM DUMP DATAFILE <absolute_file_number>
BLOCK <block_number>;

On Unix/Linux systems blocks can also be dumped using od
dd bs=8k if=<filename> skip=200 count=4 | od -x

Force DBWR to flush recently written blocks to disk using
ALTER SYSTEM CHECKPOINT;

or
ALTER SYSTEM SWITCH LOGFILE;
57
© 2005 Julian Dyke
juliandyke.com
Dumping an Index

An index tree can be dumped using
ALTER SESSION SET EVENTS
'immediate trace name treedump level <object_id>;
58

where object_id is the object number of the index (in
DBA_OBJECTS)

Dumps
 branches
 leaves
 contents of leaf blocks
© 2005 Julian Dyke
juliandyke.com
Dumping an Index

In Oracle 9.2 treedump may crash if index has been created by
a primary/unique constraint e.g.
CREATE TABLE t1 (c01 NUMBER PRIMARY KEY);
CREATE TABLE t1 (c01 NUMBER);
ALTER TABLE t1
ADD CONSTRAINT t1pk PRIMARY KEY (c01);


59
Occurs when IND$.PROPERTY > 256
Can be prevented by creating the index before creating the
constraint
© 2005 Julian Dyke
juliandyke.com
Dumping Undo/Rollback

To dump an undo segment header use
ALTER SYSTEM DUMP UNDO_HEADER '<segment_name>';

To dump an undo transaction first obtain the XID using
SELECT xidusn, xidslot, xidsqn
FROM v$transaction;

Dump the undo transaction using
ALTER SYSTEM DUMP UNDO BLOCK '<segment_name>'
XID <xidusn> <xidslot> <xidsqn>;
60
© 2005 Julian Dyke
juliandyke.com
Dumping a Redo Log

To identify the current redo log
SELECT member FROM v$logfile
WHERE group# =
(
SELECT group# FROM v$log
WHERE status = 'CURRENT'
);

To dump a redo log file use
ALTER SYSTEM DUMP LOGFILE '<logfilename>';

61
Also works for archived redo logs
© 2005 Julian Dyke
juliandyke.com
Other File Dumps

Control Files
ALTER SESSION SET EVENTS
'immediate trace name controlf level 15';

File Headers
ALTER SESSION SET EVENTS
'immediate trace name file_hdrs level 7';

Redo Log Headers
ALTER SESSION SET EVENTS
'immediate trace name redohdr level 3';
62
© 2005 Julian Dyke
juliandyke.com
Dumping the Library Cache

To dump the library cache
ALTER SESSION SET EVENTS
'immediate trace name library_cache level <level>';

63
where level is
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)
© 2005 Julian Dyke
juliandyke.com
Dumping the Row Cache

To dump the row (dictionary) cache
ALTER SESSION SET EVENTS
'immediate trace name row_cache level <level>';

64
where level is
Level
Description
1
Dump row cache statistics
2
Include hash table histogram
8
Include dump of object structures
© 2005 Julian Dyke
juliandyke.com
Dumping Fixed Memory Areas

To dump the fixed memory areas
ALTER SESSION SET EVENTS
immediate trace name global_area level <level>';

65
where level is
Level
Description
1
Include PGA
2
Include SGA
4
Include UGA
8
Include indirect memory dumps
© 2005 Julian Dyke
juliandyke.com
Dumping the Fixed SGA

The fixed SGA is externalised in X$KSMFSV
SELECT
SUBSTR (ksmfsnam,1,20) AS "Name",
SUBSTR (ksmfstyp,1,20) AS "Type",
ksmfsadr AS "Address",
ksmfssiz AS "Size"
FROM x$ksmfsv;

The fixed SGA can also be dumped using
ORADEBUG DUMPSGA
66
© 2005 Julian Dyke
juliandyke.com
Dumping Heap Memory
To dump heap memory use

ALTER SESSION SET EVENTS
'immediate trace name heapdump level <level>';

where level is
Level
67
Description
Level
Description
1 PGA summary
1025 PGA with contents
2 SGA summary
2050 SGA with contents
4 UGA summary
5000 UGA with contents
8 Current call (CGA)
8200 Current call with contents
16 User call (CGA)
16400 User call with contents
32 Large pool (LGA)
32800 Large pool with contents
© 2005 Julian Dyke
juliandyke.com
Dumping Subheap Memory

In Oracle 9.0.1 and below
ALTER SESSION SET EVENTS
'immediate trace name heapdump_addr level <level>';



Get address of subheap e.g. 0x8057eb78
Convert to decimal e.g. 2153245560
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';
68
© 2005 Julian Dyke
juliandyke.com
Dumping Subheap Memory

In Oracle 9.2 and above

For a summary dump use
ALTER SESSION SET EVENTS
'immediate trace name heapdump_addr level 1 addr 0x8057eb78';

For a detailed dump use
ALTER SESSION SET EVENTS
'immediate trace name heapdump_addr level 2 addr 0x8057eb78 ';
69
© 2005 Julian Dyke
juliandyke.com
SGA Memory




SGA Memory can be accessed directly using X$KSMMEM
Each row contains the four byte value at address ADDR in the
SGA
Always use the INDX column to access this table
For example, to select first word in SGA use
SELECT ksmmmval FROM x$ksmmem
WHERE indx = 0;

70
X$KSMMEM is unreliable in Windows NT/2000
(causes ORA-3113)
© 2005 Julian Dyke
juliandyke.com
Dumping Buffers

To dump buffer headers and buffer contents
ALTER SESSION SET EVENTS
'immediate trace name buffers level <level>';

Levels are
Levels
Description
1 Buffer headers only
2 1 + block headers
3 2 + block contents
71
4 Buffer headers only
+ hash chain
5 1 + block headers
+ hash chain
6 2 + block contents
+ hash chain
8 Buffer headers only
+ hash chain + users/waiters
9 1 + block headers
+ hash chain + users/waiters
10 2 + block contents
+ hash chain + users/waiters
© 2005 Julian Dyke
juliandyke.com
Dumping Specific Buffers


To dump all buffers currently in the cache for a specific block
First identify the tablespace number for the block e.g for 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>';

72
where level is the tablespace number + 1
© 2005 Julian Dyke
juliandyke.com
Dumping Specific Buffers


Identify the RDBA for the block
This is equal to
RELATIVE_FNO * 4194304 + BLOCK_NUMBER

e.g. for block 5/127874
= 5 * 4194304 + 127874 = 21099394

Dump the buffer using
ALTER SESSION SET EVENTS
'immediate trace name buffer level <level>';

where level is the RDBA e.g
ALTER SESSION SET EVENTS
'immediate trace name buffer level 21099394';
73
© 2005 Julian Dyke
juliandyke.com
Flushing the Buffer Cache

In Oracle 9.0.1 and above, to flush the buffer cache use
ALTER SYSTEM SET EVENTS
'immediate trace name flush_cache';

In Oracle 10.1 and above, to flush the buffer cache use
ALTER SYSTEM FLUSH BUFFER_CACHE;



74
Appears to flush all buffer caches
Only flushes unpinned buffers
Moves all unpinned buffers to auxiliary replacement list
© 2005 Julian Dyke
juliandyke.com
State Objects


State objects are structures in the SGA describing state of
various database entities
Common state objects include
# Description
2 Process
3 Call
4 Session
6 Enqueue
24 Buffer
34 DML Locks
38 Transaction
51 Library Object
52 Library Object Pin
61 Hash Table
75
© 2005 Julian Dyke
juliandyke.com
Dumping Process State

A process state dump contains all the state objects for the
process
ALTER SESSION SET EVENTS
'immediate trace name processstate level 10';

Amount of library cache dump output for state object dumps
can be limited using event 10065
Level
Description
1 Address of library object only
2 As 1 plus library object lock details
3 As 2 plus library object handle and library object

76
Level 3 is the default
© 2005 Julian Dyke
juliandyke.com
Dumping System State

A system state dump contains a process state dump for each
process
ALTER SESSION SET EVENTS
'immediate trace name systemstate level 10';


77
This prints a large amount of information
Use HANG_ANALYZE for details on currently active processes
© 2005 Julian Dyke
juliandyke.com
Dumping Hang Analysis

A hang analysis dump prints process state for active
processes – e.g. waiting, blocking or spinning
ALTER SESSION SET EVENTS
'immediate trace name hanganalyze level 5';




78
Available in 8.0.6, 8.1.6 and above
Not available in 8.1.5
Contains
 system state level 1
 process states
 error stacks
Oracle recommends level 5
© 2005 Julian Dyke
juliandyke.com
Dumping Error Stack


An error stack describes the current state of a process.
Includes the current SQL statement and the process state for
the process.
ALTER SESSION SET EVENTS
'immediate trace name errorstack level <level>';

Levels are
Level
Description
0 Error stack only
1 Error stack and function call stack
2 As 1 plus the process state
3 As 2 plus the context area
79
© 2005 Julian Dyke
juliandyke.com
Dumping Enqueues & Latches

To dump the current state of the enqueues
ALTER SESSION SET EVENTS
'immediate trace name enqueues level <level>';

Levels include
Level
Description
1 Hash Table
2 Enqueue headers
3 Enqueue details

To dump the current state of all latches
ALTER SESSION SET EVENTS
'immediate trace name latches level <level>';

Levels include
Level
Description
1 Latches
2 Include statistics
80
© 2005 Julian Dyke
juliandyke.com
Dumping Granules


In Oracle 9.0.1 and above
To dump the current state of all granules
ALTER SESSION SET EVENTS
'immediate trace name granulelist level <level>';

Levels include
Level
Description
255 Null Granule
511 Home Granule
767 Shared Pool
1023 Large Pool
1279 Java Pool
1535 Buffer Pool

81
Alternatively use X$KSMGE
© 2005 Julian Dyke
juliandyke.com
Dumping Shared Server / MTS

To dump the current state of shared servers and dispatchers
ALTER SESSION SET EVENTS
'immediate trace name shared_server_state level <level>';

Levels 1-14 give increasing amounts of detail
In 8.1.7 and below this is called MTSSTATE

To trace dispatcher behaviour

EVENT='10248 trace name context forever, level 10';

To trace shared server behaviour
EVENT='10249 trace name context forever, level 10';
82
© 2005 Julian Dyke
juliandyke.com
Oracle Net Services
83

Tracing can be set for
 SQL*Net Client
 SQL*Net Server
 Listener
 TNSPING Utility
 Names
 NAMESCTL Utility

See Metalink Note 219968.1
© 2005 Julian Dyke
juliandyke.com
Intelligent Agent
84

Tracing can be set for
 Intelligent Agent
 Agent Job Subsystem
 AGENTCTL Utility
 Data Collection
 Event Subsystem

See Metalink Note 177236.1
© 2005 Julian Dyke
juliandyke.com
RAC

In ORADEBUG OPS/RAC trace can be obtained using LKDEBUG
ORADEBUG LKDEBUG -h
Usage:lkdebug [options]
-l [r|p] <enqueue pointer>
-r <resource pointer>
-b <gcs shadow pointer>
-p <process id>
-P <process pointer>
-O <i1> <i2> <types>
-a <res/lock/proc/pres>
-a <res> [<type>]
-a convlock
-a convres
-a name
-a hashcount
-t
-s
-k
85
© 2005 Julian Dyke
Enqueue Object
Resource Object
GCS shadow Object
client pid
Process Object
Oracle Format resname
all <res/lock/proc/pres> pointers
all <res> pointers by an optional type
all converting enqueue (pointers)
all res ptr with converting enqueues
list all resource names
list all resource hash bucket counts
Traffic controller info
summary of all enqueue types
GES SGA summary info
juliandyke.com
RAC

All instances can be specified for subsequent commands
using
ORADEBUG SETINST "all"

A list of instances can be specified using
ORADEBUG SETINST "1 2"


The –g command prefix can also be used with LKDEBUG
e.g for the default instance
ORADEBUG –g DEF LKDEBUG –s

e.g for a list of instances
ORADEBUG –g "1 2" LKDEBUG –a lock
86
© 2005 Julian Dyke
juliandyke.com
RAC

To dump the current state of the global cache elements with
block dumps
ALTER SESSION SET EVENTS
'immediate trace name locks level <level>';

To dump the current state of global cache elements
ALTER SESSION SET EVENTS
'immediate trace name gc_elements level <level>';
87
© 2005 Julian Dyke
juliandyke.com
RAC

To trace RAC buffer cache activity use
ALTER SESSION SET EVENTS
'10708 trace name context forever, level 10';

To trace which enqueues are being obtained use
ALTER SESSION SET EVENTS
'10704 trace name context forever, level 10';

To trace global enqueue manipulation use
ALTER SESSION SET EVENTS
'10706 trace name context forever, level 1';


To restrict the amount of information generated by this trace
set _ksi_trace
e.g to restrict trace to TM and TX locks set
_ksi_trace = 'TMTX';
88

Instance(s) must be restarted after setting this parameter
© 2005 Julian Dyke
juliandyke.com
RAC
89

Tracing can be set for
 GSD
 GSDCTL
 SRVCONFIG
 SRVCTL

In Oracle 9.0.1 and 9.2
 See Metalink Note 178683.1

In Oracle 10.1 and above
 Set the environment variable SRVM_TRACE to true
© 2005 Julian Dyke
juliandyke.com
Any Questions?
Julian Dyke
Intel Solution Services
650 Wharfedale Road
Winnersh Triangle
Wokingham
Berkshire
RG41 5TP
[email protected]
90
© 2005 Julian Dyke
juliandyke.com