SQL PERFORMANCE ANALYZER
SQL Performance Analyzer compares the performance of SQL statements
before and after the change and produces a report identifying any
changes in execution plans or performance of the SQL statements.SQL
Performance Analyzer measures the impact of system changes both on the
overall response time of the SQL workload and of every individual SQL
statement in the workload.
Capturing the Workload
You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer. A SQL tuning set is a database object that includes one or more SQL statements, along with their execution statistics and execution context.SQL statements can be loaded into a SQL tuning set from different sources, including the cursor cache, Automatic Workload Repository (AWR), and existing SQL tuning sets.
The captured SQL statements should include the following information:
* SQL text
* Execution environment
* SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics
* Parsing schema under which a SQL statement can be compiled
* Compilation environment, including initialization parameters under which a SQL statement is executed
* Number of times a SQL statement was executed
Setting up the Test System
You should configure the test database environment to match the database environment of the production system as closely as possible. In this way, SQL Performance Analyzer can more accurately forecast the effect of the system change on SQL performance.
After the test system is properly configured, export the SQL tuning set from the production system to a staging table, then import it from the staging table into the test system.
Creating a SQL Performance Analyzer Task
To run SQL Performance Analyzer, you must first create a SQL Performance Analyzer task. A task is a container that encapsulates all of the data about a complete SQL Performance Analyzer analysis.When creating a SQL Performance Analyzer task, you will need to select a SQL tuning set as its input source.The SQL tuning set remains constant in the SQL Performance Analyzer task and is executed in isolation during each SQL trial. Thus, performance differences between trials are caused by environmental differences.
Measuring the Pre-Change SQL Performance
Executing a SQL workload runs each of the SQL statements contained in the workload to completion. Each SQL statement in the SQL tuning set is executed once—one at a time—separately from other SQL statements without preserving their initial order of execution or concurrency.During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload.
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 saving time and resource consumption.
Making a System Change
Make the change whose effect on SQL performance you intend to measure. SQL Performance Analyzer can analyze the effect of many types of system changes.
Measuring the Post-Change SQL Performance
After performing the system change, execute the SQL workload again to create post-change SQL trial. SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload a second time, resulting in a new set of performance data that can be used to compare to the pre-change version.
Comparing Performance Measurements
By default, SQL Performance Analyzer uses elapsed time as a metric for comparison. Alternatively, you can choose the metric for comparison from a variety of available SQL runtime statistics, including:
* CPU time
* Buffer gets
* Disk reads
* Disk writes
* Any combination of these metrics in the form of an expression
Fixing Regressed SQL Statements
If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis.
Capturing the Workload
You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer. A SQL tuning set is a database object that includes one or more SQL statements, along with their execution statistics and execution context.SQL statements can be loaded into a SQL tuning set from different sources, including the cursor cache, Automatic Workload Repository (AWR), and existing SQL tuning sets.
The captured SQL statements should include the following information:
* SQL text
* Execution environment
* SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics
* Parsing schema under which a SQL statement can be compiled
* Compilation environment, including initialization parameters under which a SQL statement is executed
* Number of times a SQL statement was executed
Setting up the Test System
You should configure the test database environment to match the database environment of the production system as closely as possible. In this way, SQL Performance Analyzer can more accurately forecast the effect of the system change on SQL performance.
After the test system is properly configured, export the SQL tuning set from the production system to a staging table, then import it from the staging table into the test system.
Creating a SQL Performance Analyzer Task
To run SQL Performance Analyzer, you must first create a SQL Performance Analyzer task. A task is a container that encapsulates all of the data about a complete SQL Performance Analyzer analysis.When creating a SQL Performance Analyzer task, you will need to select a SQL tuning set as its input source.The SQL tuning set remains constant in the SQL Performance Analyzer task and is executed in isolation during each SQL trial. Thus, performance differences between trials are caused by environmental differences.
Measuring the Pre-Change SQL Performance
Executing a SQL workload runs each of the SQL statements contained in the workload to completion. Each SQL statement in the SQL tuning set is executed once—one at a time—separately from other SQL statements without preserving their initial order of execution or concurrency.During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload.
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 saving time and resource consumption.
Making a System Change
Make the change whose effect on SQL performance you intend to measure. SQL Performance Analyzer can analyze the effect of many types of system changes.
Measuring the Post-Change SQL Performance
After performing the system change, execute the SQL workload again to create post-change SQL trial. SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload a second time, resulting in a new set of performance data that can be used to compare to the pre-change version.
Comparing Performance Measurements
By default, SQL Performance Analyzer uses elapsed time as a metric for comparison. Alternatively, you can choose the metric for comparison from a variety of available SQL runtime statistics, including:
* CPU time
* Buffer gets
* Disk reads
* Disk writes
* Any combination of these metrics in the form of an expression
Fixing Regressed SQL Statements
If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis.
No comments:
Post a Comment