Home » RDBMS Server » Enterprise Manager » How do I create SQL Tuning Set?
How do I create SQL Tuning Set? [message #269980] Tue, 25 September 2007 05:11
Messages: 671
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear all!

I created one Report from Database run by ADDM.

And I found that some statements need to be tuned. There are many statements, and I manually executed DBMS_SQLTUNE for each statement.

These are steps:

1. Create task_name
  a_sql_tune_task_id  VARCHAR2(100);
  a_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 1436,
                          end_snap    => 1439,
                          sql_id      => '51wpmnhjhmaj7',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '51wpmnhjhmaj7_AWR_tuning_task',
                          description => 'Tuning task for statement 51wpmnhjhmaj7 in AWR.');
  DBMS_OUTPUT.put_line('a_sql_tune_task_id: ' || a_sql_tune_task_id);

a_sql_tune_task_id: 51wpmnhjhmaj7_AWR_tuning_task
*** SCRIPT END :  Session:SYS@NEOIBM(1)   25-Sep-2007 17:01:22 *** 

2. Executed tuning task
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '51wpmnhjhmaj7_AWR_tuning_task');
PL/SQL procedure successfully completed.

3. Checked if it completed
SQL> select task_name, status
  2  from dba_advisor_log
  3  where task_name like '5%';

TASK_NAME                      STATUS
------------------------------ -----------
51wpmnhjhmaj7_AWR_tuning_task  COMPLETED
5ctw1qj4wyd6b_tuning_task      COMPLETED

4.View Report
SQL> select dbms_sqltune.report_tuning_task('51wpmnhjhmaj7_AWR_tuning_task')
  3  from dual;


Tuning Task Name   : 51wpmnhjhmaj7_AWR_tuning_task
Tuning Task Owner  : SYS
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 09/25/2007 17:06:18
Completed at       : 09/25/2007 17:06:18

Schema Name: CCS_ADMIN
SQL ID     : 51wpmnhjhmaj7
SQL Text   : SELECT   ID, NAME, detail_file, picture_file, statusbar_text,
             menu_level, parent_id, NVL (window_prop, ' ') window_prop,
             window_width,  window_height, NVL (window_param, ' ')
             window_param FROM menu a     ,(select distinct menu_id
             from menu_access a              ,user_role   b         where
             b.role_id =a.role_id              and b.user_id='QBH'         )
             b WHERE style = 'top'   AND publish = 1   AND a.ID = b.men

But, you look at my report, there are many statement to tune. How do I create one SQL tuning set?
Please guide me.
Note: I execute all of packages from console.

Thank you very much!
  • Attachment: IBM1.snap
    (Size: 13.26KB, Downloaded 400 times)

[Updated on: Tue, 25 September 2007 05:12]

Report message to a moderator

Previous Topic: How to connect to the another SID by 10g E.M?
Next Topic: Notifications
Goto Forum:

Current Time: Tue Feb 28 06:29:38 CST 2017

Total time taken to generate the page: 0.07694 seconds