oracle diagnostics l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle Diagnostics PowerPoint Presentation
Download Presentation
Oracle Diagnostics

Loading in 2 Seconds...

play fullscreen
1 / 90

Oracle Diagnostics - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


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