Databases Reference
In-Depth Information
SELECT VALUE(x)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',
NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;
--
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'HIGH_DISK_READS',
populate_cursor => cur);
END;
/
In the prior code, notice that the
SYS
user is bookended by sets of two single quotes (not double
quotes). The
SELECT_CURSOR_CACHE
function loads the SQL statements into a PL/SQL cursor, and the
LOAD_SQLSET
procedure populates the SQL tuning set with the SQL statements.
The
DBMS_SQLTUNE.SELECT_CURSOR_CACHE
function (see Table 9-4 for function parameter
descriptions) allows you to extract from memory SQL statements and associated statistics into a SQL
tuning set. The procedure allows you to filter SQL statements by various resource-consuming criteria,
such as
elapsed_time
,
cpu_time
,
buffer_gets
,
disk_reads
, and so on.
This allows you a great deal of
flexibility on how to filter and populate the SQL tuning set.
Populating SQL Tuning Set with All SQL in Memory
If your requirement is to perform a tuning analysis on all SQL statements currently in memory, use the
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET
procedure. This example creates a SQL tuning set named
PROD_WORKLOAD
and then populates by sampling memory for 3,600 seconds (waiting 20 seconds between
each polling event):
BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
--
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/
The
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET
procedure allows you to poll for queries and
memory and to use any queries found to populate a SQL tuning set. This is a powerful technique that
you can use when it's required to capture a sample set of all SQL statements executing.
You have a great deal of flexibility on instructing the
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET
to
capture SQL statements in memory (see Table 9-5 for details on all parameters). For example, you can
instruct the procedure to capture a cumulative set of statistics for each SQL statement by specifying a
CAPTURE_MODE
of
DBMS_SQLTUNE.MODE_ACCUMULATE_STATS
, like so:
