Use EXPLAIN PLAN and TKPROF To Tune Your Applications Roger

Use EXPLAIN PLAN and TKPROF To Tune Your Applications Roger Schrag Database Specialists, Inc. www.dbspecialists.com 1

Today’s Session EXPLAIN PLAN overview TKPROF overview Why? Reading execution plans Reading TKPROF reports 2

White Paper Twenty one pages of details I can't possibly cover in a one hour presentation! Lots of sample code, execution plans, and TKPROF reports that you will see are probably not readable when I put them up on PowerPoint slides—but they are readable in the white paper. Download: www.dbspecialists.com/presentations 3

Execution Plans and EXPLAIN PLAN An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations. EXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table. 4

The Plan Table A plan table holds execution plans generated by the EXPLAIN PLAN statement. The typical name for a plan table is plan table, but you may use any name you wish. Create the plan table by running utlxplan.sql, located in ORACLE HOME/rdbms/admin. 5

Important Columns in the Plan Table statement id Unique identifier for each execution plan timestamp When the execution plan was generated operation The operation performed in one step of the execution plan, such as “table access” options Additional information about the operation, such as “by index ROWID” object name Name of table, index, view, etc. accessed optimizer Optimizer goal used when creating execution plan id Step number in execution plan parent id Step number of parent step 6

EXPLAIN PLAN Prerequisites INSERT privilege on a plan table All necessary privileges to execute the statement being explained SELECT privileges on underlying tables of views, if the statement being explained involves views 7

EXPLAIN PLAN Syntax EXPLAIN PLAN [SET STATEMENT ID string in single quotes ] [INTO plan table name ] FOR SQL statement ; 8

Querying an Execution Plan from the Plan Table Use a CONNECT BY clause to trace the hierarchy Use LPAD function to indent rows, making the hierarchy easier to follow Put statement id in WHERE clause to retrieve only one execution plan at a time Sample script on next slide shows the most important information You can also try utlxpls.sql or utlxplp.sql in ORACLE HOME/rdbms/admin 9

A Simple Query to Display Execution Plans SET VERIFY OFF ACCEPT stmt id CHAR PROMPT "Enter statement id: " COL id FORMAT 999 COL parent id FORMAT 999 HEADING "PARENT" COL operation FORMAT a35 TRUNCATE COL object name FORMAT a30 SELECT id, parent id, LPAD (' ', LEVEL - 1) operation ' ' options operation, object name FROM plan table WHERE statement id '&stmt id' START WITH id 0 AND statement id '&stmt id' CONNECT BY PRIOR id parent id AND statement id '&stmt id'; 10

A Sample Execution Plan SQL EXPLAIN PLAN SET statement id 'demo' FOR 2 SELECT a.customer name, a.customer number, b.invoice number, 3 b.invoice type, b.invoice date, b.total amount, 4 c.line number, c.part number, c.quantity, c.unit cost 5 FROM customers a, invoices b, invoice items c 6 WHERE c.invoice id :b1 7 AND c.line number :b2 8 AND b.invoice id c.invoice id 9 AND a.customer id b.customer id; Explained. SQL @explain.sql Enter statement id: demo ID PARENT OPERATION OBJECT NAME ---- ------ ----------------------------------- ----------------0 SELECT STATEMENT 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS BY INDEX ROWID INVOICE ITEMS 4 3 INDEX UNIQUE SCAN INVOICE ITEMS PK 5 2 TABLE ACCESS BY INDEX ROWID INVOICES 6 5 INDEX UNIQUE SCAN INVOICES PK 7 1 TABLE ACCESS BY INDEX ROWID CUSTOMERS 8 7 INDEX UNIQUE SCAN CUSTOMERS PK 11

Other Ways to View Execution Plans The autotrace feature in SQL*Plus SET AUTOTRACE OFF ON TRACEONLY [EXPLAIN] [STATISTICS] Performance tuning tools Check out TOAD from Quest Software at: http://www.toadsoft.com/downld.html 12

Sample Autotrace Output in SQL*Plus Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer CHOOSE (Cost 4 Card 1 Bytes 39) 1 0 NESTED LOOPS (Cost 4 Card 1 Bytes 39) 2 1 NESTED LOOPS (Cost 3 Card 1 Bytes 27) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE ITEMS' (Cost 2 Card 1 Bytes 15) 4 3 INDEX (UNIQUE SCAN) OF 'INVOICE ITEMS PK' (UNIQUE) ( Cost 1 Card 2) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICES' (Cost 1 Ca rd 2 Bytes 24) 6 5 INDEX (UNIQUE SCAN) OF 'INVOICES PK' (UNIQUE) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost 1 Car d 100 Bytes 1200) 8 7 INDEX (UNIQUE SCAN) OF 'CUSTOMERS PK' (UNIQUE) 13

Sample Plan Display in TOAD 14

EXPLAIN PLAN Limitations The EXPLAIN PLAN statement provides a good faith estimate of the execution plan that Oracle would use. The real plan that gets used may differ from what EXPLAIN PLAN tells you for many reasons: – Optimizer stats, cursor sharing,, bind variable peeking, dynamic instance parameters make plans less stable. – EXPLAIN PLAN does not peek at bind variables. – EXPLAIN PLAN does not check the library cache to see if the statement has already been parsed. EXPLAIN PLAN does not work for some queries: ORA-22905: cannot access rows from a non-nested table item 15

Viewing Actual Execution Plans The v sql view shows statements in the library cache. Here you can find the address, hash value, and child number for a statement of interest. The v sql plan view shows the actual execution plan for each statement, given its address, hash value, and child number. The columns are similar to the plan table. The v sql plan statistics view shows actual statistics (rows, buffer gets, elapsed time, etc.) for each operation of the execution plan. The v sql plan and v sql plan statistics views are available starting in Oracle 9i. v sql plan statistics is not populated by default. 16

A Simple Query to Display Actual Execution Plans SET VERIFY OFF COL id FORMAT 999 COL parent id FORMAT 999 HEADING "PARENT" COL operation FORMAT a35 TRUNCATE COL object name FORMAT a30 SELECT id, parent id, LPAD (' ', LEVEL - 1) operation ' ' options operation, object name FROM ( SELECT id, parent id, operation, options, object name FROM v sql plan WHERE address '&address' AND hash value &hash value AND child number &child number ) START WITH id 0 CONNECT BY PRIOR id parent id; 17

Trace Files and TKPROF The Oracle server process managing a database session writes a verbose trace file when SQL trace is enabled for the session. TKPROF is a utility provided by Oracle that formats SQL trace files into very helpful and readable reports. TKPROF is installed automatically when the database server software is installed. You invoke TKPROF from the operating system command line; there is no graphical interface for TKPROF. Starting in Oracle 9i TKPROF can read extended SQL trace files and report on wait events statistics. 18

Enabling SQL Trace At the instance level: sql trace true timed statistics true (optional) In your own session: ALTER SESSION SET sql trace TRUE; ALTER SESSION SET timed statistics TRUE; (optional) In another session: SYS.dbms system.set sql trace in session ( SID , serial# , TRUE) 19

Finding the Trace File Look in the user dump destination. On OFA compliant systems this will be ORACLE BASE/admin/ ORACLE SID/udump Check timestamps and file contents to see which trace file is yours If non-DBAs need access to trace files, add trace files public true to the parameter file to avoid permissions problems on Unix platforms Use a dedicated server connection when tracing, if possible. 20

Formatting a Trace File with TKPROF Invoke TKPROF from the operating system prompt like this: tkprof trace file output file \ [explain username/password ] \ [sys n] [sort keyword ] 21

TKPROF Command-line Arguments tkprof trace file output file \ [explain username/password ] \ [sys n] [sort keyword ] trace file output file explain sys n sort The SQL trace file to be formatted The formatted output to be written by TKPROF Database login to be used if you want the output to include execution plans Omit “recursive SQL” performed by the SYS user List traced SQL statement in the output file in a specific order 22

TKPROF Sample Output SELECT a.customer name, a.customer number, b.invoice number, b.invoice type, b.invoice date, b.total amount, c.line number, c.part number, c.quantity, c.unit cost FROM customers a, invoices b, invoice items c WHERE c.invoice id :b1 AND c.line number :b2 AND b.invoice id c.invoice id AND a.customer id b.customer id call count ------- -----Parse 1 Execute 1 Fetch 2 ------- -----total 4 cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.05 0.02 0 0 0 0.00 0.00 0 0 0 0.00 0.00 8 8 0 -------- ---------- ---------- ---------- ---------0.05 0.02 8 8 0 rows ---------0 0 1 ---------1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 34 (RSCHRAG) 23

Why Look at Execution Plans and TKPROF Reports? These tools are critical to the application tuning process, and tuning at the application level is necessary for high performance systems. With EXPLAIN PLAN, v sql plan, and TKPROF, you can: Proactively tune applications that are in development Reactively tune production systems that are experiencing performance problems Estimate resource needs or feasibility of ad-hoc queries Quantify resource needs for specific applications 24

Reading Execution Plans An execution plan is a hierarchical listing of steps. Each step is one of a few basic data access operations known to the database server. The most complex SQL statement can be broken down into a series of basic operations. “Read from the most indented step outward.” This is not exactly correct! Instead, take this approach: a) Start at the least indented step b) Find the step or steps that provide direct input to the step noted in (a). c) Evaluate each of the steps found in (b). This may involve recursively finding steps that provide input and evaluating them. 25

Execution Plan Example #1 SELECT FROM WHERE ORDER BY customer id, customer name customers UPPER (customer name) LIKE 'ACME%' customer name; OPERATION OBJECT NAME ------------------------------ -------------SELECT STATEMENT SORT ORDER BY TABLE ACCESS FULL CUSTOMERS 26

Execution Plan Operations TABLE ACCESS FULL Perform a full table scan of the indicated table and retrieve all rows that meet criteria from the WHERE clause. Input: no subordinate operations. Output: the necessary columns from the rows meeting all criteria. SORT ORDER BY Sort the input rows for the purpose of satisfying an ORDER BY clause. Input: the rows to be sorted. Output: the rows in sorted order. 27

Execution Plan Example #2 SELECT FROM WHERE AND a.customer name, b.invoice number, b.invoice date customers a, invoices b b.invoice date TRUNC (SYSDATE - 1) a.customer id b.customer id; OPERATION -----------------------------SELECT STATEMENT NESTED LOOPS TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID INDEX UNIQUE SCAN OBJECT NAME -------------INVOICES INVOICES DATE CUSTOMERS CUSTOMERS PK 28

Execution Plan Operations INDEX UNIQUE SCAN Look up a complete key in a unique index. Input: usually no subordinate operations. (Key values typically come from the original query or a parent operation.) Output: zero or one ROWIDs from the index. INDEX RANGE SCAN Look up a key in a non-unique index, or an incomplete key in a unique index. Input: usually no subordinate operations. Output: zero or more ROWIDs from the index. 29

Execution Plan Operations TABLE ACCESS BY INDEX ROWID Look up rows in a table by their ROWIDs. Input: a list of ROWIDs to look up. Output: the necessary columns from the rows with the given ROWIDs. NESTED LOOPS Perform a join between two sets of row data using the nested loops algorithm. Inputs: two separate sets of row data. Output: the results of the join. For each row Oracle reads from the first input, the operations that make up the second input are executed once and matching rows generate output. 30

Execution Plan Example #3 SELECT a.customer name, COUNT (DISTINCT b.invoice id) open invs, COUNT (c.invoice id) open inv items FROM customers a, invoices b, invoice items c WHERE b.invoice status 'OPEN' AND a.customer id b.customer id AND c.invoice id ( ) b.invoice id GROUP BY a.customer name; OPERATION -------------------------------SELECT STATEMENT SORT GROUP BY NESTED LOOPS OUTER HASH JOIN TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN TABLE ACCESS FULL INDEX RANGE SCAN OBJECT NAME ---------------- INVOICES INVOICES STATUS CUSTOMERS INVOICE ITEMS PK 31

Execution Plan Operations HASH JOIN Perform a join between two sets of row data using the hash join algorithm. Inputs: two separate sets of row data. Output: the results of the join. Oracle reads all rows from the second input and builds a hash structure, before reading each row from the first input one at a time. For each row from the first input, the hash structure is probed and matching rows generate output. 32

Execution Plan Operations NESTED LOOPS OUTER Same as the NESTED LOOPS operation, except that an outer join is performed. SORT GROUP BY Same as the SORT ORDER BY operation, except that the rows are sorted and grouped to satisfy a GROUP BY clause. 33

Execution Plan Example #4 SELECT FROM WHERE customer name customers a EXISTS (SELECT 1 FROM invoices view b WHERE b.customer id a.customer id AND number of lines 100) ORDER BY customer name; CREATE OR REPLACE VIEW invoices view AS SELECT a.invoice id, a.customer id, COUNT(*) number of lines FROM invoices a, invoice items b WHERE b.invoice id a.invoice id GROUP BY a.invoice id, a.customer id; 34

Execution Plan Example #4 (continued) OPERATION OBJECT NAME ----------------------------------SELECT STATEMENT SORT ORDER BY FILTER TABLE ACCESS FULL VIEW FILTER SORT GROUP BY NESTED LOOPS TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN INDEX RANGE SCAN ------------- CUSTOMERS INVOICES VIEW INVOICES INVS CUST ID INV ITEMS PK 35

Execution Plan Operations FILTER Read a set of row data and discard some rows based on various criteria. To determine the criteria, operations from a second input may need to be performed. Input: rows to be examined and, sometimes, an additional subordinate operation that must be performed for each row from the first input in order to evaluate criteria. Output: the rows from the first input that met the criteria. 36

Execution Plan Operations VIEW Build a physical representation of a database view or subset of a database view. Input: set of row data. Output: set of row data that implements the view or subset of the view. 37

Notes on Execution Plan Operations The optimizer rewrites subqueries as joins and merges them into the main query whenever possible. If a subquery is completely independent of the main query and cannot be merged into the main query, the optimizer may treat the subquery as a separate statement and leave it out of the execution plan for the main query. The optimizer expands view definitions and merges them into the main query wherever possible. A VIEW operation will only appear in an execution plan when the view definition could not be merged. 38

Execution Plan Example #5 SELECT /* RULE */ a.cust name, b.contact name FROM WHERE customers a, [email protected] b UPPER(b.contact name) UPPER(a.cust name); Execution Plan -----------------------------------------------0 SELECT STATEMENT Optimizer HINT: RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 REMOTE* SALES.ACME.COM 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'CUSTOMERS' 3 SERIAL FROM REMOTE SELECT "CONTACT NAME" FROM "CONTACTS" "B” 39

Execution Plan Operations REMOTE Submit a SQL statement to a remote database via Oracle Net. Input: typically no subordinate operations. Output: the results of the query from the remote database. Note that the database link used to access the remote database and the actual SQL submitted to the remote database will be accessible from the execution plan. 40

Execution Plan Operations SORT JOIN Same as the SORT GROUP BY operation, except that the input is sorted by the join column or columns in preparation for a join using the merge join algorithm. 41

Execution Plan Operations MERGE JOIN Perform a join between two sets of row data using the merge join algorithm. Inputs: two separate sets of row data. Output: the results of the join. Oracle reads rows from both inputs in an alternating fashion and merges together matching rows in order to generate output. The two inputs are assumed to be sorted on the join column or columns. 42

Summary of Operations We have not covered all of the execution plan operations, but we have covered some of the most common ones: - TABLE ACCESS FULL TABLE ACCESS BY INDEX ROWID INDEX UNIQUE SCAN INDEX RANGE SCAN NESTED LOOPS NESTED LOOPS OUTER HASH JOIN MERGE JOIN FILTER VIEW REMOTE SORT ORDER BY SORT GROUP BY SORT JOIN 43

Elements of a TKPROF Report Report heading – TKPROF version, date run, sort option, trace file One entry for each distinct SQL statement in trace file – Listing of SQL statement – OCI call statistics: count of parse, execute, and fetch calls, rows processed, and time and I/O used – Parse information: parsing user, recursive depth, library cache misses, and optimizer mode – Row source operation listing – Execution plan listing (optional) – Wait event listing (optional) 44

Elements of a TKPROF Report (continued) Report Summary – OCI call statistics totals – Counts of how many statements were found in the trace file, how many were distinct, and how many were explained in the report. 45

Sample TKPROF Report Heading TKPROF: Release 8.1.6.1.0 - Production on Wed Aug 9 19:06:36 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Trace file: example.trc Sort options: default ************************************************************************ count number of times OCI procedure was executed cpu cpu time in seconds executing elapsed elapsed time in seconds executing disk number of physical reads of buffers from disk query number of buffers gotten for consistent read current number of buffers gotten in current mode (usually for update) rows number of rows processed by the fetch or execute call 46

Sample OCI Call Statistics SELECT table name FROM user tables ORDER BY table name call count ------- -----Parse 1 Execute 1 Fetch 14 ------- -----total 16 cpu elapsed disk query current -------- ---------- --------- --------- --------0.01 0.02 0 0 0 0.00 0.00 0 0 0 0.59 0.99 0 33633 0 -------- ---------- --------- --------- --------0.60 1.01 0 33633 0 rows --------0 0 194 --------194 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: RSCHRAG [recursive depth: 0] 47

What the TKPROF Sample on the Previous Slide Tells Us The application called on Oracle to parse this statement once while SQL trace was enabled. The parse took 0.01 CPU seconds, 0.02 elapsed seconds. No disk I/Os or buffer gets took place during the parse, suggesting that no misses in the dictionary cache. Oracle was called on to execute this statement once. The execution took under 0.01 CPU seconds. No disk I/Os or buffer gets took place during the execution. (Queries often defer the work to the fetch phase.) Oracle was called on 14 times to perform a fetch, and a total of 194 rows were returned. 48

What the TKPROF Sample Tells Us (continued) Fetching took 0.59 CPU seconds, 0.99 elapsed seconds. Fetching required 33,633 buffer gets in consistent mode, but no physical reads were required. The statement was not in the library cache (shared pool) when the parse call came in. The cost-based optimizer and a goal of “choose” were used to parse the statement. The RSCHRAG user was connected to the database when the parse occurred. This statement was executed directly by the application; it was not invoked recursively by the SYS user or a database trigger. 49

Sample Row Source Operation Listing Rows ------194 194 195 195 195 11146 11146 11339 12665 33 193 387 194 388 Row Source Operation --------------------------------------------------SORT ORDER BY NESTED LOOPS NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS TABLE ACCESS BY INDEX ROWID OBJ INDEX RANGE SCAN (object id 34) TABLE ACCESS CLUSTER TAB INDEX UNIQUE SCAN (object id 3) INDEX UNIQUE SCAN (object id 33) TABLE ACCESS CLUSTER SEG INDEX UNIQUE SCAN (object id 9) TABLE ACCESS CLUSTER TS INDEX UNIQUE SCAN (object id 7) 50

Sample Execution Plan Listing Rows ------0 194 194 195 195 195 11146 11146 11339 12665 33 193 387 194 388 Execution Plan --------------------------------------------------SELECT STATEMENT GOAL: CHOOSE SORT (ORDER BY) NESTED LOOPS NESTED LOOPS (OUTER) NESTED LOOPS (OUTER) NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF 'OBJ ' INDEX (RANGE SCAN) OF 'I OBJ2' (UNIQUE) TABLE ACCESS (CLUSTER) OF 'TAB ' INDEX (UNIQUE SCAN) OF 'I OBJ#' (NON-UNIQUE) INDEX (UNIQUE SCAN) OF 'I OBJ1' (UNIQUE) TABLE ACCESS (CLUSTER) OF 'SEG ' INDEX (UNIQUE SCAN) OF 'I FILE# BLOCK#' (NON-UNIQUE) TABLE ACCESS (CLUSTER) OF 'TS ' INDEX (UNIQUE SCAN) OF 'I TS#' (NON-UNIQUE) 51

Notes About TKPROF Execution Plan Listings Execution plans are only included in TKPROF reports if the explain parameter is specified when TKPROF is invoked TKPROF will create and drop its own plan table if one does not already exist The row counts on each step are actuals—not estimates. This can be very helpful when troubleshooting queries that perform poorly. When TKPROF runs the EXPLAIN PLAN statement for a query, a different execution plan could be returned than was actually used in the traced session. 52

TKPROF Reports: More Than Just Execution Plans Listing of SQL statements and library cache miss information helps you determine if applications are using Oracle’s shared SQL facility effectively. Parse, execute, and fetch call counts help you determine if applications are using Oracle APIs effectively. CPU and I/O statistics help you zero in on resourceintensive SQL statements. Row counts on individual steps of the execution plans help you rework inefficient execution plans. 53

Wrapping Up Use EXPLAIN PLAN, queries against v sql plan, the autotrace facility in SQL*Plus, or GUI tools to view execution plans. Use TKPROF to format SQL trace files for human readability. Execution plans and TKPROF reports give the DBA and application developer a wealth of information that can be used to make applications efficient and perform well. The catch: you need to know how to interpret execution plans and TKPROF reports in order to get any benefit from them. You also ought to know when to use EXPLAIN PLAN versus when to query v sql plan. 54

Resources Download this slide show, the accompanying white paper, and many other useful presentations at: www.dbspecialists.com/presentations 55

Contact Information Roger Schrag Database Specialists, Inc. 388 Market Street, Suite 400 San Francisco, CA 94111 Tel: 415/344-0500 Email: [email protected] Web: www.dbspecialists.com 56