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), SQL trace files, 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
Oracle Database 2 Day + Performance Tuning Guide for information about creating SQL tuning sets using Oracle Enterprise Manager
Oracle Database SQL Tuning Guide for information about creating SQL tuning sets using APIs