Oracle Diagnostics Julian Dyke Independent Consultant Web Version
Oracle Diagnostics Julian Dyke Independent Consultant Web Version © 2005 Julian Dyke juliandyke. com
Warning 2 u Much of the content of this presentation is undocumented and unsupported by Oracle u Check with Oracle support before using any of these features in a production environment © 2005 Julian Dyke juliandyke. com
Trace Parameters u To include timed statistics in trace files timed_statistics = TRUE u To specify the log file destination user_dump_dest = '<directory_name>' background_dump_dest = '<directory_name'> u To specify maximum trace file size max_dump_file_size = <size> u To allow other users to read trace files _trace_files_public = TRUE 3 © 2005 Julian Dyke juliandyke. com
Trace File Identifier u In Oracle 8. 1. 7 and above, a trace file identifier can be specified tracefile_identifier = '<identifier>' u e. g. in Oracle 9. 2 if a trace file is called ss 92001_ora_1760. trc u then the statement ALTER SESSION SET tracefile_identifier = 'test'; u will change the file name to ss 92001_ora_1760_test. trc 4 © 2005 Julian Dyke juliandyke. com
Trace File Names u u u In Oracle 9. 2 foreground process trace file names are in the following formats Process Type Name ss 92001_ora_1234. trc Foreground Process ss 92001_p 000_1234. trc Parallel execution slave ss 92001_j 000_1234. trc Job queue process ss 92001_d 000_1234. trc Dispatcher process ss 92001_s 000_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 ss 92001_<process_name>_1234. trc u 5 These trace files are written to the BACKGROUND_DUMP_DEST directory © 2005 Julian Dyke juliandyke. com
Events 6 u There are four types of numeric events u Immediate dumps u Conditional dumps u Trace dumps u Change database behaviour u Each event has 1 or more level which can be u range e. g. 1 to 10 u bitmask e. g. 0 x 01 0 x 02 0 x 04 0 x 08 0 x 10 etc u flag e. g. 0 = off; 1 = on u identifier e. g. object id, memory address, etc © 2005 Julian Dyke juliandyke. com
Events u 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>'; u To enable a numeric event at session level ALTER SESSION SET EVENTS '<event> trace name context forever, level <level>'; u Alternatively use u u 7 ORADEBUG DBMS_SYSTEM. SETEV © 2005 Julian Dyke juliandyke. com
Events u To dump all event messages SET SERVEROUTPUT ON DECLARE err_msg VARCHAR 2(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 u On Unix systems event messages are in the formatted text file $ORACLE_HOME/rdbms/mesg/oraus. msg u 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 u 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 u u u 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 u To trace the computations performed by the CBO when optimising SQL statements use ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL <level>'; u 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 u u 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 u u Tracing Bitmap Indexes 10608 Bitmap Index Creation 10 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 Data sent/received via SQL*Net 10241 Remote SQL Execution © 2005 Julian Dyke 1 10 juliandyke. com
Events u u 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 u At the session level -- Enable SQL trace ALTER SESSION SET sql_trace = TRUE; -- Disable SQL trace ALTER SESSION SET sql_trace = FALSE; u 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 u To enable at instance level # Enable SQL trace sql_trace = TRUE # Enable SQL*trace with binds event = '10046 trace name context forever, level 4'; u 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 u Example (Oracle 9. 2. 0 on Windows 2000) SET SUFFIX TRC COLUMN filename NEW_VALUE filename SELECT p 1. value||''||p 2. value||'_ora_'||p. spid filename FROM v$process p, v$session s, v$parameter p 1, v$parameter p 2 WHERE p 1. name = 'user_dump_dest' AND p 2. 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 u To enable circular tracing ALTER SESSION SET EVENTS 'immediate trace name trace_buffer_on level <level>'; u where <level> is the size of the trace buffer in bytes u 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 u Event 10046 level 1 trace can be enabled using DBMS_SESSION. SET_SQL_TRACE ( FLAG BOOLEAN -- TRUE to enable; -- FALSE to disable ); u u 20 Useful within PL/SQL blocks ALTER SESSION privilege not required © 2005 Julian Dyke juliandyke. com
Using System Triggers u Login as SYS (AS SYSDBA) CREATE OR REPLACE TRIGGER us 01_logon AFTER LOGON ON us 01. SCHEMA BEGIN dbms_session. set_sql_trace (TRUE); END; CREATE OR REPLACE TRIGGER us 01_logoff BEFORE LOGOFF ON us 01. SCHEMA BEGIN dbms_session. set_sql_trace (FALSE); END; ALTER TRIGGER us 01_login ENABLE; ALTER TRIGGER us 01_login DISABLE; 21 © 2005 Julian Dyke juliandyke. com
DBMS_SYSTEM u u u 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 u 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 ); u 23 SID and Serial number can be found in V$SESSION (SID and SERIAL#) © 2005 Julian Dyke juliandyke. com
DBMS_SYSTEM u 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 VARCHAR 2, -- Parameter Name SET_BOOL_PARAM_IN_SESSION BVAL BOOLEAN -- Value ); u For example EXECUTE DBMS_SYSTEM. SET_BOOL_PARAM_IN_SESSION (9, 27, 'hash_join_enabled', TRUE); u 24 Note: does not work with SQL_TRACE © 2005 Julian Dyke juliandyke. com
DBMS_SYSTEM u 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 VARCHAR 2, -- Parameter Name SET_BOOL_PARAM_IN_SESSION INTVAL INTEGER -- Value ); u For example EXECUTE DBMS_SYSTEM. SET_INT_PARAM_IN_SESSION (9, 27, 'sort_area_size', 131072); 25 © 2005 Julian Dyke juliandyke. com
DBMS_SYSTEM u To set an event in another session use DBMS_SYSTEM. SET_EV ( SI NUMBER, SE NUMBER, EV NUMBER, LE NUMBER, NM VARCHAR 2 ); u 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 u To write to trace files and/or alert log use DBMS_SYSTEM. KSDWRT ( DEST NUMBER, TST VARCHAR 2 ); u -- 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 u To write the date and time to a trace file use EXECUTE DBMS_SYSTEM. KSDDDT; u To flush the contents of the trace buffer to disk use EXECUTE DBMS_SYSTEM. KSDFLS; u To indent output in the trace file use EXECUTE DBMS_SYSTEM. KSDIND (<level>); u 28 This will prefix KSDWRT output with <level> colons © 2005 Julian Dyke juliandyke. com
DBMS_SUPPORT u u 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; u To get SID of current session use FUNCTION DBMS_SUPPORT. MYSID RETURN BOOLEAN; u 29 This function executes the query © 2005 Julian Dyke SELECT sid FROM v$mystat WHERE ROWNUM = 1; juliandyke. com
DBMS_SUPPORT u 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) ); u To disable use DBMS_SUPPORT. STOP_TRACE; 30 © 2005 Julian Dyke juliandyke. com
DBMS_SUPPORT u 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) ); u 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 u u 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 ); u 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 u u Trace can be enabled using client identifiers To set a client identifier use DBMS_SESSION. SET_IDENTIFIER ( CLIENT_ID VARCHAR 2 -- Client ID ); u 33 The client identifier for a specific session can be found by querying V$SESSION. CLIENT_IDENTIFIER © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u Trace can be enabled using client identifiers u 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 ); u 34 Trace can be disabled using DBMS_MONITOR. CLIENT_ID_TRACE_DISABLE © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR 35 u Trace can be enabled for a specific u service and module u service, module and action u To add a service in a RAC database u DBCA u Enterprise Manager (Oracle 10. 2 and above) © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u u To add a service in a single instance environment Set the SERVICE_NAMES parameter e. g. service_names = 'LX 101001, SERVICE 1' u Add the service to TNSNAMES. ORA e. g. SERVICE 1 = (DESCRIPTON = (ADDRESS = (PROTOCOL=TCP)(HOST=server 1)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = SERVICE 1) ) ) 36 © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u To specify a module and action use DBMS_APPLICATION_INFO. SET_MODULE ( MODULE_NAME VARCHAR 2, -- Module ACTION_NAME VARCHAR 2 -- Action ); u To specify subsequent actions use DBMS_APPLICATION_INFO. SET_ACTION ( ACTION_NAME VARCHAR 2 ); 37 © 2005 Julian Dyke -- Action juliandyke. com
DBMS_MONITOR u To enable trace for a specific module and action use DBMS_MONITOR. SERV_MOD_ACT_TRACE_ENABLE ( SERVICE_NAME VARCHAR 2, -- Service Name MODULE_NAME VARCHAR 2, -- Module ACTION_NAME VARCHAR 2, -- Action WAITS BOOLEAN, -- Waits BINDS BOOLEAN, -- Binds INSTANCE_NAME VARCHAR 2 -- Instance ); 38 u If ACTION_NAME is not specified, entire module will be traced u Tracing can be disabled using SERV_MOD_ACT_TRACE_DISABLE © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u To enable statistics collection for a specific client DBMS_MONITOR. CLIENT_ID_STAT_ENABLE ( CLIENT_ID VARCHAR 2 -- Client ID ); u Statistics externalized in V$CLIENT_STATS Disable using DBMS_MONITOR. CLIENT_ID_STAT_DISABLE u To enable statistics collection for a specific module/action u DBMS_MONITOR. SERV_MOD_ACT_STAT_ENABLE ( SERVICE_NAME VARCHAR 2, -- Service Name MODULE_NAME VARCHAR 2, -- Module ACTION_NAME VARCHAR 2 -- Action ); u u 39 Statistics externalized in V$SERV_MOD_ACT_STATS Disable using DBMS_MONITOR. SERV_MOD_ACT_STAT_DISABLE © 2005 Julian Dyke juliandyke. com
trcsess u u u 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> u u where trace_file_names can be space separated list of file names or '*' wildcard service, action and module names are case sensitive trcsess service=APP 1 module=MODULE 1 action=ACTION 1 * 40 © 2005 Julian Dyke juliandyke. com
DBA_ENABLED_TRACES u u Introduced in Oracle 10. 1 Type TRACE_TYPE VARCHAR 2(21) PRIMARY_ID VARCHAR 2(64) QUALIFIER_ID 1 VARCHAR 2(48) QUALIFIER_ID 2 VARCHAR 2(32) WAITS VARCHAR 2(5) BINDS VARCHAR 2(5) INSTANCE_NAME VARCHAR 2(16) Trace type can be u u 41 Name CLIENT_ID SERVICE_MODULE_ACTION u Based on WRI$_TRACING_ENABLED © 2005 Julian Dyke juliandyke. com
ORADEBUG u Undocumented debugging utility available u u u 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 u To list the available options ORADEBUG HELP 42 © 2005 Julian Dyke juliandyke. com
ORADEBUG u There are three ways of selecting a process using ORADEBUG u Use current process SQL> ORADEBUG SETMYPID u Use Oracle PID (V$PROCESS. PID) SQL> ORADEBUG SETORAPID <pid> u Use Operating System PID (V$PROCESS. SPID) SQL> ORADEBUG SETOSPID <spid> u 43 This is the PID in Unix and the Thread ID in Windows NT/2000 © 2005 Julian Dyke juliandyke. com
ORADEBUG u To display the name of the current trace file use ORADEBUG TRACEFILE_NAME u To set the maximum size of the current trace file to UNLIMITED use ORADEBUG UNLIMIT u To flush the current trace file use ORADEBUG FLUSH u To close the current trace file use ORADEBUG CLOSE_TRACE 44 © 2005 Julian Dyke juliandyke. com
ORADEBUG u To list the available dumps ORADEBUG DUMPLIST u To perform a dump ORADEBUG DUMP <dumpname> <level> u 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 u To suspend the current process ORADEBUG SUSPEND u To resume the current process ORADEBUG RESUME 46 u While the process is suspended ORADEBUG can be used to dump perform memory/state dumps u Can be also used to temporarily suspend long running processes © 2005 Julian Dyke juliandyke. com
ORADEBUG u To dump the events currently set use ORADEBUG DUMP EVENTS <level> u u 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 u To enable events in another process u 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>; u 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 u To dump the value of an SGA variable use ORADEBUG DUMPVAR SGA <variable name> u For example ORADEBUG DUMPVAR SGA kcbnhb u u u 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 u In some versions it is possible to dump the entire SGA to file u Freeze the instance using ORADEBUG FFBEGIN u Dump the SGA to file using ORADEBUG SGATOFILE '<directory name>' u Unfreeze the instance using ORADEBUG FFRESUMEINST 50 © 2005 Julian Dyke juliandyke. com
Immediate Dumps u There are three ways of taking an immediate dump u In the current session ALTER SESSION SET EVENTS 'immediate trace name <dump> level <level>'; u In ORADEBUG DUMP <dump> <level> u Using DBMS_SYSTEM EXECUTE DBMS_SYSTEM. SET_EV (sid, serial#, 65535, <level>, '<dump'>); u 51 Cannot be invoked from init. ora © 2005 Julian Dyke juliandyke. com
Conditional Dumps u u Invoked when an error occurs In the init. ora file event = "<error> trace name <dump> level <level>" u In the current session ALTER SESSION SET EVENTS '<error> trace name <dump> level <level>'; u In 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 u To dump the internal representation of columns use the DUMP built-in function DUMP (COLUMN_VALUE, FORMAT) u 53 where FORMAT is © 2005 Julian Dyke Format Description 8 Octal 10 Decimal 16 Hexadecimal 17 Single Character juliandyke. com
Dumping Columns u For example SELECT DUMP (1001, 16) FROM dual; u returns Typ=2 Len=3: c 2, b, 2 u To output a column in hexadecimal use the 'XXXX' format mask e. g. SELECT TO_CHAR (65536, 'XXXX') FROM dual; u returns 10000 54 © 2005 Julian Dyke juliandyke. com
Dumping a Database Block u u To dump a database block in Oracle 7 File number / block number must be converted into a data block address COLUMN decimal. DBA new_value decimal. DBA SELECT dbms_utility. make_data_block_address (&file, &block) decimal. DBA FROM dual; ALTER SESSION SET EVENTS 'immediate trace name blockdump level &decimal. DBA'; 55 © 2005 Julian Dyke juliandyke. com
Dumping a Database Block u To dump a database block in Oracle 8. 0 or above ALTER SYSTEM DUMP DATAFILE <absolute_file_number> BLOCK <block_number>; u To dump a range of database blocks ALTER SYSTEM DUMP DATAFILE <absolute_file_number> BLOCK MIN <minimum_block_number> BLOCK MAX <maximum_block_number>; u 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) u To dump a database block in hexadecimal enable event 10289 ALTER SESSION SET EVENTS '10289 trace name context forever, level 1'; u Then dump the block using ALTER SYSTEM DUMP DATAFILE <absolute_file_number> BLOCK <block_number>; u On Unix/Linux systems blocks can also be dumped using od dd bs=8 k if=<filename> skip=200 count=4 | od -x u Force DBWR to flush recently written blocks to disk using ALTER SYSTEM CHECKPOINT; u or ALTER SYSTEM SWITCH LOGFILE; 57 © 2005 Julian Dyke juliandyke. com
Dumping an Index u An index tree can be dumped using ALTER SESSION SET EVENTS 'immediate trace name treedump level <object_id>; 58 u where object_id is the object number of the index (in DBA_OBJECTS) u Dumps u branches u leaves u contents of leaf blocks © 2005 Julian Dyke juliandyke. com
Dumping an Index u In Oracle 9. 2 treedump may crash if index has been created by a primary/unique constraint e. g. CREATE TABLE t 1 (c 01 NUMBER PRIMARY KEY); CREATE TABLE t 1 (c 01 NUMBER); ALTER TABLE t 1 ADD CONSTRAINT t 1 pk PRIMARY KEY (c 01); u u 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 u To dump an undo segment header use ALTER SYSTEM DUMP UNDO_HEADER '<segment_name>'; u To dump an undo transaction first obtain the XID using SELECT xidusn, xidslot, xidsqn FROM v$transaction; u 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 u To identify the current redo log SELECT member FROM v$logfile WHERE group# = ( SELECT group# FROM v$log WHERE status = 'CURRENT' ); u To dump a redo log file use ALTER SYSTEM DUMP LOGFILE '<logfilename>'; u 61 Also works for archived redo logs © 2005 Julian Dyke juliandyke. com
Other File Dumps u Control Files ALTER SESSION SET EVENTS 'immediate trace name controlf level 15'; u File Headers ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 7'; u Redo Log Headers ALTER SESSION SET EVENTS 'immediate trace name redohdr level 3'; 62 © 2005 Julian Dyke juliandyke. com
Dumping the Library Cache u To dump the library cache ALTER SESSION SET EVENTS 'immediate trace name library_cache level <level>'; u 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 u To dump the row (dictionary) cache ALTER SESSION SET EVENTS 'immediate trace name row_cache level <level>'; u 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 u To dump the fixed memory areas ALTER SESSION SET EVENTS immediate trace name global_area level <level>'; u 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 u 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; u The fixed SGA can also be dumped using ORADEBUG DUMPSGA 66 © 2005 Julian Dyke juliandyke. com
Dumping Heap Memory To dump heap memory use u ALTER SESSION SET EVENTS 'immediate trace name heapdump level <level>'; u 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 u In Oracle 9. 0. 1 and below ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level <level>'; u u u Get address of subheap e. g. 0 x 8057 eb 78 Convert to decimal e. g. 2153245560 For a summary dump use ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2153245560'; u 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 u In Oracle 9. 2 and above u For a summary dump use ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 1 addr 0 x 8057 eb 78'; u For a detailed dump use ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2 addr 0 x 8057 eb 78 '; 69 © 2005 Julian Dyke juliandyke. com
SGA Memory u u 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; u 70 X$KSMMEM is unreliable in Windows NT/2000 (causes ORA-3113) © 2005 Julian Dyke juliandyke. com
Dumping Buffers u To dump buffer headers and buffer contents ALTER SESSION SET EVENTS 'immediate trace name buffers level <level>'; u 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 u u To dump all buffers currently in the cache for a specific block First identify the tablespace number for the block e. g for TS 01 SELECT ts# FROM sys. ts$ WHERE name = 'TS 01'; u Set the tablespace number using ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p 1 level <level>'; u 72 where level is the tablespace number + 1 © 2005 Julian Dyke juliandyke. com
Dumping Specific Buffers u u Identify the RDBA for the block This is equal to RELATIVE_FNO * 4194304 + BLOCK_NUMBER u e. g. for block 5/127874 = 5 * 4194304 + 127874 = 21099394 u Dump the buffer using ALTER SESSION SET EVENTS 'immediate trace name buffer level <level>'; u 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 u In Oracle 9. 0. 1 and above, to flush the buffer cache use ALTER SYSTEM SET EVENTS 'immediate trace name flush_cache'; u In Oracle 10. 1 and above, to flush the buffer cache use ALTER SYSTEM FLUSH BUFFER_CACHE; u u u 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 u u 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 u A process state dump contains all the state objects for the process ALTER SESSION SET EVENTS 'immediate trace name processstate level 10'; u 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 u 76 Level 3 is the default © 2005 Julian Dyke juliandyke. com
Dumping System State u A system state dump contains a process state dump for each process ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10'; u u 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 u 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'; u u 78 Available in 8. 0. 6, 8. 1. 6 and above Not available in 8. 1. 5 Contains u system state level 1 u process states u error stacks Oracle recommends level 5 © 2005 Julian Dyke juliandyke. com
Dumping Error Stack u u 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>'; u 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 u To dump the current state of the enqueues ALTER SESSION SET EVENTS 'immediate trace name enqueues level <level>'; u Levels include Level Description 1 Hash Table 2 Enqueue headers 3 Enqueue details u To dump the current state of all latches ALTER SESSION SET EVENTS 'immediate trace name latches level <level>'; u Levels include Level Description 1 Latches 80 © 2005 Julian Dyke 2 Include statistics juliandyke. com
Dumping Granules u u 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>'; u Levels include Level Description 255 Null Granule 511 Home Granule 767 Shared Pool 1023 Large Pool 1279 Java Pool 1535 Buffer Pool u 81 Alternatively use X$KSMGE © 2005 Julian Dyke juliandyke. com
Dumping Shared Server / MTS u To dump the current state of shared servers and dispatchers ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level <level>'; u Levels 1 -14 give increasing amounts of detail In 8. 1. 7 and below this is called MTSSTATE u To trace dispatcher behaviour u EVENT='10248 trace name context forever, level 10'; u To trace shared server behaviour EVENT='10249 trace name context forever, level 10'; 82 © 2005 Julian Dyke juliandyke. com
Oracle Net Services 83 u Tracing can be set for u SQL*Net Client u SQL*Net Server u Listener u TNSPING Utility u Names u NAMESCTL Utility u See Metalink Note 219968. 1 © 2005 Julian Dyke juliandyke. com
Intelligent Agent 84 u Tracing can be set for u Intelligent Agent u Agent Job Subsystem u AGENTCTL Utility u Data Collection u Event Subsystem u See Metalink Note 177236. 1 © 2005 Julian Dyke juliandyke. com
RAC u 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 <i 1> <i 2> <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 u All instances can be specified for subsequent commands using ORADEBUG SETINST "all" u A list of instances can be specified using ORADEBUG SETINST "1 2" u u The –g command prefix can also be used with LKDEBUG e. g for the default instance ORADEBUG –g DEF LKDEBUG –s u e. g for a list of instances ORADEBUG –g "1 2" LKDEBUG –a lock 86 © 2005 Julian Dyke juliandyke. com
RAC u To dump the current state of the global cache elements with block dumps ALTER SESSION SET EVENTS 'immediate trace name locks level <level>'; u 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 u To trace RAC buffer cache activity use ALTER SESSION SET EVENTS '10708 trace name context forever, level 10'; u To trace which enqueues are being obtained use ALTER SESSION SET EVENTS '10704 trace name context forever, level 10'; u To trace global enqueue manipulation use ALTER SESSION SET EVENTS '10706 trace name context forever, level 1'; u u 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 u Instance(s) must © 2005 Julian Dyke be restarted after setting this parameter juliandyke. com
RAC 89 u Tracing can be set for u GSDCTL u SRVCONFIG u SRVCTL u In Oracle 9. 0. 1 and 9. 2 u See Metalink Note 178683. 1 u In Oracle 10. 1 and above u 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 RG 41 5 TP julian. dyke@intel. com 90 © 2005 Julian Dyke juliandyke. com
- Slides: 90