That’s Me

Gavin Soorma
Oracle Certified Professional
7.3, 8i, 9i,10g, 11g, 12c
11i Apps DBA OCP
10g RAC OCE
Certified GoldenGate Implementation Specialist
Oracle 11g Exadata Implementation Specialist
10g OCM
11g OCM

Feedback

5737480 hits

Thanks A MILLION for your support!

Please send me your valuable feedback and suggestions

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.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>