1 / 90

Oracle Diagnostics

Oracle Diagnostics. Julian Dyke Independent Consultant. Web Version. juliandyke.com. © 2005 Julian Dyke. 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.

elga
Download Presentation

Oracle Diagnostics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OracleDiagnostics Julian Dyke Independent Consultant Web Version juliandyke.com ©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 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

  4. 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

  5. Trace File Names • In Oracle 9.2 foreground 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_<process_name>_1234.trc • These trace files are written to the BACKGROUND_DUMP_DEST directory

  6. Events • 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

  7. Events • To enable a numeric event at instance level # In init.ora fileevent = '<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 • ORADEBUG • DBMS_SYSTEM.SETEV

  8. 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;/

  9. 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=10000while [ $event -ne 10999 ]do event=`expr $event + 1` oerr ora $eventdone

  10. 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;/

  11. SQL Trace • SQL_TRACE is event 10046 level 1 • Other levels are • See Metalink Note39817.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 <level>'; • See "A Look under the Hood of CBO : The 10053 Event"Wolfgang Breitling - www.centrexcc.com

  13. Events • Tracing SQL 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 -- Enable SQL traceALTER SESSION SET sql_trace = TRUE; -- Disable SQL traceALTER SESSION SET sql_trace = FALSE; • For extended trace use -- Enable SQL trace with bindsALTER SESSION SET EVENTS'10046 trace name context forever, level 4'; -- Disable SQL trace with bindsALTER SESSION SET EVENTS'10046 trace name context off';

  17. Enabling SQL Trace • To enable at instance level # Enable SQL tracesql_trace = TRUE # 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 EVENTS'10046 trace name context forever, level 1; -- Disable SQL trace for instanceALTER SYSTEM SET EVENTS'10046 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 p1.name = 'user_dump_dest'AND p2.name = 'db_name'AND p.addr = s.paddrAND s.audsid = USERENV ('SESSIONID'); EDIT &&filenameSET SUFFIX SQLCOLUMN filename CLEAR

  19. 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';

  20. DBMS_SESSION • Event 10046 level 1 trace can 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) CREATE OR REPLACE TRIGGER us01_logonAFTER LOGON ON us01.SCHEMABEGINdbms_session.set_sql_trace (TRUE);END; CREATE OR REPLACE TRIGGER us01_logoffBEFORE LOGOFF ON us01.SCHEMABEGINdbms_session.set_sql_trace (FALSE);END; ALTER TRIGGER us01_login ENABLE; ALTER TRIGGER us01_login DISABLE;

  22. 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;

  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 and Serial number can be found in V$SESSION (SID and SERIAL#)

  24. DBMS_SYSTEM • To set a Boolean parameter in another session use DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION( SID NUMBER, -- SID SERIAL# NUMBER, -- Serial Number PARNAM VARCHAR2, -- Parameter Name BVAL BOOLEAN -- Value); ORADEBUG SUSPEND SET_BOOL_PARAM_IN_SESSION • For example EXECUTE DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(9, 27, 'hash_join_enabled', TRUE); • Note: does not work with SQL_TRACE

  25. DBMS_SYSTEM • To set an integer parameter in another session use DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION( SID NUMBER, -- SID SERIAL# NUMBER, -- Serial Number PARNAM VARCHAR2, -- Parameter Name INTVAL INTEGER -- Value); ORADEBUG SUSPEND SET_BOOL_PARAM_IN_SESSION • For example EXECUTE DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(9, 27, 'sort_area_size', 131072);

  26. DBMS_SYSTEM • To set an event in another session use DBMS_SYSTEM.SET_EV( SI NUMBER, -- SID SE NUMBER, -- Serial Number EV NUMBER, -- Event Number e.g. 10046 LE NUMBER, -- Level e.g. 1 NM 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 Log TST VARCHAR2 -- Message); • Example BEGIN 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 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>); • This will prefix KSDWRT output with <level> colons

  29. Available in Oracle 7.2 and above Requires dbmssupp.sql and prvtsupp.plb See Metalink Note62294.1 Install using SYS AS SYSDBA DBMS_SUPPORT $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.MYSIDRETURN BOOLEAN; • This function executes the query SELECT sid FROM v$mystatWHERE ROWNUM = 1;

  30. 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;

  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 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));

  32. 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 );

  33. DBMS_MONITOR • Trace can be enabled using client identifiers • To set a client identifier use DBMS_SESSION.SET_IDENTIFIER( CLIENT_ID VARCHAR2 -- Client ID ); • The client identifier for a specific session can be found by querying V$SESSION.CLIENT_IDENTIFIER

  34. 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 ); • 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 • 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 = (PROTOCOL=TCP)(HOST=server1)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = SERVICE1) ))

  37. DBMS_MONITOR • To specify a module and action use DBMS_APPLICATION_INFO.SET_MODULE( MODULE_NAME VARCHAR2, -- Module ACTION_NAME VARCHAR2 -- Action ); • To specify subsequent actions use DBMS_APPLICATION_INFO.SET_ACTION( ACTION_NAME VARCHAR2 -- Action );

  38. 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 ); • 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_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); • Statistics externalized in V$SERV_MOD_ACT_STATS • Disable using DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE

  40. 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 *

  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 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 /NOLOGSQL> CONNECT SYS/password AS SYSDBA • To list the available options ORADEBUG HELP

  43. 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> • This is the PID in Unix and the Thread ID in Windows NT/2000

  44. 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

  45. 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 SETMYPIDORADEBUG DUMP LIBRARY_CACHE 4

  46. ORADEBUG • To suspend the current process ORADEBUG SUSPEND • To resume the current process ORADEBUG RESUME • 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

  47. ORADEBUG • To dump the events currently set use ORADEBUG DUMP EVENTS <level> • where level is • Output is written to the current trace file

  48. 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 8SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

  49. 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_

  50. 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

More Related