SQL Tuning Advisor - Expert Indexing in Oracle Database 11g - page 212

,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;
Here is some sample output:
SQL_ID SUBSTR(SQL_TEXT,1,20 DISK_READS CPU_TIME ELAPSED_TIME
------------- -------------------- ---------- ---------- ------------
0s6gq1c890p4s delete from "MVS"." 3325320 8756130000 1.0416E+10
b63h4skwvpshj BEGIN dbms_mview.ref 9496353 1.4864E+10 3.3006E+10
You have a great deal of flexibility in how you use this function (see Table 9-4 for a description of the
SELECT_CURSOR_CACHE function parameters). Here's an example that selects SQL in memory, but excludes
statements parsed by the SYS user and also returns statements with a total elapsed time greater than
100,000 seconds:
SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''
AND elapsed_time > 100000'))
ORDER BY sql_id;
In the prior query, the SYS keyword is enclosed by two single quotes (in other words, those aren't
double quotes around SYS ). The SQL_TEXT column is truncated to 20 characters so that the output can be
displayed on the page more easily. Here is some sample output:
SQL_ID SUBSTR(SQL_TEXT,1,20 DISK_READS CPU_TIME ELAPSED_TIME
------------- -------------------- ---------- ---------- ------------
byzwu34haqmh4 SELECT /* DS_SVC */ 0 140000 159828
This next example selects the top ten queries in memory in terms of CPU time for non- SYS users:
SELECT
sql_id
,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
,buffer_gets
,parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));