...SQL Performance Analyzer in Oracle Database 11g Release 1 The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as: •Database, operating system, or hardware upgrades. •Database, operating system, or hardware configuration changes. •Database initialization parameter changes. •Schema changes, such as adding indexes or materialized views. •Refreshing optimizer statistics. •Creating or changing SQL profiles. Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics. The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods. •Setting Up the Test •Creating SQL Tuning Sets using the DBMS_SQLTUNE Package •Running the SQL Performance Analyzer using the DBMS_SQLPA Package •Creating SQL Tuning Sets using Enterprise Manager •Running the SQL Performance Analyzer using Enterprise Manager •Optimizer Upgrade Simulation •Parameter Change •Transferring SQL Tuning Sets Setting Up the Test The SQL performance...
Words: 1591 - Pages: 7
