Introduction to SQL Tuning

1
Introduction to SQL Tuning

• Brown Bag

• Three essential concepts

2
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

3
Introduction to SQL Tuning

• How does a database run a SQL query?
• Join order
• Join method
• Access method

4
Example Query
SQLgt select 2 sale_date, product_name,
customer_name, amount 3 from sales, products,
customers 4 where 5 sales.product_numberpro
ducts.product_number and 6 sales.customer_numbe
rcustomers.customer_number and 7 sale_date
between 8 to_date('01/01/2012','MM/DD/YYYY')
and 9 to_date('01/31/2012','MM/DD/YYYY')
and 10 product_type 'Cheese' and 11
customer_state 'FL' SALE_DATE PRODUCT_NAME
CUSTOMER_NAME AMOUNT ---------
------------ ----------------- ---------- 04-JAN-1
2 Feta Sunshine State Co
300 02-JAN-12 Chedder Sunshine State Co
100 05-JAN-12 Feta Green Valley Inc
400 03-JAN-12 Chedder Green Valley Inc
200
5
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

6
Join Order sales, products, customers
7
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)
8
Bad Join Order customers, products, sales
9
Cartesian Join all products to all customers
SQLgt -- joining products and customers SQLgt --
cartesian join SQLgt SQLgt select 2
product_name,customer_name 3 from products,
customers 4 where 5 product_type 'Cheese'
and 6 customer_state 'FL' PRODUCT_NAME
CUSTOMER_NAME ------------ ----------------- Chedd
er Sunshine State Co Chedder Green
Valley Inc Feta Sunshine State Co Feta
Green Valley Inc
10
Plan with Cartesian Join
Execution Plan -----------------------------------
----------------------- 0 SELECT
STATEMENT OptimizerALL_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)
11
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

12
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
13
Selectivity of sub-tree
SQLgt select count() from sales, products 3
where 4 sales.product_numberproducts.product_n
umber and 5 sale_date between 6
to_date('01/01/2012','MM/DD/YYYY') and 7
to_date('01/31/2012','MM/DD/YYYY') and 8
product_type 'Cheese' COUNT() ----------
4 SQLgt select count() 2 from sales,
products 3 where 4 sales.product_numberprod
ucts.product_number COUNT() ----------
4
14
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

15
Gather Optimizer Statistics
-- 1 - set preferences begin DBMS_STATS.SET_TABL
E_PREFS(NULL,'SALES','ESTIMATE_PERCENT','10') DBM
S_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_S
TATS (NULL,'SALES')
16
Cardinality Hint
SQLgt select /cardinality(sales 1) / 2
sale_date, product_name, customer_name, amount
3 from sales, products, customers 4 where 5
sales.product_numberproducts.product_number
and 6 sales.customer_numbercustomers.customer_
number and 7 sale_date between 8
to_date('01/01/2012','MM/DD/YYYY') and 9
to_date('01/31/2012','MM/DD/YYYY') and 10
product_type 'Cheese' and 11 customer_state
'FL' SALE_DATE PRODUCT_NAME CUSTOMER_NAME
AMOUNT --------- ------------ -----------------
---------- 04-JAN-12 Feta Sunshine State
Co 300 02-JAN-12 Chedder Sunshine
State Co 100 05-JAN-12 Feta Green
Valley Inc 400 03-JAN-12 Chedder
Green Valley Inc 200
17
Plan with Cardinality hint
Execution Plan -----------------------------------
----------------------- 0 SELECT
STATEMENT OptimizerALL_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)
18
Leading Hint
SQLgt select /leading(sales) / 2 sale_date,
product_name, customer_name, amount 3 from
sales, products, customers 4 where 5
sales.product_numberproducts.product_number and
6 sales.customer_numbercustomers.customer_numbe
r and 7 sale_date between 8
to_date('01/01/2012','MM/DD/YYYY') and 9
to_date('01/31/2012','MM/DD/YYYY') and 10
product_type 'Cheese' and 11 customer_state
'FL' SALE_DATE PRODUCT_NAME CUSTOMER_NAME
AMOUNT --------- ------------ -----------------
---------- 04-JAN-12 Feta Sunshine State
Co 300 02-JAN-12 Chedder Sunshine
State Co 100 05-JAN-12 Feta Green
Valley Inc 400 03-JAN-12 Chedder
Green Valley Inc 200
19
Break Query Into Pieces
SQLgt create global temporary table
sales_product_results 2 ( 3 sale_date
date, 4 customer_number number, 5 amount
number, 6 product_type varchar2(12), 7
product_name varchar2(12) 8 ) on commit
preserve rows Table created.
20
Break Query Into Pieces
SQLgt insert /append / 2 into
sales_product_results 3 select 4
sale_date, 5 customer_number, 6 amount, 7
product_type, 8 product_name 9 from sales,
products 10 where 11 sales.product_numberprod
ucts.product_number and 12 sale_date between
13 to_date('01/01/2012','MM/DD/YYYY') and 14
to_date('01/31/2012','MM/DD/YYYY') and 15
product_type 'Cheese' 4 rows created.
21
Break Query Into Pieces
SQLgt select 2 sale_date, product_name,
customer_name, amount 3 from
sales_product_results spr, customers c 4
where 5 spr.customer_numberc.customer_number
and 6 c.customer_state 'FL' SALE_DATE
PRODUCT_NAME CUSTOMER_NAME
AMOUNT --------- ------------ -----------------
---------- 02-JAN-12 Chedder Sunshine State
Co 100 03-JAN-12 Chedder Green Valley
Inc 200 04-JAN-12 Feta Sunshine
State Co 300 05-JAN-12 Feta Green
Valley Inc 400
22
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

23
Join Method Nested Loops
Execution Plan -----------------------------------
------------------------------- 0 SELECT
STATEMENT OptimizerALL_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)
24
Join Method Hash Join
Execution Plan -----------------------------------
----------------------- 0 SELECT
STATEMENT OptimizerALL_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)
25
Modifying the Join Method

• Hints
• use_hash
• use_nl
• Add Index
• Hash_area_size parameter

26
Join Methods Hints
/ use_hash(products) use_nl(customers) /
27
Join Methods Indexes
create index products_index on products(product_nu
mber) create index customers_index on
customers(customer_number)
28
Join Methods Hash_Area_Size
NAME TYPE
VALUE ------------------------------------
----------- --------- hash_area_size
integer 100000000 sort_area_size
integer 100000000 workarea_s
ize_policy string MANUAL
29
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

30
Modifying the Access Method

• Set Initialization Parameter
• optimizer_index_caching
• optimizer_index_cost_adj
• db_file_multiblock_read_count
• Set Parallel Degree gt 1
• Hints
• Full
• Index

31
Set Initialization Parameter
alter system set optimizer_index_cost_adj1000
scopeboth sid''
32
Set Parallel Degree
alter table sales parallel 8
33
Full Scan and Index Hints
/ full(sales) index(customers) index(products)
/
34
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

35
Check For Improved Elapsed Time
SQLgt set timing on SQLgt SQLgt select removed
for clarity SALE_DATE PRODUCT_NAME
CUSTOMER_NAME AMOUNT ---------
------------ ----------------- ---------- 02-JAN-1
2 Chedder Sunshine State Co
100 03-JAN-12 Chedder Green Valley Inc
200 04-JAN-12 Feta Sunshine State Co
300 05-JAN-12 Feta Green Valley Inc
400 Elapsed 000000.00
36
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/