Oracle 10g: Exporting SQL Profile(s) from One System to Another

Oracle 10g RDBMS has some useful features to automate SQL tuning up to some extent. The SQL Tuning Advisor of Oracle 10g helps us in optimizing poorly written/tuned SQL statements. SQL Tuning Advisor can be used from the command line SQL*Plus environment or from the web based Enterprise Manager. The optimizer runs in the 'tuning' mode under the SQL Tuning Advisor; collects the SQL profile data and tries to improve the performance of the SQL by creating new indexes, gathering {additional} statistics, etc., In case of significant improvements, the potential SQL profile can be accepted and saved in the database, so the optimizer can use the potential SQL profile while executing similar SQL statements in the 'normal' mode.

Sometimes it is desirable to use similar SQL profile(s) in environments running similar databases -- Testing and Production database environments, for example. As long as the SQL tuning advisor's recommendations are within the boundaries of the database system administrator, it is easy to manually replicate one system's behavior on another. However in some cases it is beyond the control of a DBA to replicate the database system behavior - for example, optimizer might suggest a completely different explain plan based on some internal heuristics. In such cases it is hard to replicate similar behavior by hand. Fortunately the Transportable SQL Profile feature of Oracle Database 10g makes it possible to export the SQL profiles from one system to another in just few steps.

Without further ado, let's have a look at the necessary steps with examples.

1. On the source system, create the staging table. The staging table will be used to store the contents of the SQL Profile.
   
   eg.,
   

   % sqlplus / as sysdba
   
   SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 11 01:47:39 2008
   Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
   
   SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => 'CS90_BATCH_PROFILE_STGTAB');
   
   PL/SQL procedure successfully completed.

   
   The name of the {staging} table is CS90_BATCH_PROFILE_STGTAB.
   
   
2. Load the contents of the SQL Profile into the staging table. To perform this step, you need to know the SQL profile name. When the profile is accepted, you can either specify a name of your choice for the profile, or accept the system generated name.
   
   

    SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01464eb777e6c001', 
           staging_table_name => 'CS90_BATCH_PROFILE_STGTAB');
   
   PL/SQL procedure successfully completed.

   
   In this example, the name of the SQL Profile being loaded is SYS_SQLPROF_01464eb777e6c001, a system generated name.
   
   Note #1:
   You can store as many SQL profiles as you wish in the staging table. The following example, loads another SQL profile called SYS_SQLPROF_01464eb83e5f8002, another system generated name.
   
   

    SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01464eb83e5f8002', 
           staging_table_name => 'CS90_BATCH_PROFILE_STGTAB');
   
   PL/SQL procedure successfully completed.

   
   
3. Export the staging table (CS90_BATCH_PROFILE_STGTAB in this example) to the destination system using Data Pump, exp utility or DB Links.
   

    % exp \'CS90/CS90 AS SYSDBA\' FILE=CS90BatchSQLprofiles.DMP TABLES=CS90_BATCH_PROFILE_STGTAB
   
   Export: Release 10.2.0.3.0 - Production on Fri Apr 11 02:02:12 2008
   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
   With the Partitioning, OLAP and Data Mining options
   Export done in UTF8 character set and UTF8 NCHAR character set
   server uses WE8ISO8859P15 character set (possible charset conversion)
   
   About to export specified tables via Conventional Path ...
   . . exporting table      CS90_BATCH_PROFILE_STGTAB          2 rows exported
   Export terminated successfully without warnings.

   
   Note #2:
   Since the string "AS SYSDBA" contains a blank, most operating systems require that entire string '<username>/<password> AS SYSDBA' be placed in quotes or marked as a literal by some method. Be aware that some operating systems also require that quotes on the command line be escaped as well.
   
   
4. On the destination system, import the staging table into the database.
   
   eg., Import the staging table, CS90_BATCH_PROFILE_STGTAB, into the destination database using Data Pump, imp utility or DB Links.
   
   

    % imp \'CS90/CS90 AS SYSDBA\' FILE=CS90BatchSQLprofiles.DMP TABLES=CS90_BATCH_PROFILE_STGTAB
   
   Import: Release 10.2.0.3.0 - Production on Fri Apr 11 02:06:57 2008
   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
   With the Partitioning, OLAP and Data Mining options
   
   Export file created by EXPORT:V10.02.01 via conventional path
   import done in UTF8 character set and UTF8 NCHAR character set
   import server uses WE8ISO8859P15 character set (possible charset conversion)
   . importing SYS's objects into SYS
   . importing SYS's objects into SYS
   IMP-00015: following statement failed because the object already exists:
    "CREATE TYPE "SQLPROF_ATTR" TIMESTAMP '2008-02-02:00:37:22' OID 'AE1A3645A6B"
    "D1155E0340800209420B8'      "
    "                                                                       AS V"
    "ARRAY(2000) of VARCHAR2(500)"
   . . importing table    "CS90_BATCH_PROFILE_STGTAB"          2 rows imported
   Import terminated successfully with warnings.

   
   
5. Download SQL Profile content from the staging table to create equivalent SQL profile(s) in the destination database.
   
   

    % sqlplus / as sysdba
   
   SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 11 02:08:02 2008
   Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
   
   Connected to:
   Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
   With the Partitioning, OLAP and Data Mining options
   
   SQL> desc CS90_BATCH_PROFILE_STGTAB
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    PROFILE_NAME                                       VARCHAR2(30)
    CATEGORY                                           VARCHAR2(30)
    SIGNATURE                                          NUMBER
    SQL_TEXT                                           CLOB
    DESCRIPTION                                        VARCHAR2(500)
    TYPE                                               VARCHAR2(9)
    STATUS                                             VARCHAR2(8)
    BOOLEAN_FLAGS                                      NUMBER
    ATTRIBUTES                                         SQLPROF_ATTR
    VERSION                                            NUMBER
    SPARE1                                             CLOB
    SPARE2                                             BLOB
   
   SQL> execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => FALSE, staging_table_name => 'CS90_BATCH_PROFILE_STGTAB');
   
   PL/SQL procedure successfully completed.

   
   [New:09/05/09]
   Note #3:
   By default the staging will be created in SYS schema. If you want it to be created in a different schema, specify the schema owner.
   
   

   SQL> execute DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF( replace => FALSE, staging_table_name => 'CS90_BATCH_PROFILE_STGTAB', staging_schema_owner  => 'CS90');
   
   PL/SQL procedure successfully completed.

   
   
6. Finally query the DBA_SQL_PROFILES table to make sure that the SQL profile(s) are successfully imported into the database.
   
   eg.,
   

    SQL> select NAME from DBA_SQL_PROFILES;
   
   NAME
   --------------------------------------------------------------------------------
   SYS_SQLPROF_01464eb83e5f8002
   SYS_SQLPROF_01464eb777e6c001
   ...
   ...

[New:09/05/09]
How to drop an SQL profile?

By using the DROP_SQL_PROFILE procedure in DBMS_SQLTUNE package.

eg.,

SQL> execute DBMS_SQLTUNE.drop_sql_profile (name => 'SYS_SQLPROF_01464eb83e5f8002', ignore => TRUE);

PL/SQL procedure successfully completed.

That is all there is in it. For further/detailed information around SQL Tuning Advisor and SQL profiles, check the following:

• Optimizing the Optimizer: Essential SQL Tuning Tips and Techniques. An Oracle White Paper
  
• Automatic SQL Tuning in Oracle Database 10g. Author: Tim. http://www.oracle-base.com

_____________
Technorati Tags:
Oracle | Database | SQL Tuning | Oracle Enterprise Manager