Capturing the SQL Workload

Before running SQL Performance Analyzer, capture a set of SQL statements on the production system that represents the SQL workload which you intend to analyze.

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

Capturing a SQL workload has a negligible performance impact on your production system and should not affect throughput. A SQL workload that contains more SQL statements will better represent the state of the application or database. This will enable SQL Performance Analyzer to more accurately forecast the potential impact of system changes on the SQL workload. Therefore, you should capture as many SQL statements as possible. Ideally, you should capture all SQL statements that are either called by the application or are running on the database.

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. Capturing a SQL workload using a SQL tuning set enables you to:

• Store the SQL text and any necessary auxiliary information in a single, persistent database object

• Populate, update, delete, and select captured SQL statements in the SQL tuning set

• Load and merge content from various data sources, such as the Automatic Workload Repository (AWR) or the cursor cache

• Export the SQL tuning set from the system where the SQL workload is captured and import it into another system

• Reuse the SQL workload as an input source for other advisors, such as the SQL Tuning Advisor and the SQL Access Advisor