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.
- 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 isCS90_BATCH_PROFILE_STGTAB. - 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 isSYS_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 calledSYS_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. - Export the staging table (
CS90_BATCH_PROFILE_STGTABin 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. - 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. - 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. - Finally query the
DBA_SQL_PROFILEStable 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
0 comments:
Post a Comment