Oracle Diagnos.ppt

1、Julian Dyke Independent Consultant,OracleDiagnostics,Web Version, 2005 Julian Dyke,2,Warning,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,3,Trace Parameters,To include timed

2、 statistics in trace files,timed_statistics = TRUE,To specify the log file destination,_trace_files_public = TRUE,max_dump_file_size = ,user_dump_dest = background_dump_dest = ,To specify maximum trace file size,To allow other users to read trace files,4,Trace File Identifier,In Oracle 8.1.7 and abo

3、ve, a trace file identifier can be specified,tracefile_identifier = ,e.g. in Oracle 9.2 if a trace file is called,ss92001_ora_1760.trc,ss92001_ora_1760_test.trc,then the statement,will change the file name to,ALTER SESSION SET tracefile_identifier = test;,5,Trace File Names,In Oracle 9.2 foreground

4、process trace file names are in the following formats,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_1234.trc,These trace files are written to the BACKGROUND_DUMP_DEST directory,6,Events,There are four types o

5、f 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. 0 x01 0 x02 0 x04 0 x08 0 x10 etc flag e.g. 0 = off; 1 = on identifier e.g. object id, memory address, etc,7,Events,To enable a numeric

6、 event at instance level,# In init.ora fileevent = trace name context forever, level ;,ALTER SYSTEM SET EVENTS trace name context forever, level ;,To enable a numeric event at session level,ALTER SESSION SET EVENTS trace name context forever, level ;,Alternatively use ORADEBUG DBMS_SYSTEM.SETEV,8,Ev

7、ents,To dump all event messages,SET SERVEROUTPUT ON DECLAREerr_msg VARCHAR2(120);BEGINDBMS_OUTPUT.ENABLE (1000000);FOR err_num IN 10000.10999 LOOPerr_msg := SQLERRM (-err_num);IF err_msg NOT LIKE %Message |err_num| not found% THENDBMS_OUTPUT.PUT_LINE (err_msg);END IF;END LOOP;END;/,9,Events,On Unix

8、systems event messages are in the formatted text file,event=10000while $event -ne 10999 doevent=expr $event + 1oerr ora $eventdone,To print detailed event messages (Unix only),$ORACLE_HOME/rdbms/mesg/oraus.msg,10,Events,To check which events are enabled in the current session,SET SERVEROUTPUT ON DEC

9、LAREl_level NUMBER;BEGINFOR l_event IN 10000.10999 LOOPdbms_system.read_ev (l_event,l_level);IF (l_level 0) THENdbms_output.put_line (Event |TO_CHAR (l_event) | is set at level |TO_CHAR (l_level);END IF;END LOOP;END;/,11,SQL Trace,SQL_TRACE is event 10046 level 1 Other levels are,See Metalink Note 3

10、9817.1 for details of trace output,12,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 ;,See A Look under the Hood of CBO : The 10053 EventWolfgang Breitling - ,13,Events,Tracing SQL

11、 Execution,Tracing Parallel Execution,14,Events,Tracing Bitmap Indexes,Tracing Remote Processing,15,Events,Tracing Space Management,Tracing Undo/Read Consistency,16,Enabling SQL Trace,At the session level,For extended trace use,- Enable SQL traceALTER SESSION SET sql_trace = TRUE;,- Enable SQL trace

12、 with bindsALTER SESSION SET EVENTS10046 trace name context forever, level 4;,- Disable SQL traceALTER SESSION SET sql_trace = FALSE;,- Disable SQL trace with bindsALTER SESSION SET EVENTS10046 trace name context off;,17,Enabling SQL Trace,To enable at instance level,# Enable SQL tracesql_trace = TR

13、UE,# Enable SQL*trace with bindsevent = 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 EVENTS10046 trace name context forever, level 1;,- Disable SQL trace for instanceALT

14、ER SYSTEM SET EVENTS10046 trace name context off;,18,Editing a Trace File from SQL*Plus,Example (Oracle 9.2.0 on Windows 2000),SET SUFFIX TRCCOLUMN filename NEW_VALUE filename SELECT p1.value|p2.value|_ora_|p.spid filenameFROM v$process p, v$session s, v$parameter p1, v$parameter p2WHERE = u

15、ser_dump_destAND = db_nameAND p.addr = s.paddrAND s.audsid = USERENV (SESSIONID); EDIT ,where 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;,20,DBMS_SESSION,Event 10046 level 1 trace c

16、an be enabled using,DBMS_SESSION.SET_SQL_TRACE( FLAG BOOLEAN- TRUE to enable; - FALSE to disable );,Useful within PL/SQL blocks ALTER SESSION privilege not required,21,Using System Triggers,Login as SYS (AS SYSDBA),ALTER TRIGGER us01_login ENABLE;,CREATE OR REPLACE TRIGGER us01_logoffBEFORE LOGOFF O

17、N us01.SCHEMABEGINdbms_session.set_sql_trace (FALSE);END;,CREATE OR REPLACE TRIGGER us01_logonAFTER LOGON ON us01.SCHEMABEGINdbms_session.set_sql_trace (TRUE);END;,ALTER TRIGGER us01_login DISABLE;,22,DBMS_SYSTEM,Undocumented package Installed in all versions Owned by SYS user,GRANT EXECUTE ON DBMS_

18、SYSTEM TO ; CREATE PUBLIC SYNONYM dbms_system FOR sys.dbms_system;,$ORACLE_HOME/rdbms/admin/dbmsutil.sql,23,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 );,SID

19、and Serial number can be found in V$SESSION (SID and SERIAL#),24,To set a Boolean parameter in another session use,ORADEBUG SUSPEND,For example,SET_BOOL_PARAM_IN_SESSION,DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION( SID NUMBER,- SID SERIAL# NUMBER,- Serial NumberPARNAMVARCHAR2,- Parameter NameBVALBOOLEAN-

20、Value);,EXECUTE DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(9, 27, hash_join_enabled, TRUE);,Note: does not work with SQL_TRACE,DBMS_SYSTEM,25,To set an integer parameter in another session use,ORADEBUG SUSPEND,For example,SET_BOOL_PARAM_IN_SESSION,DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION( SID NUMBER,- SID SE

21、RIAL# NUMBER,- Serial NumberPARNAMVARCHAR2,- Parameter NameINTVALINTEGER- Value);,EXECUTE DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(9, 27, sort_area_size, 131072);,DBMS_SYSTEM,26,DBMS_SYSTEM,To set an event in another session use,DBMS_SYSTEM.SET_EV( SI NUMBER,- SID SE NUMBER,- Serial NumberEV NUMBER,- Ev

22、ent Number e.g. 10046 LE NUMBER,- Level e.g. 1NM VARCHAR2- Action Name can be );,Disable using same SID, serial number and event with level 0,27,DBMS_SYSTEM,To write to trace files and/or alert log use,DBMS_SYSTEM.KSDWRT(DEST NUMBER,- 1 = Trace File, 2 = Alert LogTST VARCHAR2- Message);,Example,BEGI

23、N DBMS_SYSTEM.KSDWRT (1, Output to trace file); DBMS_SYSTEM.KSDWRT (2, Output to alert log);END;/,28,DBMS_SYSTEM,To write the date and time to a trace file use,EXECUTE DBMS_SYSTEM.KSDDDT;,To indent output in the trace file use,EXECUTE DBMS_SYSTEM.KSDIND ();,To flush the contents of the trace buffer

24、to disk use,EXECUTE DBMS_SYSTEM.KSDFLS;,This will prefix KSDWRT output with colons,29,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 ; C

25、REATE PUBLIC SYNONYM dbms_support FOR sys.dbms_support;,FUNCTION DBMS_SUPPORT.MYSIDRETURN BOOLEAN;,SELECT sid FROM v$mystatWHERE ROWNUM = 1;,This function executes the query,To get SID of current session use,30,DBMS_SUPPORT,To enable SQL trace in the current session use,DBMS_SUPPORT.START_TRACE( WAI

26、TS BOOLEAN,- Include waits (default FALSE) BINDS BOOLEAN- Include binds (default FALSE);,DBMS_SUPPORT.STOP_TRACE;,To disable use,31,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

27、 waits (default FALSE) BINDS BOOLEAN- Include binds (default FALSE);,DBMS_SUPPORT.STOP_TRACE_IN_SESSION( SI NUMBER,- SID SE NUMBER- Serial Number (can be 0);,To disable use,32,DBMS_MONITOR,Introduced in Oracle 10.1 To enable trace in another session use,DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID

28、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 );,33,DBMS_MONITOR,Trace can be enabled using client ident

29、ifiers To set a client identifier use,The client identifier for a specific session can be found by querying V$SESSION.CLIENT_IDENTIFIER,DBMS_SESSION.SET_IDENTIFIER( CLIENT_ID VARCHAR2- Client ID );,34,DBMS_MONITOR,Trace can be enabled using client identifiers,To enable trace for a specific client us

30、e,DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE( CLIENT_ID NUMBER,- Client ID WAITS BOOLEAN,- Include Waits BINDS BOOLEAN- Include Binds );,Trace can be disabled using DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE,35,DBMS_MONITOR,Trace can be enabled for a specific service service and module service, module and action

31、 To add a service in a RAC database use DBCA Enterprise Manager (Oracle 10.2 and above),36,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 = (

32、PROTOCOL=TCP)(HOST=server1)(PORT=1521)(CONNECT_DATA =(SERVICE_NAME = SERVICE1),37,DBMS_MONITOR,DBMS_APPLICATION_INFO.SET_MODULE( MODULE_NAME VARCHAR2,- ModuleACTION_NAME VARCHAR2- Action );,To specify subsequent actions use,DBMS_APPLICATION_INFO.SET_ACTION( ACTION_NAME VARCHAR2- Action );,To specify

33、 a module and action use,38,DBMS_MONITOR,To enable trace for a specific module and action use,DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( SERVICE_NAME VARCHAR2,- Service NameMODULE_NAME VARCHAR2,- ModuleACTION_NAME VARCHAR2,- ActionWAITS BOOLEAN,- WaitsBINDS BOOLEAN,- BindsINSTANCE_NAME VARCHAR2- Instan

34、ce );,If ACTION_NAME is not specified, entire module will be traced,Tracing can be disabled using SERV_MOD_ACT_TRACE_DISABLE,39,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_S

35、TATS 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 NameMODULE_NAME VARCHAR2,- ModuleACTION_NAME VARCHAR2- Action);,Statistics externalized in V$SERV_MOD_ACT_STATS D

36、isable using DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE,40,trcsess,Introduced in Oracle 10.1 Conditionally extracts trace data Merges trace files,trcsessoutput = session = clientid = service = module = action = ,where trace_file_names can be space separated list of file names or * wildcard service, acti

37、on and module names are case sensitive,trcsess service=APP1 module=MODULE1 action=ACTION1 *,41,DBA_ENABLED_TRACES,Introduced in Oracle 10.1,Trace type can be CLIENT_ID SERVICE SERVICE_MODULE SERVICE_MODULE_ACTION,Based on WRI$_TRACING_ENABLED,42,ORADEBUG,Undocumented debugging utility available as a

38、 standalone utility on Unix (oradbx) as a standalone utility on VMS (orambx) within Server Manager (svrmgr) within SQL*Plus (8.1.5 and above),ORADEBUG HELP,To use ORADEBUG within SQL*Plus login using,To list the available options,SQLPLUS /NOLOGSQL CONNECT SYS/password AS SYSDBA,43,ORADEBUG,There are

39、 three ways of selecting a process using ORADEBUG,Use current process,SQL ORADEBUG SETMYPID,Use Oracle PID (V$PROCESS.PID),SQL ORADEBUG SETORAPID ,Use Operating System PID (V$PROCESS.SPID),SQL ORADEBUG SETOSPID ,This is the PID in Unix and the Thread ID in Windows NT/2000,44,ORADEBUG,To display the

40、name of the current trace file use,To flush the current trace file use,ORADEBUG TRACEFILE_NAME,ORADEBUG FLUSH,To close the current trace file use,ORADEBUG CLOSE_TRACE,To set the maximum size of the current trace file to UNLIMITED use,ORADEBUG UNLIMIT,45,ORADEBUG,To list the available dumps,ORADEBUG

41、DUMPLIST,E.g. for a level 4 dump of the library cache,ORADEBUG SETMYPIDORADEBUG DUMP LIBRARY_CACHE 4,ORADEBUG DUMP ,To perform a dump,46,ORADEBUG,To suspend the current process,While the process is suspended ORADEBUG can be used to dump perform memory/state dumps Can be also used to temporarily susp

42、end long running processes,ORADEBUG SUSPEND,To resume the current process,ORADEBUG RESUME,47,ORADEBUG,To dump the events currently set use,where level is,ORADEBUG DUMP EVENTS ,Output is written to the current trace file,48,ORADEBUG,To enable events in another process,SELECT pid FROM v$process p, v$s

43、ession s WHERE p.addr = s.paddr AND s.sid = ;,SQL ORADEBUG SETORAPID 8SQL ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12,e.g. to set event 10046 level 12 in Oracle process 8 use,For foreground processes Oracle Process ID can be obtained from Session ID using,49,ORADEBUG,To dump the value

44、of an SGA variable use,ORADEBUG DUMPVAR SGA ,For example,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.,ORADEBUG DUMPVAR SGA kcbnhb,50,ORADEBUG,In some versions it is poss

45、ible to dump the entire SGA to file Freeze the instance using,ORADEBUG FFBEGIN,Dump the SGA to file using,ORADEBUG SGATOFILE ,Unfreeze the instance using,ORADEBUG FFRESUMEINST,51,Immediate Dumps,There are three ways of taking an immediate dump,In the current session,ALTER SESSION SET EVENTS immediat

46、e trace name level ;,Using DBMS_SYSTEM,EXECUTE DBMS_SYSTEM.SET_EV(sid, serial#, 65535, , );,ORADEBUG DUMP ,In ORADEBUG,Cannot be invoked from init.ora,52,Conditional Dumps,Invoked when an error occurs In the init.ora file,event = trace name level ,ALTER SESSION SET EVENTS trace name level ;,In the c

47、urrent session,In ORADEBUG,ORADEBUG EVENT trace name level ,ORADEBUG SESSION_EVENT trace name level ,53,Dumping Columns,To dump the internal representation of columns use the DUMP built-in function,DUMP (COLUMN_VALUE, FORMAT),where FORMAT is,54,Dumping Columns,For example,SELECT DUMP (1001,16) FROM

48、dual;,Typ=2 Len=3: c2,b,2,returns,To output a column in hexadecimal use the XXXXXXXX format mask e.g.,SELECT TO_CHAR (65536,XXXXXXXX) FROM dual;,returns,10000,55,Dumping a Database Block,To dump a database block in Oracle 7 File number / block number must be converted into a data block address,COLUM

49、N decimalDBA new_value decimalDBASELECTdbms_utility.make_data_block_address (,56,Dumping a Database Block,To dump a database block in Oracle 8.0 or above,ALTER SYSTEM DUMP DATAFILE BLOCK ;,ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;,To dump a range of database blocks,To dump a block from a data

50、file in a closed database,ALTER SYSTEM DUMP DATAFILE BLOCK ;,57,Dumping a Database Block (Hex),To dump a database block in hexadecimal enable event 10289,ALTER SESSION SET EVENTS10289 trace name context forever, level 1;,Then dump the block using,ALTER SYSTEM DUMP DATAFILE BLOCK ;,On Unix/Linux syst

51、ems blocks can also be dumped using od,ALTER SYSTEM CHECKPOINT;,dd bs=8k if= skip=200 count=4 | od -x,Force DBWR to flush recently written blocks to disk using,ALTER SYSTEM SWITCH LOGFILE;,or,58,Dumping an Index,An index tree can be dumped using,ALTER SESSION SET EVENTSimmediate trace name treedump

52、level ;,where object_id is the object number of the index (in DBA_OBJECTS),Dumps branches leaves contents of leaf blocks,59,Dumping an Index,In Oracle 9.2 treedump may crash if index has been created by a primary/unique constraint e.g.,Occurs when IND$.PROPERTY 256 Can be prevented by creating the i

53、ndex before creating the constraint,CREATE TABLE t1 (c01 NUMBER);ALTER TABLE t1 ADD CONSTRAINT t1pk PRIMARY KEY (c01);,CREATE TABLE t1 (c01 NUMBER PRIMARY KEY);,60,Dumping Undo/Rollback,To dump an undo segment header use,ALTER SYSTEM DUMP UNDO_HEADER ;,To dump an undo transaction first obtain the XI

54、D using,ALTER SYSTEM DUMP UNDO BLOCK XID ;,SELECT xidusn, xidslot, xidsqn FROM v$transaction;,Dump the undo transaction using,61,Dumping a Redo Log,To identify the current redo log,ALTER SYSTEM DUMP LOGFILE ;,To dump a redo log file use,SELECT member FROM v$logfileWHERE group# = (SELECT group# FROM

55、v$logWHERE status = CURRENT);,Also works for archived redo logs,62,Other File Dumps,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;,ALTER SESSION SET EVENTSimmediate trace name controlf leve

56、l 15;,Control Files,63,Dumping the Library Cache,To dump the library cache,ALTER SESSION SET EVENTS immediate trace name library_cache level ;,where level is,64,Dumping the Row Cache,To dump the row (dictionary) cache,ALTER SESSION SET EVENTS immediate trace name row_cache level ;,where level is,65,

57、Dumping Fixed Memory Areas,To dump the fixed memory areas,ALTER SESSION SET EVENTS immediate trace name global_area level ;,where level is,66,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,ksmfs

58、siz AS SizeFROM x$ksmfsv;,The fixed SGA can also be dumped using,ORADEBUG DUMPSGA,67,Dumping Heap Memory,ALTER SESSION SET EVENTS immediate trace name heapdump level ;,To dump heap memory use,where level is,68,Dumping Subheap Memory,In Oracle 9.0.1 and below,ALTER SESSION SET EVENTS immediate trace

59、name heapdump_addr level ;,Get address of subheap e.g. 0 x8057eb78 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;,69,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 0 x8057eb78;,ALTER SESSI