SQL Tuning Tips

SQL Tuning Tips

Avoid un-necessary sorts - use union all, use compute stats while creating index.

Hint when needed.

Avoid Cartesian products

Avoid full table scans on large tables

Avoid joining too many tables

Use SQL standards and conventions to reduce parsing

Try to use indexed cols in the WHERE clause

Use compound indexes with care (Do not repeat columns)

Skip -scan index (INDEX_SS)- to use a composite index even if the leading column is not a limiting condition in the sql.

Monitor index browning (due to deletions; rebuild as necessary)

Use literals in the WHERE clause (use bind variables)

Monitor V$SESSION_LONGOPS to detect long running operations

Use the SHARED_CURSOR parameter

Finally Test Correctly - with Large volumes, Large users.