Захват продуктивной SQL нагрузки

-- Создаем STS:

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
END;
/


--Захватываем SQL из CURSOR_CACHE в SQLSET:

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/


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

SELECT name, created, statement_count
FROM dba_sqlset;

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;

SELECT sql_id, elapsed_time
,cpu_time, buffer_gets
,disk_reads, sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('PROD_WORKLOAD'));


-- Выборочно удалаем ненужные SQL statements из STS:

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,basic_filter => 'disk_reads < 100000');
END;
/
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;



-- Создаем таблицу для загрузки SQL statements из STS:

BEGIN
dbms_sqltune.create_stgtab_sqlset(
table_name => 'STS_TABLE'
,schema_name => 'SCOTT');
END;
/
select * from STS_TABLE;


-- Загружаем в неё SQL statements из STS:

BEGIN
dbms_sqltune.pack_stgtab_sqlset(
sqlset_name => 'PROD_WORKLOAD'
,sqlset_owner => 'SCOTT'
,staging_table_name => 'STS_TABLE'
,staging_schema_owner => 'SCOTT');
END;
/
select * from STS_TABLE;

SELECT name, owner, created, statement_count
FROM dba_sqlset;


-- Переносим  таблицу  STS_TABLE в другую СУБД:

drop database link source_db;
create database link source_db
connect to scott
identified by tiger
using 'testdb';

create table STS_TABLE as select * from STS_TABLE@source_db;

-- Создать все STS из таблицы (с опцией replace):

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%'
,replace => TRUE
,staging_table_name => 'STS_TABLE'
,staging_schema_owner=> 'SCOTT');
END;
/

-- Проверяем, что STS создан:

SELECT name, owner, created, statement_count
FROM dba_sqlset;

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;