Brown Bag Introduction to SQL Tuning Three essential concepts

Brown Bag Introduction to SQL Tuning Three essential concepts

Introduction to SQL Tuning How to speed up a slow query? Find a better way to run the query Cause the database to run the query your way

Introduction to SQL Tuning How does a database run a SQL query? Join order Join method Access method

Example Query SQL 2 3 4 5 6 7 8 9 10 11 select sale date, product name, customer name, amount from sales, products, customers where sales.product number products.product number and sales.customer number customers.customer number and sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY') and product type 'Cheese' and customer state 'FL'; SALE DATE --------04-JAN-12 02-JAN-12 05-JAN-12 03-JAN-12 PRODUCT NAME -----------Feta Chedder Feta Chedder CUSTOMER NAME AMOUNT ----------------- ---------Sunshine State Co 300 Sunshine State Co 100 Green Valley Inc 400 Green Valley Inc 200

Join Order Join Order order in which tables in from clause are joined Two row sources at a time Row source: Table Result of join View as tree – execution tree or plan

Join Order – sales, products, customers join 2 join 1 sales customers products

Join Order as Plan Execution Plan ---------------------------------------------------------0 SELECT STATEMENT 1 0 HASH JOIN 2 1 HASH JOIN 3 2 TABLE ACCESS (FULL) OF 'SALES' (TABLE) 4 2 TABLE ACCESS (FULL) OF 'PRODUCTS' (TABLE 5 1 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE)

Bad Join Order – customers, products, sales join 2 join 1 customers sales products

Cartesian Join – all products to all customers SQL SQL SQL SQL 2 3 4 5 6 -- joining products and customers -- cartesian join select product name,customer name from products, customers where product type 'Cheese' and customer state 'FL'; PRODUCT NAME -----------Chedder Chedder Feta Feta CUSTOMER NAME ----------------Sunshine State Co Green Valley Inc Sunshine State Co Green Valley Inc

Plan with Cartesian Join Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer ALL ROWS 1 0 MERGE JOIN (CARTESIAN) 2 1 TABLE ACCESS (FULL) OF 'PRODUCTS' (TABLE) 3 1 BUFFER (SORT) 4 3 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE)

Selectivity Selectivity percentage of rows accessed versus total rows Use non-joining where clause predicates sale date, product type, customer state Compare count of rows with and without non-joining predicates

Count(*) to get selectivity -- # selected rows select count(*) from sales where sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY'); -- total #rows select count(*) from sales;

SQL 3 4 5 6 7 8 Selectivity of sub-tree select count(*) from sales, products where sales.product number products.product number and sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY') and product type 'Cheese'; COUNT(*) ---------4 SQL 2 3 4 select count(*) from sales, products where sales.product number products.product number; COUNT(*) ---------4

Modifying the Join Order Tables with selective predicates first Gather Optimizer Statistics Estimate Percent Histogram on Column Cardinality Hint Leading Hint Break Query into Pieces

Gather Optimizer Statistics -- 1 - set preferences begin DBMS STATS.SET TABLE PREFS(NULL,'SALES','ESTIMATE PERCENT','10'); DBMS STATS.SET TABLE PREFS(NULL,'SALES','METHOD OPT', 'FOR COLUMNS SALE DATE SIZE 254 PRODUCT NUMBER SIZE 1 ' 'CUSTOMER NUMBER SIZE 1 AMOUNT SIZE 1'); end; / -- 2 - regather table stats with new preferences execute DBMS STATS.GATHER TABLE STATS (NULL,'SALES');

Cardinality Hint SQL 2 3 4 5 6 7 8 9 10 11 select /* cardinality(sales 1) */ sale date, product name, customer name, amount from sales, products, customers where sales.product number products.product number and sales.customer number customers.customer number and sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY') and product type 'Cheese' and customer state 'FL'; SALE DATE --------04-JAN-12 02-JAN-12 05-JAN-12 03-JAN-12 PRODUCT NAME -----------Feta Chedder Feta Chedder CUSTOMER NAME AMOUNT ----------------- ---------Sunshine State Co 300 Sunshine State Co 100 Green Valley Inc 400 Green Valley Inc 200

Plan with Cardinality hint Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer ALL ROWS 1 0 HASH JOIN 2 1 HASH JOIN 3 2 TABLE ACCESS (FULL) OF 'SALES' (TABLE) 4 2 TABLE ACCESS (FULL) OF 'PRODUCTS' (TABLE 5 1 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE)

Leading Hint SQL 2 3 4 5 6 7 8 9 10 11 select /* leading(sales) */ sale date, product name, customer name, amount from sales, products, customers where sales.product number products.product number and sales.customer number customers.customer number and sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY') and product type 'Cheese' and customer state 'FL'; SALE DATE --------04-JAN-12 02-JAN-12 05-JAN-12 03-JAN-12 PRODUCT NAME -----------Feta Chedder Feta Chedder CUSTOMER NAME AMOUNT ----------------- ---------Sunshine State Co 300 Sunshine State Co 100 Green Valley Inc 400 Green Valley Inc 200

Break Query Into Pieces SQL 2 3 4 5 6 7 8 create global temporary table sales product results ( sale date date, customer number number, amount number, product type varchar2(12), product name varchar2(12) ) on commit preserve rows; Table created.

Break Query Into Pieces SQL 2 3 4 5 6 7 8 9 10 11 12 13 14 15 insert /* append */ into sales product results select sale date, customer number, amount, product type, product name from sales, products where sales.product number products.product number and sale date between to date('01/01/2012','MM/DD/YYYY') and to date('01/31/2012','MM/DD/YYYY') and product type 'Cheese'; 4 rows created.

Break Query Into Pieces SQL 2 3 4 5 6 select sale date, product name, customer name, amount from sales product results spr, customers c where spr.customer number c.customer number and c.customer state 'FL'; SALE DATE --------02-JAN-12 03-JAN-12 04-JAN-12 05-JAN-12 PRODUCT NAME -----------Chedder Chedder Feta Feta CUSTOMER NAME AMOUNT ----------------- ---------Sunshine State Co 100 Green Valley Inc 200 Sunshine State Co 300 Green Valley Inc 400

Join Methods Join Method way that data from two sources is joined Nested Loops Small number of rows in first table Unique index on second large table Hash Join Smaller or equal number of rows in first table No index required

Join Method – Nested Loops Execution Plan -----------------------------------------------------------------0 SELECT STATEMENT Optimizer ALL ROWS 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (TABLE) 2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF 'SALES' (TABLE) 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' 6 5 INDEX (RANGE SCAN) OF 'PRODUCTS INDEX' (INDEX) 7 2 INDEX (RANGE SCAN) OF 'CUSTOMERS INDEX' (INDEX)

Join Method – Hash Join Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer ALL ROWS 1 0 HASH JOIN 2 1 HASH JOIN 3 2 TABLE ACCESS (FULL) OF 'SALES' (TABLE) 4 2 TABLE ACCESS (FULL) OF 'PRODUCTS' 5 1 TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE)

Modifying the Join Method Hints use hash use nl Add Index Hash area size parameter

Join Methods Hints /* use hash(products) use nl(customers) */

Join Methods Indexes create index products index on products(product number); create index customers index on customers(customer number);

Join Methods Hash Area Size NAME -----------------------------------hash area size sort area size workarea size policy TYPE ----------integer integer string VALUE --------100000000 100000000 MANUAL

Access Methods Access method way that data is retrieved from table Index scan – small number of rows accessed Full scan – larger number of rows accessed

Modifying the Access Method Set Initialization Parameter optimizer index caching optimizer index cost adj db file multiblock read count Set Parallel Degree 1 Hints Full Index

Set Initialization Parameter alter system set optimizer index cost adj 1000 scope both sid '*';

Set Parallel Degree alter table sales parallel 8;

Full Scan and Index Hints /* full(sales) index(customers) index(products) */

Conclusion Use count queries to determine selective parts of where clause Modify the join order, join methods, and access methods using Optimizer statistics Hints Initialization parameters Breaking the query into pieces Parallel degree Indexes Compare elapsed time of query with new plan to original

Check For Improved Elapsed Time SQL set timing on SQL SQL select removed for clarity SALE DATE --------02-JAN-12 03-JAN-12 04-JAN-12 05-JAN-12 PRODUCT NAME -----------Chedder Chedder Feta Feta Elapsed: 00:00:00.00 CUSTOMER NAME AMOUNT ----------------- ---------Sunshine State Co 100 Green Valley Inc 200 Sunshine State Co 300 Green Valley Inc 400

Further Reading Oracle Database Concepts Chapter 7 SQL Oracle Database Performance Tuning Guide Chapter 11 The Query Optimizer Chapter 19 Using Optimizer Hints Oracle Database Reference Chapter 1 Initialization Parameters Oracle Database PL/SQL Packages and Types Reference Chapter 141 DBMS STATS Cost-Based Oracle Fundamentals - Jonathan Lewis http://www.bobbydurrettdba.com/resources/