How to manually submit a 10g SQL Tuning Advisor task

Purpose:

Use the following procedure to generate the 10g SQL Tuning Advisor reports and recommendations from the command line when  Database Control or Grid Control GUI  is not available.
CREATE A SQL TUNING TASK

 DECLARE
  my_task_name   VARCHAR2 (30);
  my_sqltext     CLOB;
 BEGIN
  my_sqltext :=  ‘INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,
            ASSIGNMENT_NUMBER, LOCATION_CODE, PAYROLL_ACTION_ID,PAY_DATE) (SELECT
            PAA.ASSIGNMENT_ID, PAA.FULL_NAME, PAA.ASSIGNMENT_NUMBER,
            PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_DATE FROM
             PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE PPS WHERE
            PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID = PPS.PERSON_ID AND
            PPS.ACTUAL_TERMINATION_DATE IS NULL)’;  >>>>> SQL TO BE TUNED GOES HERE
   my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
      bind_list     => sql_binds (anydata.convertnumber (9)),
          user_name     => ‘BWINT’,
         scope         => ‘COMPREHENSIVE’,
         time_limit    => 60,
          task_name     => ‘CHECK_PAYROLL_RUN’,
         description   => ‘Testing Slow Running Online Payslip Run’
      );
   END;
  /

PL/SQL procedure successfully completed.

EXECUTE THE TUNING TASK

 BEGIN
dbms_sqltune.execute_tuning_task (task_name =>   2  ‘CHECK_PAYROLL_RUN’);
 end;
 /

PL/SQL procedure successfully completed.

VERIFY THE STATUS OF THE TASK

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name =’CHECK_PAYROLL_RUN’;

STATUS
———–
COMPLETED

RUN THE SQL TUNING ADVISOR REPORT

SQL> set pagesize 120
SQL> set pagesize 5000
SQL> set linesize 120
SQL> set long 50000
SQL> set longchunksize 500000

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’) FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’)                           
—————————————————————————————————-
GENERAL INFORMATION SECTION                                                    
——————————————————————————-
Tuning Task Name                  : CHECK_PAYROLL_RUN                          
Tuning Task Owner                 : SYS                                        
Scope                             : COMPREHENSIVE                              
Time Limit(seconds)               : 60                                         
Completion Status                 : COMPLETED                                  
Started at                        : 05/09/2008 10:34:44                        
Completed at                      : 05/09/2008 10:35:54                        
Number of Index Findings          : 1                                          
Number of SQL Restructure Findings: 1                                          
Number of Errors                  : 1                                          
                                                                               
——————————————————————————-
Schema Name: BWINT                                                             
SQL ID     : 35mw20gycm0q5                                                     
SQL Text   : INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,  
                      ASSIGNMENT_NUMBER, LOCATION_CODE,                        
             PAYROLL_ACTION_ID,PAY_DATE) (SELECT                               
                      PAA.ASSIGNMENT_ID, PAA.FULL_NAME,                        
             PAA.ASSIGNMENT_NUMBER,                                            
                      PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_ 
             DATE FROM                                                         
                      PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE    
             PPS WHERE                                                         
                      PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID =          
             PPS.PERSON_ID AND                                                 
                      PPS.ACTUAL_TERMINATION_DATE IS NULL)                     
                                                                               
——————————————————————————-
FINDINGS SECTION (2 findings)                                                  
——————————————————————————-
                                                                               
1- Index Finding (see explain plans section below)                             
————————————————–                             
  The execution plan of this statement can be improved by creating one or more 
  indices.                                                                     
                                                                               
  Recommendation (estimated benefit: 100%)                                     
  —————————————-                                     
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0001 on HR.PER_ALL_ASSIGNMENTS_F(‘ASSIGNMENT_ID’)
    ;                                                                          
                                                                               
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0002 on HR.PER_ALL_PEOPLE_F(‘PERSON_ID’);        
                                                                               
  – Consider running the Access Advisor to improve the physical schema design  
    or creating the recommended index.                                         
    create index HR.IDX$$_04DE0003 on HR.PAY_PAYROLL_ACTIONS(‘DATE_EARNED’,’PAY
    ROLL_ID’);                                                                 
                                                                               
  Rationale                                                                    
  ———                                                                    
    Creating the recommended indices significantly improves the execution plan 
    of this statement. However, it might be preferable to run “Access Advisor” 
    using a representative SQL workload as opposed to a single statement. This 
    will allow to get comprehensive index recommendations which takes into     
    account index maintenance overhead and additional space consumption.       
                                                                               
2- Restructure SQL finding (see plan 1 in explain plans section)               
—————————————————————-               
  An expensive cartesian product operation was found at line ID 17 of the      
  execution plan.                                                              
                                                                               
  Recommendation                                                               
  ————–                                                               
  – Consider removing the disconnected table or view from this statement or    
    add a join condition which refers to it.                                   
                                                                               
  Rationale                                                                    
  ———                                                                    
    A cartesian product should be avoided whenever possible because it is an   
    expensive operation and might produce a large amount of data.