-
Tune the workload first
– Always tune your workload as a whole, optimizing the optimizer
parameters (optimizer_mode,
optimizer_index_cost_adj, db_file_multiblock_read_count) before
tuning individual SQL statements.
-
Avoid re-parsing of SQL statements
– The library cache is intended to make SQL re-entrant, and you can
improve response time by cutting-down on redundant SQL parsing. Be sure to
use
cursor_sharing when appropriate.
-
Use materialized views -
Materialized views can pre-summarize aggregations and pre-join tables,
making SQL run super fast in systems with low volume update activity.
-
Never assume that CBO statistics are correct – Using
the GIGO principle (garbage-in, garbage-out), don’t hesitate to
re-analyze tables and indexes with
dbms_stats.
-
Use histograms for tuning
– Many common SQL problems (e.g. sub-optimal table join order) are
caused by poor cardinality estimates.
Apply histograms providently (only when required) to help the
optimizer estimate the size of intermediate rowset operations.
-
Use function-based indexes
- In almost all cases, the use of a built-in function like
to_char, decode, substr, etc.
in an SQL query may cause a full-table scan of the target table. To
avoid this problem, many Oracle DBAs will create corresponding indexes
that make use of function-based indexes. If a corresponding
function-based index matches the built-in function of the query, Oracle
will be able to service the query with an index range scan thereby
avoiding a potentially expensive full-table scan.
-
Decompose complex SQL
– You can use the with clause
and global temporary tables to flatten-out complex subqueries and make
execution times faster.
-
Avoid subqueries
– May types of subqueries (exists,
in, not in) can be re-written
as a standard join with faster performance.
-
Watch out for counterintuitive tips
– tricks such as using where
rownum=1 can be dangerous.
-
Use views sparingly
– Views were designed to assist end-users, and views make complex
queries “appear” as-if they were a discrete table.
Hence, running production queries against views can cause a host
of optimization problems.
-
Watch out for the having
clause
– You can often decompose a complex query using the
with clause to avoid the use
of the expensive having clause.
-
Use union all when possible
- The union clause removes
duplicates, and it must perform an expensive sort to remove duplicate
rows. Instead, the
union all clause is faster
because it does not sort to remove duplicate rows.
-
Always reference an indexed column
– SQL with a where clause that does not reference any indexed columns
can result in an unnecessary large-table full-table scan.
-
Avoid using BIF’s in where clause predicates
- Don’t invalidate columns by changing the left-hand side of a where
clause predicate (where substr(last_name,1,3) = 'Jon';
where trunc(my_date) = trunc(sysdate)).
-
Test with the RULE hint
– The RULE hint is fantastic for testing whether a sub-optimal SQL query
is failing because of a missing index, or bad CBO statistics.
In many cases, the RULE hints simplicity can help tune SQL
statements faster.
