Oracle Advanced Diagnostics Revisited: Tips and Tools

1. AdvancedDiagnostics Revisited Julian Dyke Independent Consultant UKOUG Conference 2014 juliandyke.com

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. Agenda Trace Parameters Features and Hints Execution Plans Events ORADEBUG ALTER Statements

4. Trace

5. TraceV$DIAG_INFO • Current trace file can be determined using: SELECT value FROM v$diag_infoWHERE name = 'Default Trace File'; VALUE /u01/app/oracle/diag/rdbms/target/TARGET/trace/TARGET_ora_5556.trc Introduced in Oracle 11.1 Session-specific view reporting various diagnostic parameters

6. TraceTRACEFILE_IDENTIFIER Parameter • For example if the current trace file name is: /u01/app/oracle/diag/rdbms/target/TARGET/trace/TARGET_ora_5556_TEST.trc • If the TRACEFILE_IDENTIFIER is set to "TEST": ALTER SESSION SET tracefile_identifier = 'TEST'; • Subsequent trace will be written to: /u01/app/oracle/diag/rdbms/target/TARGET/trace/TARGET_ora_5556_TEST.trc • TRACEFILE_IDENTIFIER can also be set using ORADEBUG: ORADEBUG SETTRACEFILEID TEST • Introduced in Oracle 8.1.7 • Appends specified suffix to trace file name • Subsequent trace is immediately written to new file • Can be updated unlimited number of times

7. TraceDBMS_SYSTEM.KSDWRT Procedure DBMS_SYSTEM.KSDWRT( DEST NUMBER, -- 1 = Trace File, 2 = Alert Log TST VARCHAR2 -- Message); • For example: BEGIN DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’); DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);END; To write to trace files and/or alert log use:

8. TraceDBMS_MONITOR - Overview • Introduced in Oracle 10.1 • Package to enable/disable trace and statistics • Defined in $ORACLE_HOME/rdbms/admin/dbmsmntr.sql • Trace subroutines include: • SESSION_TRACE_ENABLE / DISABLE • DATABASE_TRACE_ENABLE / DISABLE • CLIENT_ID_TRACE_ENABLE / DISABLE • SERV_MOD_ACT_TRACE_ENABLE / DISABLE • Statistics subroutines include: • CLIENT_ID_STAT_ENABLE / DISABLE • SERV_MOD_ACT_STAT_ENABLE / DISABLE • Trace subroutines are equivalent to event 10046 level 8 • Include wait events by default

9. TraceDBMS_MONITOR - PLAN_STAT column • In Oracle 11.1 and above the following subroutines include the PLAN_STAT column: • SESSION_TRACE_ENABLE • DATABASE_TRACE_ENABLE • CLIENT_ID_TRACE_ENABLE • SERV_MOD_ACT_TRACE_ENABLE • PLAN_STAT specifies the dump frequency for row source (STAT) statistics • Values can be: • NEVER • FIRST_EXECUTION (default) • ALL_EXECUTIONS • Useful when individual statement executions are affected by data cardinality / selectivity

10. Parameters

11. ParametersV$PARAMETER • CON_ID - new in Oracle 12.1 -> Container ID • ISPDB_MODIFIABLE is also new in 12.1 V$PARAMETER includes supported parameters

12. ParametersV$PARAMETER • To set an unsupported parameter use double quotes around name e.g.: ALTER SESSION SET "_serial_direct_read" = NEVER; • V$PARAMETER includes non-default values for unsupported parameters • V$PARAMETER includes supported parameters • Based on X$KSPPI - includes all parameters

13. ParametersUnsupported parameters • To dump values for all parameters • including default parameters for unsupported parameters: SELECT i.ksppinm||';'||sv.ksppstvlFROM x$ksppi i, x$ksppsv svWHERE i.indx = sv.indxORDER BY i.ksppinm; V$PARAMETER only reports non-default values for unsupported parameters

14. ParametersV$PARAMETER_VALID_VALUES • V$PARAMETER_VALID_VALUES - supported parameters • Based on X$KSPVLD_VALUES - all parameters

15. ParametersV$PARAMETER_VALID_VALUES SELECT value_kspvld_values AS "Value", isdefault_kspvld_values AS "Default"FROM x$kspvld_valuesWHERE name_kspvld_values = '_serial_direct_read'ORDER BY ordinal_kspvld_values; For example:

16. FeaturesandHints

17. Features and HintsV$SQL_FEATURE • Fixes can be identified from DESCRIPTION column WHERE description LIKE 'Fix%' • Individual fixes can be enabled and disabled. For example: ALTER SESSION SET "_fix_control"='6776808:off'; • Based on X$QKSFM • Includes features and fixes

18. Features and HintsV$SQL_FEATURE Version counts are:

19. Features and HintsV$SQL_FEATURE_HIERARCHY SELECT lpad ( ' ' , level * 2 ,' ' ) || sql_featureFROM v$sql_feature_hierarchyCONNECT BY PRIOR sql_feature = parent_idSTART WITH parent_id = 'QKSFM_ALL'; .... QKSFM_TRANSFORMATION QKSFM_CBQT QKSFM_CVM QKSFM_DIST_PLCMT QKSFM_JOINFAC QKSFM_JPPD QKSFM_PLACE_GROUP_BY QKSFM_PULL_PRED.... • V$SQL_FEATURE describes the relationships between features • The root feature is QKSFM_ALL • The following query prints the hierarchy in a nested format:

20. Features and HintsV$SQL_HINT • INVERSE column • Describes the opposite hint e.g.: PUSH_PRED and NO_PUSH_PRED • Based on X$QKSHT • Currently no concept of unsupported hints

21. Features and HintsV$SQL_HINT • VERSION column • Describes version in which hint was introduced:

22. ExecutionPlans

23. Execution PlansDBMS_XPLAN - Overview • Introduced in Oracle 9.2 • Enhanced in subsequent releases • Defined in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql • Useful subroutines include • DISPLAY - display a plan from a PLAN_TABLE • DISPLAY_AWR - display a plan from the AWR • DISPLAY_CURSOR - display a plan from the library cache • DISPLAY_PLAN – returns plan as a CLOB • DISPLAY_SQLSET – displays a plan from a SQL tuning set • DISPLAY_SQLPLAN_BASELIN E – displays a plan from SQL baseline • For example to display execution plan for statement most recently executed by the current session: SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR);

24. Execution PlansDBMS_XPLAN - FORMAT Column • All DISPLAY subroutines include a FORMAT column which can be: • BASIC • Minimum information • Display operation ID, name and option • TYPICAL • Default. • Display operation ID, name and option, #rows, #bytes and optimizer cost. • Includes pruning, parallel and predicate information when applicable • ALL • Maximum information • As TYPICAL plus projection, alias and remote SQL if applicable • SERIAL • Same as TYPICAL except parallel information is not displayed even if statement executes in parallel

25. Execution PlansDBMS_XPLAN - FORMAT Column Options • FORMAT column options can be fine-tuned using the following keywords: • ROWS - number of rows estimated by optimizer • BYTES - number of bytes estimated by optimizer • COST - optimizer cost information • PARTITION - partition pruning information • PARALLEL - parallel execution information • PREDICATE - predicate section • PROJECTION - projection section • ALIAS - query block name / object alias • REMOTE - distributed query information • NOTE - note section • If keyword is prefixed by a minus sign then specified information is excluded • BASIC ROWS - displays basic information plus number of rows • ALL -PROJECTION -NOTE displays everything except the projection and note sections

26. Execution PlansDBMS_XPLAN - Additional Statistics • For DISPLAY_CURSOR and DISPLAY_SQLSET, additional data can be output using the following keywords: • IOSTATS – shows IO statistics for all executions of the cursor • MEMSTATS – displays memory management statistics including • execution mode of operator • how much memory was used • number of bytes spilled to disk • ALLSTATS – both IOSTATS and MEMSTATS • LAST – only report statistics for last execution of cursor • IOSTATS, ALLSTATS and LAST require basic plan statistics which are collected by • setting STATISTICS_LEVEL parameter to ALL • specifying GATHER_PLAN_STATISTICS hint • MEMSTATS requires PGA memory management to be enabled by: • setting PGA_AGGREGATE_TARGET to non-zero value

27. Execution PlansOperations and Options • AWR includes some interesting (static) views that describe execution plan operations and options available in the installed database version • DBA_HIST_PLAN_OPERATION_NAME • Contains a list of row source operations • DBA_HIST_PLAN_OPTION_NAME • Contains a list of row source options • DBA_HIST_TOPLEVELCALL_NAME • Contains a list of top level calls • Used in DBA_HIST_ACTIVE_SESS_HISTORY

28. Events

29. Event 10046Levels Event 10046 has additional levels in recent versions

30. Event 10046Bind Variables EXECUTE dbms_monitor.session_trace_enable (binds=>TRUE); CREATE TABLE driver (key VARCHAR2(4), name VARCHAR2(30)); DECLARE TYPE driver_tab IS TABLE OF driver%ROWTYPE; d driver_tab := driver_tab ();BEGIN d.extend (6); d(1).key := 'LHAM'; d(1).name := 'Lewis Hamilton'; d(2).key := 'NROS'; d(2).name := 'Nico Rosberg'; d(3).key := 'SVET'; d(3).name := 'Sebastian Vettel'; d(4).key := 'FALO'; d(4).name := 'Fernando Alonso'; d(5).key := 'DRIC'; d(5).name := 'Daniel Ricciardo'; d(6).key := 'JBUT'; d(6).name := 'Jenson Button'; FORALL j IN 1..3 INSERT INTO driver VALUES (d(j).key,d(j).name); FORALL j IN 4..6 INSERT INTO driver VALUES (d(j).key,d(j).name);END; Event 10046 level 4 trace only captures the first bind variable in array

31. Event 10046Bind Variables BINDS #139676743602632:Bind#0 ... value="LHAM"Bind#1 ... value="Lewis Hamilton" ... BINDS #139676743602632:Bind#0 ... value="FALO"Bind#1 ... value="Fernando Alonso" Note this issue has consequences for load generation / simulation tools such as HammerDB and LoadRunner which base their input on 10046 trace. • Trace only includes first row of each array insert • Remaining rows are not traced Trace will only contain bind variable values for two rows:

32. Event 10053Optimizer Decisions • Details are only written to trace when the statement is hard-parsed To trace optimizer decisions use enable event 10053:

33. Event 10053 DBMS_SQLDIAG • Provides APIs to diagnose SQL statements. • Used by diagnostic modules: • SQL_TESTCASE • SQL_DIAGNOSTIC • Defined in $ORACLE_HOME/rdbms/admin/dbmsdiag.sql • Includes DUMP_TRACE procedure: • Dumps 10053 trace (optimizer or compiler) trace for a specific SQL statement • Does not execute SQL statement to generate trace • Compiler trace is a superset of optimizer trace • Includes kkf library calls • Query block decisions • Expressions

34. Event 10053 DBMS_SQLDIAG • For example: dbms_sqldiag.dump_trace( p_sql_id => '2y220pbrk573n', p_child_number => 0, p_component => 'Optimizer', p_file_id => 'OPTIMIZER'); Note that P_FILE_ID sets TRACEFILE_IDENTIFIER, but does not reset it at end of call so subsequent trace file be written to the new file. Alternatively NULL can be specified (the default) DUMP_TRACE procedure parameters are:

35. ORADEBUG

36. ORADEBUGIntroduction • Requires SYSDBA privilege • Options vary for each release • To list available options use: SQL> ORADEBUG HELP • 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)

37. ORADEBUGProcess Specification • Using current process: SQL> ORADEBUG SETMYPID • Using Oracle PID (V$PROCESS.PID) SQL> ORADEBUG SETORAPID <pid> • Using operating system PID (V$PROCESS.SPID) SQL> ORADEBUG SETOSPID <spid> • Using the Oracle process name: SQL> ORADEBUG SETORAPNAME <pname> • For example: SQL> ORADEBUG SETORAPNAME PMON There are four ways of specifying a process in ORADEBUG:

38. ORADEBUGUseful Commands SQL> ORADEBUG TRACEFILE_NAME • To flush the current trace file use: SQL> ORADEBUG FLUSH • To set the maximum size of the current trace file to UNLIMITED use: SQL> ORADEBUG UNLIMIT • To set the TRACEFILE_IDENTIFER parameter: SQL> ORADEBUG SETTRACEFILEID <suffix> • To dump the currently executing statement (SQL or PL/SQL) SQL> ORADEBUG CURRENT_SQL To display the current trace file name use:

39. ORADEBUGDumps • To list available dumps: SQL> ORADEBUG DUMPLIST • To perform an dump immediately use: SQL> ORADEBUG DUMP <dump_name> <level> [address] • To perform a dump periodically use: SQL> ORADEBUG PDUMP [interval=<interval>] [ndumps=<count> <dump_name> <level> [address] Dumps vary for each release:

40. ORADEBUGProcess Statistics SQL> ORADEBUG SETORAPNAME PMONSQL> ORADEBUG PROCSTAT ----- Dump of Process Statistics -----User time used = 65System time used = 30Maximum resident set size = 28452Integral shared text size = 0Integral unshared data size = 0Integral unshared stack size = 0Page reclaims = 7480Page faults = 7Swaps = 0Block input operations = 1472Block output operations = 32Socket messages sent = 0Socket messages received = 0Signals received = 0Voluntary context switches = 2555Involuntary context switches = 387 To dump process statistics use ORADEBUGPROCSTAT. For example:

41. ORADEBUGComponents SQL> ORADEBUG DOC Internal Documentation EVENT Help on events (syntax, event list, ...) COMPONENT [<comp_name>] List all components or describe <comp_name> • Components are arranged in nested hierarchies • Each component has a unique name • Do not need to specify parent(s) to uniquely identify component • Most component descriptions include the internal module name(s) e.g.: SQL_Execution SQL Execution (qer, qes, kx, qee) Parallel_Execution Parallel Execution (qerpx, qertq, kxfr, kxfx, kxfq, kxfp) • ORADEBUG includes internal documentation for • Components • Events

42. ORADEBUGComponents Components are grouped into eight libraries:

43. ORADEBUGComponents Components in library RDBMS: SQL_Compiler SQL Compiler ((null)) SQL_Parser SQL Parser (qcs) SQL_Semantic SQL Semantic Analysis (kkm) SQL_Optimizer SQL Optimizer ((null)) SQL_Transform SQL Transformation (kkq, vop, nso) SQL_MVRW SQL Materialized View Rewrite ((null)) SQL_Vmerge SQL View Merging (kkqvm) SQL_Virtual SQL Virtual Column (qksvc, kkfi) SQL_APA SQL Access Path Analysis (apa) SQL_Costing SQL Cost-based Analysis (kko, kke) • The following are all valid: SQL> ORADEBUG DOC RDBMSSQL> ORADEBUG DOC SQL_CompilerSQL> ORADEBUG DOC SQL_OptimizerSQL> ORADEBUG DOC SQL_TransformSQL> ORADEBUG DOC SQL_Vmerge • For example given the extractbelow from ORADEBUG DOC COMPONENT:

44. ORADEBUGEvents SQL> ORADEBUG DOC EVENT <event_spec>::= '<event_id> [<event_scope>] [<event_filter_list>] [<event_parameters>] [<action_list>] [off]' <event_id> ::= <event_name | number>[<target_parameters>] <event_scope> ::= [<scope_name>: scope_parameters] <event_filter> ::= {<filter_name>: filter_parameters} <action> ::= <action_name>(action_parameters) <*_parameters> ::= <parameter_name> = <value>[, ] The full event syntax is described in ORADEBUG:

45. ORADEBUGEvents SQL> ORADEBUG DOC EVENT NAME SQL> ORADEBUG DOC EVENT SCOPE SQL> ORADEBUG DOC EVENT FILTER SQL> ORADEBUG DOC EVENT ACTION • Each event specification is comprised of up to four parts: • Name • Scope • Filter • Action • Not all of the above are always required • Defaults apply for many permutations • More information is available in:

46. ORADEBUGEvents - Name • For example for the RDBMS library: • Event names are optional: • Can be specified for some libraries

47. ORADEBUGEvents - Scope [SQL: sql_id <string> ] • For example: [SQL: 3s1yukp05bzg6] • Multiple SQL_IDs can be specified using | as a separator [SQL: 3s1yukp05bzg6 | aca4xvmz0rzup] • Scope is the SQL scope for RDBMS events • Defined using SQL[ ] • Syntax is

48. ORADEBUGEvents - Filter • Filters must be enclosed in curly brackets. For example pgadep below: SQL> ALTER SESSION SET EVENTS 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0} plan_stat=all_executions,wait=true,bind=true'; Filters include:

49. ORADEBUGEvents - Filter {pgadep: exactdepth 0} • For normal statement execution • dep = 0 SQL statement execution • For PL/SQL execution • dep = 0 PL/SQL execution • dep = 1 SQL statement execution • pgadep is the PGA depth (dep=N) in trace file. • Can take the following values: • exactDepth • lessThan • greaterThan • For example:

50. ORADEBUGEvents - Action • Actions exist for each library (except PLSQL) • For the RDBMS library most actions correspond to ORADEBUG DUMP commands from older versions including: