Databases Reference
In-Depth Information
Step 2: Determine Begin and End AWR Snapshot IDs
If you're unsure of the available snapshots in your database, you can run an AWR report or select the
SNAP_ID
from
DBA_HIST_SNAPSHOTS
.
select snap_id, begin_interval_time
from dba_hist_snapshot order by 1;
Step 3: Populate the SQL Tuning Set with High-Resource SQL Found in AWR
Now the SQL tuning set is populated with the top 15 SQL statements ordered by disk reads. The begin
and end AWR snapshot IDs are 29800 and 29802, respectively.
DECLARE
base_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN base_cur FOR
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
26800,26900, null, null,'disk_reads',
null, null, null, 15)) x;
--
dbms_sqltune.load_sqlset(
sqlset_name => 'MY_TUNING_SET',
populate_cursor => base_cur);
END;
/
This code populates the top 15 SQL statements contained in the AWR ordered by disk reads. The
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
function is used to populate a PL/SQL cursor with AWR
information based on a ranking criterion. Next, the
DBMS_SQLTUNE.LOAD_SQLSET
procedure is used to
populate the SQL tuning set using the cursor as input.
The
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
function can be used in a variety of ways to
populate a SQL tuning set using queries in the AWR. You can instruct it to load SQL statements by
criteria such as disk reads, elapsed time, CPU time, buffer gets, and so on. See Table 9-3 for descriptions
for parameters of this function.
Populating a SQL Tuning Set from High-Resource SQL in Memory
If you want to analyze a group of SQL statements currently in memory, use the
DBMS_SQLTUNE.SELECT_CURSOR_CACHE
function. The following example creates a tuning set named
HIGH_DISK_READS
and populates it with high-resource-consuming statements not belonging to the
SYS
schema and having more than 1,000,000 disk reads:
-- Create the tuning set
EXEC DBMS_SQLTUNE.CREATE_SQLSET('HIGH_DISK_READS');
-- populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
