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 :
Long live "Tom Kyte".
Good Luck !!
Ravi Vedala.
