Tuning Oracle SQL

1
Tuning Oracle SQL

• The Basics of Efficient SQL
• Common Sense Indexing
• The Optimizer
• Making SQL Efficient
• Finding Problem Queries
• Oracle Enterprise Manager
• Wait Event Interface

2
The Basics of Efficient SQL
SELECT FROM division SELECT division_id, name,
city, state, country FROM division SELECT
division_id FROM division

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY

3
The Basics of Efficient SQL

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY

Avoid unintentional full table scans SELECT
FROM division WHERE country LIKE 'a' Match
indexes Exact hits (equality) SELECT FROM
division WHERE division_id 1 Range scans /
skip scans / full index scans EXISTS (correlate)
faster than IN Biggest filters first Full table
scans can sometimes be faster
4
The Basics of Efficient SQL
Resorts on result after WHERE and GROUP BY Dont
repeat sorting (ORDER BY often ignored) by
SELECT SELECT division_id FROM division ORDER BY
division_id by WHERE SELECT FROM division
WHERE division_id lt 10 ORDER BY
division_id GROUP BY SELECT state, COUNT(state)
FROM division GROUP BY state ORDER BY state by
DISTINCT SELECT DISTINCT(state) FROM division
ORDER BY state by indexes SELECT division_id
FROM division ORDER BY division_id

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY

5
The Basics of Efficient SQL
Resorts on result after WHERE and GROUP BY Dont
repeat sorting (ORDER BY often ignored) by
SELECT SELECT division_id FROM division ORDER BY
division_id by WHERE SELECT FROM division
WHERE division_id lt 10 ORDER BY
division_id GROUP BY SELECT state, COUNT(state)
FROM division GROUP BY state ORDER BY state by
DISTINCT SELECT DISTINCT(state) FROM division
ORDER BY state by indexes SELECT division_id
FROM division ORDER BY division_id

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY

6
The Basics of Efficient SQL

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY
• Use WHERE not HAVING

GROUP BY SELECT state, COUNT(state) FROM division
GROUP BY state ORDER BY state HAVING (filters
aggregate) SELECT state, COUNT(state) FROM
division GROUP BY state HAVING COUNT(state) gt
1 use WHERE SELECT state, COUNT(state) FROM
division WHERE state 'NY' GROUP BY state not
HAVING SELECT state, COUNT(state) FROM division
GROUP BY state HAVING state 'NY'
7
The Basics of Efficient SQL

• SELECT
• FOR UPDATE
• Filtering
• WHERE
• ORDER BY
• often ignored by the Optimizer
• depends on query and index complexity
• may need ORDER BY using composite indexes
• GROUP BY
• Use WHERE not HAVING

GROUP BY SELECT state, COUNT(state) FROM division
GROUP BY state ORDER BY state HAVING (filters
aggregate) SELECT state, COUNT(state) FROM
division GROUP BY state HAVING COUNT(state) gt
1 use WHERE SELECT state, COUNT(state) FROM
division WHERE state 'NY' GROUP BY state not
HAVING SELECT state, COUNT(state) FROM division
GROUP BY state HAVING state 'NY'
8
The Basics of Efficient SQL

• Functions
• conversions
• miss indexes
• counteract with function based indexing
• avoid using
• DECODE
• CASE expressions
• set operators (UNION)
• Use sequences
• Use equality () or range scans (gt)
• avoid negatives (!, NOT)
• avoid LIKE

9
The Basics of Efficient SQL

• Joins
• avoid Cartesian Products
• avoid anti joins
• avoid outer joins
• perhaps replace
• multiple table complex joins
• with subquery semi joins and inline views
• Be careful with views

10
Common Sense Indexing

• Dont always need indexes
• table with few columns
• static data
• small tables
• appended tables (SQLLoader)
• How to index
• single column surrogate sequences
• dont override PK and FKs
• avoid nullable columns

11
Common Sense Indexing

• Read write indexing
• BTree
• Often read only
• Bitmaps
• IOTs
• Clusters

12
Common Sense Indexing

• Read write indexing
• BTree
• function based
• can help a lot
• get out of control
• everybody wants one
• reverse key
• surrogate keys
• High insertion rates
• not DW
• Oracle RAC

13
Common Sense Indexing

• Often read only
• Bitmaps
• can be much faster than BTrees
• can deteriorate drastically over time
• twice as fast in my book
• at a previous client
• 1 year of DML activity
• 100s of times slower
• problem was nobody knew why
• and nobody wanted to change anything

14
Common Sense Indexing

• Often read only
• IOTs
• small number of columns
• small tables
• heard good things in Oracle RAC
• even highly active DML environments

15
The Optimizer

• Is intelligent
• better with simple queries
• Is usually correct
• Nothing is set in stone
• Verify SQL code efficiency
• use EXPLAIN PLAN
• SET AUTOTRACE ON EXPLAIN
• ORACLE_HOME/rdbms/admin/utlxplan.sql

16
The Optimizer

• Everything cost based
• rule based is redundant
• Maintain statistics
• Dynamic sampling
• OPTIMIZER_DYNAMIC_SAMPLING
• Set TIMED_STATISTICS
• Histograms
• maintain as for statistics
• use for unevenly distributed indexes

17
The Optimizer

• Tables
• full Table scans
• small static tables
• reading most of the rows
• over 10 for the Optimizer
• reading deleted rows
• parallel table scans
• sample table scans
• retrieve only portion of table by blocks or
• SELECT FROM generalledger SAMPLE(0.001)
• ROWID scans

18
The Optimizer

• Indexes
• index unique scan
• index range scan
• reverse order index range scan
• index skip scan
• index full scan
• fast full index scan
• others (very specific)

19
The Optimizer

• Joins
• nested loop join
• most efficient
• row sets both small
• one large and one small row set
• one sorted
• hash join
• both large with little difference
• temporary hash table generated

20
The Optimizer

• More on joins
• sort merge join
• inefficient
• both rows sets sorted then merge sorted
• other join types
• semi joins
• bitmap joins
• star queries
• Cartesian joins
• outer joins (nested, hash or sort merge)

21
The Optimizer

• Hints can change things
• influence the optimizer
• CURSOR_SHARING
• configuration parameter
• FORCE (OLTP)
• EXACT (DW)
• FIRST or ALL_ROWS
• DYNAMIC_SAMPLING
• change table scans
• FULL

22
The Optimizer

• More on hints
• change index scans
• INDEX_ASC, INDEX_DESC
• INDEX_FFS
• INDEX_JOIN (join indexes)
• INDEX_SS_ASC or INDEX_SS_DESC
• NO_INDEX, NO_INDEX_FFS or NO_INDEX_SS
• change joins
• can change join type and influence with
  parameters
• parallel SQL

23
Finding Problem Queries

• EXPLAIN PLAN
• SET AUTOTRACE ON EXPLAIN
• ORACLE_HOME/rdbms/admin/utlxplan.sql
• SQL Trace and TKPROF

24
Finding Problem Queries

• Performance views
• VSQLAREA
• executions
• parsing
• sorting
• disk and buffer reads
• fetching
• VSQL
• optimizer cost
• CPU time
• elapsed time

Wait Event Interface
25
Wait Event Interface
26
Wait Event Interface
27
Wait Event Interface
28
Wait Event Interface
29
Wait Event Interface
30
Wait Event Interface
31
Wait Event Interface
32
Performance overview
33
Database health overview
34
Drilldown
35
More drilldown
36
TopSQL
37
EXPLAIN PLAN
38
Wait Event Interface
39
Wait Event Interface
40
Wait Event Interface
41
Use the help files
Use the HELP FILES in Oracle Enterprise Manager