Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Параметры функции:

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL,
  attribute_list      IN   VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;


sqlset_name   The SQL tuning set name

basic_filter  The SQL predicate to filter the SQL from the cursor cache defined on attributes of the
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED


object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage  A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit  The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list  List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics


Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.



Примеры запросов:

SELECT *  FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE);
   


SELECT *  FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                                                                                           'parsing_schema_name <> ''SYS'''  ));
    

SELECT sql_id, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;


SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''bc688uy91wzka'''));


SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''bc688uy91wzka'''))
ORDER BY sql_id, plan_hash_value;


SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('module = ''MMON_SLAVE'''));


-- all statements that ran for at least five seconds   
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000'));

   
-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an SCOTT user
SELECT *
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''SCOTT'''));

       
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)        
SELECT *
FROM table(dbms_sqltune.select_cursor_cache(
'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN'));


-- Select the top 100 statements in the cursor cache ordering by elapsed_time.     
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100));
                                               

SELECT sql_id, 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;


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
));


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 > 1000000'))
ORDER BY sql_id;




-- Запрос к  CURSOR_CACHE
SELECT *
FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1  => NULL,
          ranking_measure2  => NULL,
          ranking_measure3  => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' ));


-- Удаляем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/


-- Создаем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/



-- Загружаем SQL statements в SQL Tuning Set из CURSOR_CACHE
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1  => NULL,
          ranking_measure2  => NULL,
          ranking_measure3  => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
  CLOSE ref_cursor;
END;
/


-- Просмотр STS:

SELECT * FROM  DBA_SQLSET_STATEMENTS;


-- Просмотр плана из STS:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => 'btd7m8k25qr9h',          
       plan_hash_value => 1081635106
       ));


-- Планы из CURSOR_CACHE можно посмотреть так:

-- находим sql_id:

select * from table (dbms_sqltune.select_cursor_cache('sql_text like ''select /*MY_CRITICAL_SQL*/%'''));
select * from table (dbms_sqltune.select_cursor_cache('sql_id = ''4n3pdustvb0yk'''));

 -- смотрим план:

select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'BASIC'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL -projection'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL +peeked_binds'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +alias -predicate'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +outline'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));


dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;


-- или через SQL monitor:

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;
/