DBMS SQLTUNE


Grant select on sys.V_$SQL to username; 
Grant select on sys.V_$SQL_BIND_CAPTURE to username; 
grant select on v_$sqlarea_plan_hash to username; 
grant advisor to username;


DECLARE 
 l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
 sql_id => '8kxfzzm3f8vk4', 
 scope => DBMS_SQLTUNE.scope_comprehensive, 
 time_limit => 300, 
 task_name => '8kxfzzm3f8vk4_tuning_task', 
 description => 'Tuning task for statement 8kxfzzm3f8vk4'); 
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created for specific a statement from the AWR. 
DECLARE 
 l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
 begin_snap => 361, 
 end_snap => 362, 
 sql_id => '75kgzyr8dba7f', 
 scope => DBMS_SQLTUNE.scope_comprehensive, 
 time_limit => 60, 
 task_name => '75kgzyr8dba7f_AWR_tuning_task', 
 description => 'Tuning task for statement 75kgzyr8dba7f in AWR.'); 
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

---drop_tuning_task
exec DBMS_SQLTUNE.drop_tuning_task(task_name   => '8kxfzzm3f8vk4_tuning_task');  

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '75kgzyr8dba7f_AWR_tuning_task'); 
 
SELECT task_name, status FROM dba_advisor_log WHERE task_name = '75kgzyr8dba7f_AWR_tuning_task'; 
 
TASK_NAME                                                              STATUS 
------------------------------                                       ----------- 
awucddb4gtz6y_tuning_task                                             COMPLETED
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: