Databases Reference
In-Depth Information
BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/
This is more resource-intensive than the default settings, but it produces more accurate statistics for
each SQL statement.
Table 9-5. CAPTURE_CURSOR_CACHE_SQLSET Parameter Descriptions
Parameter
Description
Default Value
SQL tuning set name.
none
SQLSET_NAME
Total time in seconds to spend sampling.
1800
TIME_LIMIT
While sampling, amount of time to pause in seconds
before polling memory again.
300
REPEAT_INTERVAL
Either
INSERT
,
UPDATE
, or
MERGE
statements when new
statements are detected.
MERGE
CAPTURE_OPTION
When capture option is
UPDATE
or
MERGE
, either replace
statistics or accumulate statistics. Possible values are
MODE_REPLACE_OLD_STATS
or
MODE_ACCUMULATE_STATS.
CAPTURE_MODE
MODE_REPLACE_OLD_STATS
NULL
Filter type of statements captured.
BASIC_FILTER
SQL tuning set owner;
NULL
indicates the current user.
NULL
SQLSET_OWNER
Include (or not) recursive SQL; possible values are
HAS_RECURSIVE_SQL
,
NO_RECURSIVE_SQL
.
HAS_RECURSIVE_SQL
RECURSIVE_SQL
Displaying the Contents of a SQL Tuning Set
Once you've created a SQL tuning set, you may want to manually view its contents. For example, you
might want to know specifically which queries are in the set or might want to verify various metrics
associated with the queries. There are several methods for viewing SQL tuning set contents.
