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.

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

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.

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.