You can use SQL Performance Analyzer to test the impact on SQL response time of a database upgrade from Oracle Database 10g Release 2 or a newer release to any later release by capturing a SQL tuning set on the production system, then executing it twice remotely over a database link on a test system—first to create a pre-change SQL trial, then again to create a post-change SQL trial.
Before the database upgrade can be tested, ensure that the following conditions are met:
The production system which you are upgrading from is running Oracle Database 10g Release 2 or a newer release.
Initially, the test system should also be running the same release of Oracle Database.
The test system must contain an exact copy of the production data found on the production system.
The hardware configuration must be as similar to the production system as possible.
You will also need to set up a separate SQL Performance Analyzer system running Oracle Database 11g Release 2. You will be using this system to run SQL Performance Analyzer. Neither your production data or schema need to be available on this system, since the SQL tuning set will be built using statistics stored in the SQL trace files from the production system. SQL Performance Analyzer tasks will be executed remotely on the test system to generate the execution plan and statistics for the SQL trial over a database link that you specify. The database link must be a public database link that connects to a user with the EXECUTE
privilege for the DBMS_SQLPA
package and the ADVISOR
privilege on the test system. You should also drop any existing PLAN_TABLE
from the user's schema on the test system.
Once the upgrade environment is configured as described, perform the steps as described in the following procedure to use SQL Performance Analyzer in a database upgrade from Oracle Database 10g Release 2 or a newer release to any later release.