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
The Basics of Efficient SQL
SELECT
FOR UPDATE
Filtering
WHERE
ORDER BY
SELECT * FROM division;
SELECT division_id, name,
city, state, country FROM
division;
SELECT division_id FROM
division;
often ignored by the Optimizer
depends on query and index complexity
may need ORDER BY using composite indexes
GROUP BY
The Basics of Efficient SQL
SELECT
Avoid unintentional full table scans
FOR UPDATE SELECT * FROM division WHERE country LIKE
'%a%'
LIKE '%a%';
Filtering
WHERE
ORDER BY
Match indexes
Exact hits (equality)
SELECT * FROM division WHERE
1
WHERE division_id
division_id == 1;
often ignored by the Optimizer
scans / skip scans / full index scans
depends on queryRange
and index complexity
may need ORDEREXISTS
(correlate)
faster
than IN
BY using
composite
indexes
GROUP BY Biggest filters first
Full table scans can sometimes be faster
Resorts on result after WHERE and GROUP BY
Dont repeat sorting (ORDER BY often ignored)
The Basics
of
Efficient
SQL
by SELECT
SELECT
SELECT division_id FROM division ORDER BY
division_id;
FOR UPDATE by WHERE
Filtering
WHERE
ORDER BY
SELECT * FROM division WHERE division_id < 10
ORDER BY division_id;
GROUP BY
SELECT state, COUNT(state) FROM division GROUP
often ignored by the
BYOptimizer
state ORDER BY state;
depends on query and index complexity
DISTINCT
may need ORDERby
BY using composite indexes
GROUP BY
SELECT DISTINCT(state) FROM division ORDER BY
state;
by indexes
SELECT division_id FROM division ORDER BY
division_id;
Resorts on result after WHERE and GROUP BY
Dont repeat sorting (ORDER BY often ignored)
The Basics
of
Efficient
SQL
by SELECT
SELECT
SELECT division_id
division_id FROM division ORDER BY
division_id;
FOR UPDATE by WHERE
Filtering
WHERE
ORDER BY
SELECT * FROM division WHERE division_id
division_id << 10
10
ORDER BY division_id;
GROUP BY
SELECT state, COUNT(state) FROM division GROUP
GROUP
often ignored by the
Optimizer
BY
BY state
state ORDER BY state;
depends on query and index complexity
DISTINCT
may need ORDERby
BY using composite indexes
GROUP BY
SELECT DISTINCT(state)
DISTINCT(state) FROM division ORDER BY
state;
by indexes
SELECT division_id
division_id FROM division ORDER BY
division_id;
The Basics of Efficient SQL
SELECT
FOR UPDATE
GROUP BY
Filtering
WHERE
SELECT state, COUNT(state) FROM division GROUP BY
state ORDER BY state;
HAVING (filters aggregate)
ORDER BY SELECT
state, COUNT(state) FROM division GROUP BY
ignored state
by the HAVING
Optimizer
COUNT(state) > 1;
often
depends on query and index complexity
use WHERE
may need ORDER BY using composite indexes
SELECT state, COUNT(state) FROM division WHERE
state = 'NY' GROUP BY state;
GROUP BY
not HAVING
Use WHERE
not HAVING
SELECT state, COUNT(state) FROM division GROUP BY
state HAVING state = 'NY';
The Basics of Efficient SQL
SELECT
FOR UPDATE
GROUP BY
Filtering
WHERE
SELECT state, COUNT(state) FROM division GROUP BY
state ORDER BY state;
HAVING (filters aggregate)
ORDER BY SELECT
state, COUNT(state) FROM division GROUP BY
ignored state
by the HAVING
Optimizer
COUNT(state)
1
HAVING
COUNT(state) >> 1;
often
depends on query and index complexity
use WHERE
may need ORDER BY using composite indexes
SELECT state, COUNT(state) FROM division WHERE
WHERE
state
state == 'NY'
'NY' GROUP BY state;
GROUP BY
not HAVING
Use WHERE
not HAVING
SELECT state, COUNT(state) FROM division GROUP BY
state HAVING
'NY'
HAVING state
state == 'NY';
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 (>)
avoid negatives (!=, NOT)
avoid LIKE
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
Common Sense Indexing
Dont always need indexes
table with few columns
static data
small tables
appended tables (SQL*Loader)
How to index
single column surrogate sequences
dont override PK and FKs
avoid nullable columns
Common Sense Indexing
Read write indexing
BTree
Often read only
Bitmaps
IOTs
Clusters
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
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
Common Sense Indexing
Often read only
IOTs
small number of columns
small tables
heard good things in Oracle RAC
even highly active DML environments
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
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
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
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)
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
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)
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
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
Finding Problem Queries
EXPLAIN PLAN
SET AUTOTRACE ON EXPLAIN
$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL Trace and TKPROF
Finding Problem Queries
Performance views
V$SQLAREA
executions
parsing
sorting
disk and buffer reads
fetching
V$SQL
optimizer cost
CPU time
elapsed time
Wait Event
Interface
Wait Event Interface
Wait Event Interface
Wait Event Interface
Wait Event Interface
Wait Event Interface
Wait Event Interface
Wait Event Interface
Performance overview
Database health overview
Drilldown
More drilldown
TopSQL
EXPLAIN PLAN
Wait Event Interface
Wait Event Interface
Wait Event Interface
Use
Use the
the HELP
HELP FILES
FILES in
in
Oracle
Oracle
Enterprise
Use theEnterprise
help files
Manager
Manager
