AN INTRODUCTION TO Execution plan of Queries

1. AN INTRODUCTION TO Execution plan of Queries • These slides have been adapted from a presentation originally made by ORACLE. • The full set of original slides are available at : • http://www.slideshare.net/Jackieken/explaining-the-explain-plan

2. What is an Execution plan? • Detailed steps necessary to execute a SQL statement • Steps expressed as • a set of database operators that consumes and produces rows • Optimizer decides • The order of the operators and their implementation • using query transformations and physical optimization techniques • The display is commonly shown in a tabular format, • but a plan is in fact tree-shaped

3. Tabular andTreeRepresentations Query SELECT prod_category, avg(amount_sold)FROM sales s, products pWHERE p.prod_id = s.prod_idGROUP BY prod_category;Tabular representation of plan -----------------------------------------------------------Id Operation Name -----------------------------------------------------------0 SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES ---------------------------------------------------------- Tree-shaped representation of plan GROUP BY | JOIN ______|_______ | |TABLE ACCESS TABLE ACCESS PRODUCTS SALES

4. How to get an Execution Plan ? • EXPLAIN PLAN command • Displays an execution plan for a SQL statement without actually executing the statement • EXPLAIN PLAN SET STATEMENT_ID = '<some-name>' FOR <select statement to be analyzed>; • Plan stored in PLAN_TABLE

5. Query the PLAN_TABLE • SELECT LPAD(' ', 2*LEVEL)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME Query_Plan • FROM PLAN_TABLE • CONNECT BY PRIOR ID = PARENT_ID • and STATEMENT_ID = '<some-name>' • START WITH ID=0 and STATEMENT_ID = '<some-name>' • ORDER BY ID;

6. USE CASE Use Case: Plan review allows review of access paths and join types • Access paths • Is the data being accessed in the best way? Scan? Index lookup? • Join type • Are the right join algorithm types being used?

7. Access Paths Access Path describes method to get data out of the table • The access path can be: • Full table scan • Table access by Rowid • Index unique scan • Index range scan (descending) • Index skip scan • Full index scan • Fast full index scan • Index joins • Bitmap indexes

8. EXAMPLE OF Access Paths What access method should be use for this Query? SELECT e.name, e.salary, d.dept_name FROM hr.employees e, hr.departments d WHERE d.dept_name IN ('Marketing‘,'Sales') AND e.department_id=d.department_id; Employees has 107 rows Departments has 27 rows Foreign key relationship between Employees and Departments on dept_id Look in Operation session to see how obj is being accessed

9. Join Algorithm Type • A Join retrieve data from more than one table • Possible join algorithm types are • Nested Loops joins • Hash Joins • Partition Wise Joins • Sort Merge joins • Cartesian Joins • Outer Joins

10. Look in Operation session to see Join strategy Join Algorithm Type Example What Join type should be use for this Query? SELECT e.name, e.salary, d.dept_name FROM hr.employees e, hr.departments d WHERE d.dept_name IN ('Marketing‘,'Sales') AND e.department_id=d.department_id; Employees has 107 rows Departments has 27 rows Foreign key relationship between Employees and Departments on dept_id