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

-- Включаем трассировку
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'


-- Выполняем запросы
SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM all_objects WHERE object_type = 'SCHEDULE';


-- Выключаем трассировку
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';


-- Создаем  mapping таблицу
DROP TABLE map_tab;
CREATE TABLE map_tab AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
   FROM dba_objects
   WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
                             'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
                             'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
                             'LOB', 'OPERATOR', 'PACKAGE',
                             'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
                             'RESOURCE PLAN', 'TRIGGER', 'TYPE',
                             'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
   FROM dba_users;
  

-- Находим файл трассировки текущей сессии
SELECT name, value FROM v$diag_info WHERE   name ='Default Trace File';

--- /u01/app/oracle/diag/rdbms/testdb_p/testdb/trace/testdb_ora_3648.trc


-- Создаем объект directory
CREATE DIRECTORY SQL_TRACE_DIR as '/u01/app/oracle/diag/rdbms/testdb_p/testdb/trace';



-- Удаляем 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 из ранее созданного Trace File
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQL_TRACE(
           directory=>'SQL_TRACE_DIR',
           file_name=>'testdb_ora_3648.trc',
           mapping_table_name=>'map_tab')) 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;

-- Используя функцию select_sqlset
SELECT
  first_load_time,
  executions as execs,
  parsing_schema_name,
  elapsed_time  / 1000000 as elapsed_time_secs,
  cpu_time / 1000000 as cpu_time_secs,
  buffer_gets,
  disk_reads,
  direct_writes,
  rows_processed,
  fetches,
  optimizer_cost,
  sql_plan,
  plan_hash_value,
  sql_id,
  sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'my_sts'));

            
-- Обработка в курсорном цикле
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value (p)
    FROM table(dbms_sqltune.select_sqlset(sqlset_name => 'my_sts')) p;

  -- Process each statement (or pass cursor to load_sqlset)

  CLOSE cur;
END;
/

--Можно использовать функцию display_sqlset:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => '4xjfbbpfj40xc',            
       plan_hash_value => 4294967295
       ));



С помощью функции  SELECT_SQL_TRACE, можно непосредственно обращаться к файлу трассировки:


SELECT *
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2                 -- (1- only first execution,   2 - all executions)
             )) t
WHERE parsing_schema_name = 'SCOTT'
ORDER BY elapsed_time DESC;


SELECT sql_id,
          sum(elapsed_time) AS elapsed_time,
          sum(executions) AS executions,
          round(sum(elapsed_time)/sum(executions)) AS elapsed_time_per_execution
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
             )) t
WHERE parsing_schema_name = 'SCOTT'
GROUP BY sql_id
ORDER BY elapsed_time DESC;



SELECT plan_hash_value, executions, fetches, elapsed_time, cpu_time, disk_reads, buffer_gets, rows_processed
FROM table(dbms_sqltune.select_sql_trace(
                 directory => 'SQL_TRACE_DIR',
                 file_name => 'testdb_ora_3648.trc',
                 select_mode => 2
              )) t
WHERE sql_id = '4xjfbbpfj40xc'
ORDER BY elapsed_time DESC;


SELECT elapsed_time,
          value(b).gettypename() AS type,
          value(b).accessnumber() AS value
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
             )) t,
        table(bind_list) b
WHERE sql_id = '4xjfbbpfj40xc'
ORDER BY elapsed_time DESC;



Можно загрузить SQL statements в SQL Tuning Set так:

DECLARE
     cur sys_refcursor;
BEGIN
     dbms_sqltune.create_sqlset('my_sts');
     OPEN cur FOR
       SELECT value(p)
       FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
                 )) p;
     dbms_sqltune.load_sqlset('my_sts', cur);
     CLOSE cur;
END;
 /