Measuring the Pre-Change SQL Performance

Execute the SQL workload to create a pre-change SQL trial before making the system change. Executing a SQL workload runs each of the SQL statements contained in the workload to completion. During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload. Each SQL statement in the SQL tuning set is executed separately from other SQL statements, without preserving their initial order of execution or concurrency. This is done at least twice for each SQL statement, for as many times as possible until the execution times out (up to a maximum of 10 times). The first execution is used to warm the buffer cache. All subsequent executions are then used to calculate the run-time execution statistics for the SQL statement based on their averages. To avoid a potential impact to the database, DDLs are not supported; only the query portion of DMLs are executed.

Depending on its size, executing a SQL workload can be time and resource intensive. When executing a SQL workload, you can choose to generate execution plans only, without collecting execution statistics. This technique shortens the time to run the execution and lessens the effect on system resources, but a comprehensive performance analysis is not possible because only the execution plans will be available during the analysis.

Alternatively, you can execute a SQL workload remotely on a separate database using a database link. SQL Performance Analyzer will establish a connection to the remote database using the database link, execute the SQL statements on that database, collect the execution plans and run-time statistics for each SQL statement, and store the results in a SQL trial on the local database that can be used for later analysis. This method is useful in cases where you want to:

• Test a database upgrade

• Execute the SQL workload on a system running another version of Oracle Database

• Store the results from the SQL Performance Analyzer analysis on a separate test system

• Perform testing on multiple systems with different hardware configurations

• Use the newest features in SQL Performance Analyzer even if you are using an older version of Oracle Database on your production system

Once the SQL workload is executed, the resulting execution plans and run-time statistics are stored in a SQL trial.