dbms_sqltune with example

First find out the sqlid from


select sid,serial#,sql_id from v$session where username='user name ';

or

find the top sql in particulate schema

SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
where PARSING_SCHEMA_NAME='ANUJ'
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/




First create the tuning task:


set serveroutput on
set long 10000 longchunksize 5000

DECLARE

my_task_name VARCHAR2(30);

BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'c6u1g74vugzxz',
scope => 'COMPREHENSIVE',
time_limit => 3000,
task_name => 'c6u1g74vugzxz_tune',
description => 'Task to tune a query');

END;
/

Execute the task

SYS AS SYSDBA>exec dbms_sqltune.execute_tuning_task('c6u1g74vugzxz_tune');

PL/SQL procedure successfully completed.



Now run the report for task


SYS AS SYSDBA>set long 20000

set longchunksize 20000

set linesize 400

select dbms_sqltune.report_tuning_task('c6u1g74vugzxz_tune') from dual;