Use EXPLAIN PLAN and TKPROF to Tune Your Applications Summary: EXPLAIN PLAN overview TKPROF overview Why??? Reading execution plans Reading TKPROF reports www.dbspecialists.com/presentations 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. 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. Important Columns in the Plan Table statement_id timestamp operation
Unique identifier for each execution plan When the execution plan was generated 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
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 EXPLAIN PLAN Syntax EXPLAIN PLAN [SET STATEMENT_ID = <string in single quotes>] [INTO
] FOR Use EXPLAIN PLAN and TKPROF To Tune Your Applications
i
<SQL statement>;
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 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';
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
Other Ways to View Execution Plans Use EXPLAIN PLAN and TKPROF To Tune Your Applications
ii
The autotrace feature in SQL*Plus Performance tuning tools
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)
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
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. A Simple Query to Display Actual Execution Plans SET COL COL COL COL
VERIFY OFF id parent_id operation object_name
FORMAT FORMAT FORMAT FORMAT
999 999 HEADING "PARENT" a35 TRUNCATE a30
Use EXPLAIN PLAN and TKPROF To Tune Your Applications
iii
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;
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. 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)
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. Formatting a Trace File with TKPROF Invoke TKPROF from the operating system prompt like this: tkprof