Chapter 3 Tools of the Trade

1. Chapter 3Tools of the Trade

2. “Explaining” SQL Statements • Understanding optimizers execution path • Explain Plan facility • Plan Table • Goal: Cached SQL (V$SQL) • PL/SQL Package DBMS_XPLAN • Provides readable output • Easier to use • Virtual Indexing • Using Optimizer to see if adding an index would help the execution plan

3. “Explaining” SQL Statements (cont.) • Explain Plan facility • Reading the Execution Plan output • More indented means earlier execution • If multiple lines with same indentation, uppermost executed first • See table 3-1 for common execution plan steps (pp. 48-51)

4. Tracing the Oracle Execution • Allows session-level tracing (SQL Trace) • Using the TKPROF utility • Powerful tools • Can be difficult and awkward to interpret

5. Session-level tracing • Initiating session-level tracing • SQL Plus alter session set sql_trace=true; • PL/SQL BEGIN DBMS_SESSION.session_trace_enable ( waits=>TRUE, binds=>FALSE, plan_stat => ‘all executions’ );

6. Session-level tracing (cont.) • The trace file • Oracle generates randomly named files by default • Can tell oracle to include identifier in file name ALTER SESSION SET tracefile_identifier=GUY • Resulting trace file name would be similar to: gh11R1GA_ora_31429_GUY.trc • Getting tracing status SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds, traceid, tracefile FROM v$session s JOIN v$process p ON (p.addr=s.paddr) WHERE audsid = USERENV (‘SESSIONID);

7. Session-level tracing (cont.) • Can invoke trace in another session • Can trace by specific module, action, or service • Can trace using a Login Trigger • Finding the trace file • Show the value of the user_dump_dest parameter • Via v$parameter view • Via SQL Plus ‘show parameter’ command • Easier to find by using tracefile_identifier

8. Formatting Trace Files with TKPROF • TKPROF Utility makes trace files usable and readable Tkprof trace_file output_file explain=connection waits=yes|no sort=(sort keys) • Key TKPROF parameters • Trace_file (raw generated trace file) • Output_file (resultant formatted file) • Explain (for issuing explain plans) • Waits (shows wait information) • Sort (sorts the SQL statements)

9. Formatting Trace Files with TKPROF (cont.) • Can merge multiple trace files into single TKPROF report • The format of the TKPROF output • The SQL text is displayed • Table of execution statistics • Number of times issued • CPU time • Elapsed time • Disk reads • Number of buffers read in query • Number of rows processed • Explain Plans

10. Using the Autotrace facility • Use within SQL Plus • Very easy to use and execute • The Autotrace options • OFF (default) • ON • TRACEONLY (gives only trace output, doesn’t execute query) • EXPLAIN (generates explain plan output only) • STATISTICS (generates execution statistics only) SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] STAT[ISTICS]]

11. Monitoring The Oracle Server • The dynamic performance views (V$) • Session and system views • The wait interface views • Wait classes • Wait events • See Table 3-3 (pp. 70-71) • The time model (different views of time slices • CPU-based • Elapsed time-based

12. Monitoring The Oracle Server (cont.) • Integrate the time model with the wait interface • Time model and wait interface are by themselves incomplete • Integrating them gives a more complete look at the system • Done by joining information on waits and time from V$ views

13. Oracle Enterprise Manager • Web-based tool for DBA’s • Requires special licensing for the different “packs” that come with the product • Diagnostic Pack • Tuning Pack • Many other Packs are available • Gives very specific information in graphical form • Can help the DBA tune quickly