SQL Tuning Steps

3 Main steps to Tuning:
Identify high load sql's or programs
Make sure that these sql's/programs have optimal execution plans
If not, take corrective measures to improve the performance

Purpose/Goals of Tuning:
Reduce the response time taken by sql/program
Reduce the resources consumed

Ways to accomplish these goals:
Reduce/Optimize the highly utilized query
Balance the work load
Parallelize the work load

Reduce/Optimize the highly utilized query:
You can rewrite the query in such a way that functionality does not change - in case the underlying indexes are not getting used or if the query is written badly in first place
See if you can improve the performance by creating an index on any of the columns that results in relative low data fetch as compared to number of rows in the table

Balance the work load:
Look out if any of the programs/sql statements can be moved to batch process.  Since the load on the OLTP  system is considerably more during day time.  So, if any process is not required to execute during day time - it can be safely moved to execute during off peak hours.  This kind of balancing can free up some resources and helps in improving the performance

Parallelize the work load:
See if any of the programs/sql's running in batch process can be parallelized.  This will significantly improved the execution time of the sql.  This is normally advisable for batch processes as compared to OLTP process, since batch jobs are generally scheduled during off peak hours.  And comparatively more resources are available during this time.

Different tools/utilities to identify high load sql statements or programs:
Automatic Database Diagnostic Monitor (ADDM)
SQL Trace - TKPROF
Automatic Workload Repository
V$SQL* Views - V$SQLSTATS, V$SQLTEXT,  V$SQLAREA, V$SQL_PLAN, V$SQL and V$SQL_PLAN_STATISTICS

How to Identify resource consuming SQL:
Categories:
Specific program that is consuming considerable amount of resources
Spread across application - could be multiple statements across application consuming high amount of resources

Speicific program:
First thing to look for is EXPLAIN PLAN of the sql statements within the program and make sure that they are optimal
If EXPLAIN PLAN is not possible because of dynamic sql, than enable SQL_Trace for the program and use TKPROF utility to generate the output.  You can sort this output by elapsed time in descending order and figure out which sql statements are the culprit or poorly written.

Across application:
Use AWR
Use V$SQL* views:
V$SQLSTATS - For SQL related statistics
V$SYSSTAT - For System statistics
V$FILESTAT - For I/O

V$SQLSTATS.BUFFER_GETS - For CPU utilization
V$SQLSTATS.DISK_READS - For I/O statements
V$SQLSTATS.SORTS - For sorts

Once a high load sql statement is identified, prep steps:
Study and understand the structure of the underlying tables
Indexes - unique or non-unique
Latest statistics gathered on the tables being used or not

Tools to tune the queries:
ADDM - It looks at AWR for tuning
SQL Tuning Advisor
SQLAccess Advisor - For materialized views

Explain Plan Checks:
Driving table has the best filter condition - Driving table is determined ideally by the table that results in fetching of the fewest rows of all the tables