Mandalika's scratchpad [ Work blog @Oracle | Stock Market Notes | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17  04.17  06.17  07.17  08.17  09.17 


Sunday, May 18, 2008
 
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:

_____________
Technorati Tags:
| | |


Comments:
Very nice reference. I come back to it every now and then to check my synatax. Thanks!
 
thx very much..your doc was useful for one of my tasks..
 
Excellent !!
 
Post a Comment

Links to this post:

Create a Link



<< Home


2004-2017 

This page is powered by Blogger. Isn't yours?