After specifying options for the SQL tuning set, select the load method to use for collecting and loading SQL statements into the SQL tuning set, as described in the following sections:
■ Loading Active SQL Statements Incrementally from the Cursor Cache ■ Loading SQL Statements from the Cursor Cache
■ Loading SQL Statements from AWR Snapshots
■ Loading SQL Statements from Preserved Snapshot Sets ■ Loading SQL Statements from a User-Defined Workload
Loading Active SQL Statements Incrementally from the Cursor Cache You can load active SQL statements from the cursor cache into the SQL tuning set incrementally over a
specified period of time. This allows you to not only collect current and recent SQL statements stored in the SQL cache, but also SQL statements that will run during the specified time period in the future.
To load active SQL statements incrementally from the cursor cache:
1. On the Create SQL Tuning Set: Load Methods page, select Incrementally capture active SQL statements over a period of time from the cursor cache.
2. In the Duration field, specify how long active SQL statements will be captured.
3. In the Frequency field, specify how often active SQL statements will be captured
during the specified duration. 4. Click Next.
The Create SQL Tuning Set: Filter Options page appears.
5. Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options" on page 10-9.
Loading SQL Statements from the Cursor Cache You can load SQL statements from the cursor cache into the SQL tuning set. However, because only current and recent SQL statements are stored in the SQL cache, collecting these SQL statements only once may result in a SQL tuning set this is not representative of the entire workload on your database.
To load SQL statements from the cursor cache:
1. On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
2. In the Data Source field, select Cursor Cache.
3. Click Next.
The Create SQL Tuning Set: Filter Options page appears.
Tip: Before selecting the load method for the SQL tuning set, you need to create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options" on page 10-5
Managing SQL Tuning Sets
4. Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options" on page 10-9.
Loading SQL Statements from AWR Snapshots You can load SQL statements captured in AWR snapshots. This is useful when you want to collect SQL statements for specific snapshot periods of interest that can be used for later comparison or tuning purposes. To load SQL statements from AWR snapshots:
1. On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
2. In the Data Source field, select AWR Snapshots.
3. In the AWR Snapshots field, select the snapshots to include. Do one of the
following:
■ Select Last 24 hours.
Only snapshots that are captured and stored in the AWR in the last 24 hours will be included.
■ Select Last 7 days.
Only snapshots that are captured and stored in the AWR in the last 7 days will be included.
■ Select Last 31 days.
Only snapshots that are captured and stored in the AWR in the last 31 days will be included.
■ Select ALL.
All snapshots that are captured and stored in the AWR will be included. 4. Click Next.
The Create SQL Tuning Set: Filter Options page is shown.
5. Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options" on page 10-9.
Loading SQL Statements from Preserved Snapshot Sets You can load SQL statements captured in preserved snapshot sets. This is useful when you want to collect SQL statements that are representative of a time period during known performance levels that can be used for later comparison or tuning purposes.
To load SQL statements from preserved snapshot sets:
1. On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
2. In the Data Source field, select Preserved Snapshot Sets.
Managing SQL Tuning Sets
Tuning SQL Statements 10-9 4. Click Next.
The Create SQL Tuning Set: Filter Options page is shown.
5. Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options" on page 10-9.
Loading SQL Statements from a User-Defined Workload You can load SQL statements by importing from a table or view. This is useful if the workload you want to analyze is not currently running on the database, or captured in an existing AWR snapshot or a preserved snapshot set.
There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the format of the table must match the USER_WORKLOAD table.
To load SQL statements from a user-defined workload:
1. On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
2. In the Data Source field, select User-Defined Workload.
3. In the User-Defined Workload field, select the table or view to include.
4. Click Next.
The Create SQL Tuning Set: Filter Options page is shown.
5. Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options" on page 10-9.