–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
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;
/
–Query the data from view
SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = ‘HIGH_DISK_READS’;
Recent Posts
