Oracle - SQL Tuning

Basic Methods

1. Using Oracle Hints
2. Splitting the queries from the inner select and debugging
3. Using explain plan and remove full table scan
4. Using Materialized Views
5. In from clause put the tables with less records in the last,
In where clause put the index columns at the first ,
In where clause put the columns with less records in the first

Using Hints to tune SQL

There are many hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL. See Oracle Good Hints List - Sql Query Tuning.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can tame a wild optimizer and give you optimal performance.

Hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.



SELECT /*+ hint --or-- text */
statement body
-- or --
SELECT --+ hint --or-- text
statement body




Where:

/*, */ — These are the comment delimiters for multi-line comments
-- — This is the comment delimiter for a single line comment (not usually used for hints)
+ — This tells Oracle a hint follows, it must come immediately after the /*
hint — This is one of the allowed hints
text — This is the comment text


Hint Meaning
+ Must be immediately after comment indicator, tells Oracle this is a list of hints.
ALL_ROWS Use the cost based approach for best throughput.
CHOOSE Default, if statistics are available will use cost, if not, rule.
FIRST_ROWS Use the cost based approach for best response time.
RULE Use rules based approach; this cancels any other hints specified for this statement.
Access Method Hints:
CLUSTER(table) This tells Oracle to do a cluster scan to access the table.
FULL(table) This tells the optimizer to do a full scan of the specified table.
HASH(table) Tells Oracle to explicitly choose the hash access method for the table.
HASH_AJ(table) Transforms a NOT IN subquery to a hash anti-join.
ROWID(table) Forces a rowid scan of the specified table.
INDEX(table [index]) Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
INDEX_ASC (table [index]) Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
INDEX_DESC(table [index]) Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index) Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
INDEX_FFS(table index) Perform a fast full index scan rather than a table scan.
MERGE_AJ (table) Transforms a NOT IN subquery into a merge anti-join.