11G

SQL Performance Analysis (overview)

User Rating:

/ 0

Details: Parent Category: Articles; Created on Monday, 22 March 2010 21:37; Last Updated on Monday, 12 March 2012 13:54; Published on Monday, 22 March 2010 21:37; Written by Guy Lambregts; Hits: 3213

SQL Performance Analysis : Overview

ARTICLE YET TO BE REVIEWED AND IMPROVED

The steps are

1. Creation of a SQL Tuning Set --- STS --- which is a set of sql statements as well as their processing context.

2. Usage of the SQL Performance Analyzer --- SPA --- to catch the processing context statistics.

3. Implementation of a configuration change, a database instance initialization parameter change, optimizer version change.

4. Optionally if the SQL Tuning Set is to be moved to another database a staging table needs to be created in the source database, populated with the STS, exported and imported into the target database.

5. After the staging table has been imported in the target database or after the change has been made in the source database the SQL Performance Analyzer --- SPA --- is to be used again to measure the changed processing context statistics.

6. The SQL Performance Analyzer --- SPA --- is used again for comparison between the 2 environments.