Oracle 10g : How to tune a sql statement in Oracle 10g ?

It's simple. Make sure that you have advisor previlege.

Execute the following on sqlplus :

-- Gather statistics for the schema or the table based on your query.

If you are dealing more than one table, gathering stats for the schema would be good.

exec dbms_stats.gather_schema_stats(ownname => 'scott',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE auto',cascade => true);

If you are dealing with a table, for eg : emp

execute dbms_stats.gather_table_stats(ownname => 'scott', tabname =>
'emp', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

set serveroutput on;
SET LONG 1000;
SET LONGCHUNKSIZE 1000;
SET LINESIZE 100;

DECLARE
ret_val VARCHAR2(4000);
SqlStr CLOB := '';
BEGIN
ret_val := dbms_sqltune.create_tuning_task(SqlStr);
dbms_output.put_line(ret_val);
END;
/

You will get a task id, eg : TASK_57561

exec dbms_sqltune.execute_tuning_task('TASK_57561');

Now see what the sql advisor says :

SELECT dbms_sqltune.report_tuning_task('TASK_57561') FROM dual;

After reading the recommendations from the sql advisor, you can drop the task if you want to :

exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_57561');

You can also use the QUICK_TUNE procedure to quickly analyze a single SQL statement:

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
sql_stmt := 'SELECT COUNT(*) FROM sales WHERE country =''US''';
task_name := 'MY_TASK';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name, sql_stmt);

Another good place to look at :
http://www.oracle.com/technology/oramag/oracle/08-mar/o28sqlperf.html

Long live "Tom Kyte".

Good Luck !!

Ravi Vedala.