Introduction   
   Application package software is a collection of software programs that is   
   developed for the purpose of being licensed to third-party organizations.   
   Although a package software may be tailored for a user's specific needs   
   through parameters or tables, the    
   software itself is not developed specifically for an organization. So, users   
   do not own the source code and have no way to modify the embedded SQL   
   statements for performance tuning purpose. There are a lot of application   
   packages built on Oracle RDBMS    
   such as Siebel, PeopleSoft JD Edwards, SAP and so on.  In order to help   
   application packages’ users, Oracle provides some features for helping users   
   to tune their SQL statements without the need to change their source code.   
      
   SQL profile   
   It is a profile generated by Oracle SQL Tuning Advisor. A SQL profile contains   
   corrections for wrongly estimated statistics, auxiliary information.   
   Therefore, SQL profile just guides the optimizer to a better plan, but they do   
   not guarantee the same plan    
   each time the statement is parsed. For certain SQL statements, no matter how   
   good the statistics are corrected, Oracle SQL optimizer is still not able to   
   generate a better plan in specific environments. For these kinds of SQL   
   statements, human    
   intervention is necessary, but SQL profile is not a convenient tool for   
   developers to force Oracle to pick up a new plan without changing the program   
   source code.   
      
   SQL plan baselines and stored outlines   
   Due to the Oracle environment changes or Oracle database version upgrade, it   
   might target Oracle SQL optimizer to generate new plans for certain SQL   
   statements. If it is not good, and we need something to preserve the old plans   
   for the new environment.     
   To achieve SQL plan stability, stored outlines was the major tool in earlier   
   releases of Oracle Database. This feature is still supported in Oracle   
   Database 11g; however, it might be depreciated in the future releases and   
   replaced by SQL plan management.    
   The mechanism of SQL Plan Baselines is to preserve the performance of   
   specified SQL statements, regardless of changes in the database environment or   
   release upgrade. Furthermore, create Plan Baselines manually for a SQL   
   statement is possible, and this    
   technique can help developers to guide Oracle SQL optimizer to generate a   
   specific plan for a bad performance SQL statement. So, when Oracle SQL   
   optimizer receive the same SQL statement next time, a better performance plan   
   will be composed according to    
   the new plan baselines stored in database. There is no need to change the SQL   
   syntax in source programs.   
   For example, if you want to tune a SQL with execution plan-A that is currently   
   used by Oracle SQL optimizer in your database, and you want to tune the SQL   
   with Hints to make Oracle SQL optimizer to generate a new execution plan-B.   
   What you have to do is    
   to execute the tuned SQL with new Hints and use the following method provided   
   by Oracle:   
      
      
      
      
      
   Execute the tuned SQL with Hints and plan B cached in SGA.   
      
   SET SERVEROUTPUT ON   
   DECLARE   
       My_Plan  PLS_INTEGER;   
   BEGIN   
        My_Plan := DBMS_SPM.load_plans_from_cursor_cache(   
                 sql_id                     => 'Plan-B SQL_ID',   
                 plan_hash_value => 'Plan-B plan_hash_value’,   
                 sql_handle            => 'Original SQL’s sql_handle');   
         DBMS_OUTPUT.put_line('Plan Loaded=> ' || My_plan);   
   END;   
      
   To enable the use of the tuned plan, manually alter the tuned plan to a fixed   
   plan by setting its FIXED attribute to YES.   
   To enable the use of SQL plan baselines, make sure the OPTIMIZER   
   USE_SQL_PLAN_BASELINES initialization parameter is set to TRUE.   
      
   Weaknesses of using SQL Plan baselines for SQL tuning   
   As the SQL plan baselines was designed to preserve the performance of SQL   
   statements such as after the following environment changes:   
   •	New optimizer version   
   •	Changes to optimizer statistics and optimizer parameters   
   •	Changes to schema and metadata definitions   
   •	Changes to system settings   
   •	SQL profile creating   
   You can see that it is not designed for the purpose of manual SQL tuning.   
   There are also some additional limitations such as Parallel Hints is not   
   supported by SQL Plan Baselines, you cannot load a Plan-B with Parallel Hints   
   applied your SQL with bad    
   performance of original Plan-A. Parallel Hints sometimes are very important   
   for a better plan generation by Oracle SQL optimizer.   
   SQL Patches   
   SQL Patches is part of the features provided by SQL Repair Advisor which is   
   used to fix a SQL statement’s critical failures such as returning wrong   
   result. The SQL Repair Advisor analyzes the problematic statement and in many   
   cases recommends a SQL    
   patch to repair the statement. The SQL patch is to influence the Oracle SQL   
   optimizer to choose an alternate execution plan for future executions, instead   
   of using the original problematic execution plan. There is a public API call   
   to create SQL patches    
   manually provided by Oracle Database 12c Release 2 onwards.  The   
   DBMS_SQLDIAG.CREATE_SQL_PATCH package can help users to create a SQL Patch for   
   specific SQL statement for SQL tuning purpose. You can change a bad   
   performance SQL statement’s execution    
   plan without the need to modify the program source code as the following   
   example:   
   DECLARE   
       Patch_name  VARCHAR2(32767);   
   BEGIN   
         Patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(   
                   sql_text  => 'SELECT *   
                                           FROM   employees   
                                         WHERE  emps_dept IN   
                                               (SELECT dpts_id   
                                                 FROM   departments   
                                               WHERE  dpts_avg_salary <200000)',   
                   hint_text => 'INDEX(@SEL$1 EMPLOYEES) INDEX(@SEL$2   
   DEPARTMENTS)',   
                   name       => 'my_sql_patch_name');   
   END;   
      
      
   [continued in next message]   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)