Oracle 11g - Automatic SQL Tuning
 

  Automatic SQL Tuning :- During maintenance window , database automatic run SQL Tuning Advisor for high load sqls from   AWR (Automatic Workload Repository). Following step performe during execution

  1. Identified high load SQLs  from AWR
     
  2. Run automatic Advisory for sqls
     
  3. Check sql profile on sql
     
  4. If ACCEPT_SQL_PROFILES = true then implement the SQL profiles provided they meet the criteria of threefold performance improvement.

  We can check from dba_advisor_parameters, what is value set for ACCEPT_SQL_PROFILES

 

 

SQL> COLUMN parameter_value FORMAT A33
SQL>
SQL> SELECT parameter_name, parameter_value
  2 FROM   dba_advisor_parameters
  3  WHERE  task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  4  AND    parameter_name IN ('ACCEPT_SQL_PROFILES')

PARAMETER_NAME                 PARAMETER_VALUE

------------------------------               ------------------------------

ACCEPT_SQL_PROFILES            FALSE 

 

 

Enabling and Disabling Automatic SQL Tuning

If STATISTICS_LEVEL parameter is set to  BASIC , then AWR will not collect data that mean auto SQL Tuning will disable

 

SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

 

 

 

#To enable automatic SQL tuning, use the ENABLE procedure in the
#
DBMS_AUTO_TASK_ADMIN package:

 

BEGIN
 
DBMS_AUTO_TASK_ADMIN.ENABLE(
   
client_name => 'sql tuning advisor',
   
operation => NULL,
   
window_name => NULL);
END;
/

 

#To disable automatic SQL tuning, use the DISABLE procedure in the #DBMS_AUTO_TASK_ADMIN package:

 

BEGIN
 
DBMS_AUTO_TASK_ADMIN.DISABLE(
   
client_name => 'sql tuning advisor',
   
operation => NULL,
   
window_name => NULL);
END;
/

 

Configuring Automatic SQL Tuning If you want to accept profile automatically run , use DBMS_SQLTUNE. For example to enable automatic profile accept , run following command


BEGIN
 
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
   
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
   
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

 

Viewing Automatic SQL Tuning Reports 

 

 

set pagesize 0
set long 100000000
set longchunksize 1000
set linesize 180
set timing on trim spool on
select dbms_sqltune.report_auto_tuning_task(Null, Null, 'TEXT', 'ALL, WITH_TRACE') from dual; 

GENERAL INFORMATION SECTION                                                                                                  -------------------------------------------------------------------------------                                              
Tuning Task Name                 : SYS_AUTO_SQL_TUNING_TASK                                                          
Tuning Task Owner                  : SYS                                                                                
Tuning Task ID                          : 240303                                                                            
Workload Type                           : Automatic High-Load SQL Workload                                                   
Execution Count                         : 10                                                                                
Current Execution                       : EXEC_240303_2                                                                      
Execution Type                            : TUNE SQL                                                                           Scope                                            : COMPREHENSIVE                                                                      
Global Time Limit(seconds)     : 3600                                                                              
Per-SQL Time Limit(seconds)     : 1200                                                                               
Completion Status                       : INTERRUPTED                                                                       
Started at                                     : 07/13/2008 06:00:07                                                                 Completed at                               : 07/13/2008 07:00:10                                                               
Number of Candidate SQLs         : 235                                                                               
Cumulative Elapsed Time of SQL (s)      : 1918761                                                                           

-------------------------------------------------------------------------------                                             
Error: ORA-13639: The current operation was interrupted because it timed out.                                                -------------------------------------------------------------------------------                                              -------------------------------------------------------------------------------                                             
SUMMARY SECTION                                                                                                              -------------------------------------------------------------------------------                                             
                     
Global SQL Tuning Result Statistics                                                                    -------------------------------------------------------------------------------                                             

Number of SQLs Analyzed                                      : 37                                                                           

Number of SQLs in the Report                               : 37                                                                           

Number of SQLs with Findings                               : 11                                                                           

Number of SQLs with SQL profiles recommended : 3                                                                            

Number of SQLs with Index Findings                     : 5                                                                            

Number of SQLs with SQL Restructure Findings : 7                                                                            

Number of SQLs with Timeouts                                 : 1                                                                            

-------------------------------------------------------------------------------                                             
   
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID        -------------------------------------------------------------------------------                                             

object ID  SQL ID        statistics profile(benefit) index(benefit) restructure                                              ---------- ------------- ---------- ---------------- -------------- -----------                                             

     18877 2hzurm2fawknx                                     99.98%                                                         
    
18879 cnr9xrg31g4hj                                     99.98%                                                          
    
18882 9vmpy0a7qt1jg                                     99.30%           2                                             
    
18887 3x31j5nchnxy4                      95.57%   99.17%                                                          
    
18876 aqxxqyhu34b00                                    98.79%                                                         
    
18869 c7tkc4vrm22cq                      90.79%                          2                                              
    
18875 btp5hv774rqg6                      64.84%                         12                                             
    
18867 2xp7amuazgtma                                                      2                                              
    
18868 14w914xjqkv3z                                                      2                                             
    
18874 4kfhnt2vdrrjz                                                           2                                              
    
18886 c98y4sqk0zy2z                                                      2                                             

-------------------------------------------------------------------------------                                              
 
Tables with New Potential Indices (ordered by schema, number of times, table)                                               -------------------------------------------------------------------------------                                              
Schema Name                 Table Name                  Index Name     Nb Time       

…..