Long Running Statements
• Identify the specific statement(s) that are causing a problem. The simplest way to do this usually involves running the individual statements using SQLPlus
and timing them (SET TIMING ON)
Full Table Accesses
• Use EXPLAIN to look at the execution plan of the statement. Look for any full table accesses that look dubious. Remember, a full table scan of a small table
is often more efficient than access by rowid.
Indexes that can Help
• Check to see if there are any indexes that may help performance. A quick way to do this is to run the statement using the Rule Based Optimizer (RBO)
(SELECT /*+ RULE */ ). Under the RBO, if an index is present it will be used. Displaying the Execution Plan may help you in identifying these indexes.
Adding Indexes
• Try adding new indexes to the system to reduce excessive full table scans. Typically, foreign key columns should be indexed as these are regularly used in
join conditions. On some queries it may be necessary to add composite (concatenated) indexes that will only aid individual queries. Remember, excessive
indexing can reduce INSERT, UPDATE and DELETE performance.
Data Volumes
• Test with similar sized data volumes to production. If this is not possible, manipulate the table statistics so it appears these small tables have large data
volumes.
• Be sure to explicitly perform data type conversions rather than letting Oracle implicitly perform them.
Execution Plans
• Determine the statement's general execution plan (nested loops: good filter conditions and supporting indexes; hash joins: few filter conditions and full table
scans; star joins: multiple lookup tables and one large fact table).
Full Table Scans
• Don't be afraid of full table scans. They are appropriate when more than 5-10% of the table will be retrieved.
SQL Statement Conditions
• Always consider greater than or equal ( >= ) or less than or equal ( <= ) in the WHERE clause than just merely less than ( < ) or greater than ( >) to reduce
block reads
• For ranged select statements, it’s better to use BETWEEN than sets of >=, <=, >, <.
Table Cache
• Consider caching your big and frequently queried table to improve parsing time.
Driving Tables
• Have the most selective table (the one that yields the fewest rows) be the driving table.
• Consider putting your driving table at the rightmost position in the FROM clause of your SQL statement, and condition of the driving table at the start of the
where clause.
Existence
• Always consider using “exists” rather than using “in” for checking presence of a single row in SQL.
Example:
Bad Statement
Code: Select all
SELECT p.product_id
FROM products p
WHERE p.item_no IN (SELECT i.item_no
FROM items i);Code: Select all
SELECT p.product_id
FROM products p
WHERE EXISTS (SELECT '1'
FROM items i
WHERE i.item_no =
p.item_no);• Always use rownum for your condition whenever possible, especially for presence checking.
Example:
Bad Statement
Code: Select all
SELECT Count(*)
INTO v_count
FROM items
WHERE item_size = 'SMALL';
IF v_count = 0 THEN
-- Do processing related to no small items
present
END IF;Code: Select all
SELECT Count(*)
INTO v_count
FROM items
WHERE item_size = 'SMALL'
AND rownum = 1;
IF v_count = 0 THEN
-- Do processing related to no small items
present
END IF;• Use bind variables whenever possible. Bind variables reduces parsing.
• Use array processing whenever possible. Performing operations 1-rowat-
a-time causes excessive SQL*Net overhead. Performing these
operations in bulk is much more efficient.
• Have the most selective table (the one that yields the fewest rows) be
the driving table.
• Use greater than or equal ( >= ) or less than or equal ( <= ) in the
WHERE clause than just merely less than ( < ) or greater than ( >) to
reduce block reads
• For ranged select statements, it’s better to use BETWEEN than sets of
>=, <=, >, <.
• Always use rownum for your condition whenever possible, especially
for presence checking.
• use “exists” rather than using “in” for checking presence of a single
row in SQL.
• Be sure to explicitly perform data type conversions (e. g. datecolumn1=
to_date(’01-JAN-2003’,’DD-MON-YYYY’))
Don’ts :
• Don’t perform a function on the indexed column i.e. WHERE
Upper(name) = 'JONES'
• Avoid performing mathematical operations on the indexed column i.e.
WHERE salary + 1 = 10001
• Do not concatenate a column that is indexed i.e. WHERE firstname || ' '
|| lastname = 'JOHN JONES'
• Avoid the use of 'OR' statements when you opt to use an index, it
confuses the Cost Based Optimizer (CBO). It will rarely choose to use
an index on column referenced using an OR statement. It will even
ignore optimizer hints in this situation. The only way of guaranteeing
the use of indexes in these situations is to use the /*+ RULE */ hint.
• Use DISTINCT only when necessary.
The use of DISTINCT will always cause full table scan
• Do not use the ORDER BY clause unless it is actually needed. It is
better to let SQL use the primary key as the sort since it’s always
indexed