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.