SQL Tuning in Oracle 10G

/* Thanks to "Sridhar Chitupolu" for sharing this useful information */

Generally we will manually tune the SQL queries to optimize the time and cost.
Instead of manually tuning the query we can use $ORACLE_HOME/rdbms/admin/sqltrpt.sql
to tune the queries,it will provide us the suggestions and alternatives etc.. Using those suggestions we can optimize the query.

Note: It will work only in  Oracle 10G.

In TOAD, First run the query, which needs tuning.
Then follow the below steps:

1) First Get the Session Id From Session Browser in TOAD.
2) Get Hash Value :
 Query to get the Hash Value Based on SID (Session ID of particular Query)

select hash_value, sql_text
from v$sqltext
where hash_value in
        (select sql_hash_value from
                v$session where sid = &sid)
order by piece
/

3) To get the SQL ID Associated with the Hash Value

SQL> select sql_id from gv$sql where HASH_VALUE='1119157887';

To Check the cost,etc...

SQL> select plan_table_output from table(DBMS_XPLAN.display_cursor('f43ga151b9zmz'));

4)Path for Tuning SQL

-bash-3.00$ cd $ORACLE_HOME/rdbms/admin

SQL> @sqltrpt.sql <Input sql id.>

It will provide us the cost and time details and suggestions.