OSA (Optimizer Statistics Advisor)

-- статус OSA
select client_name, status from dba_autotask_client where client_name='auto optimizer stats collection';

-- выключение OSA
BEGIN
  dbms_auto_task_admin.disable(
    client_name =>  'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
  );
END;
/

-- включение OSA
BEGIN
  dbms_auto_task_admin.enable(
    client_name =>  'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
  );
END;
/

--=======================================================================
-- Управление периодом хранения данных, собираемых OSA.
--=======================================================================

-- определение периода хранения данных собранных OSA, по умолчанию это 30 дней
select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';

-- изменение периода хранения данных собранных OSA
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);

Важно! В Multitenant среде автоматическая чистка данных OSA на основе периода хранения EXECUTION_DAYS_TO_EXPIRE в подключаемых PDB базах не выполняется,
поэтому табличное пространство SYSAUX будет постоянно расти. Для чистки данных, необходимо переключится в контекст соответствующей PDB и запустить процесс
чистки вручную. Данные будут очищаться на основе заданного для данной базы значения EXECUTION_DAYS_TO_EXPIRE. Если чистка не выполнялась длительное время,
то чистка большого объема может создать существенную нагрузку и может выполняться очень долго, поэтому чистить лучше частями, а не все сразу. Для чистки частями
задайте сначала большой период хранения EXECUTION_DAYS_TO_EXPIRE и постепенно уменьшайте его перед каждой итерацией чистки.

-- Запуск чистки данных OSA вручную
exec prvt_advisor.delete_expired_tasks;

-- OSA хранит данные отдельно по каждой процедуре сбора данных.

-- список операций сбора данных, по которым данные еще не очищены
SELECT EXECUTION_NAME, EXECUTION_START, STATUS FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK' and EXECUTION_START < to_date('22.09.2020','dd.mm.yyyy')
ORDER BY 2;

-- количество неочищенных операций сбора данных
SELECT count(*) FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK' and EXECUTION_START < sysdate-365;

-- Как известно, удаление записей в таблице не уменьшает размер ее сегмента,
-- поэтому после основательной чистки для освобождения места в табличном пространстве SYSAUX,
-- необходимо выполнить следующее:

-- сжатие таблицы для обычной базы
alter table WRI$_ADV_OBJECTS move;
alter index WRI$_ADV_OBJECTS_PK rebuild;
alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

-- сжатие таблицы для контейнерной базы
-- alter table WRI$_ADV_OBJECTS move; 
-- ORA-65040: operation not allowed from within a pluggable database
-- Есть два способа решить эту проблему:
-- 1.
alter session set "_oracle_script"=true;
alter table WRI$_ADV_OBJECTS move;
alter index WRI$_ADV_OBJECTS_PK rebuild;
alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;
alter session set "_oracle_script"=false;

-- 2.
exec dbms_pdb.exec_as_oracle_script('alter table WRI$_ADV_OBJECTS move');
exec dbms_pdb.exec_as_oracle_script('alter index WRI$_ADV_OBJECTS_PK rebuild');
exec dbms_pdb.exec_as_oracle_script('alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;');
exec dbms_pdb.exec_as_oracle_script('alter index WRI$_ADV_OBJECTS_IDX_02 rebuild');

--===========================================================================================
-- быстрая ручная чистка всех данных задачи AUTO_STATS_ADVISOR_TASK
--===========================================================================================
-- определение количества записей созданных задачей AUTO_STATS_ADVISOR_TASK
SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

-- создание копии данных таблицы, не включающих данные задачи AUTO_STATS_ADVISOR_TASK
CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;

-- быстрый сброс всех данных таблицы
TRUNCATE TABLE WRI$_ADV_OBJECTS;

-- восстановление данных, не относящихся к задаче AUTO_STATS_ADVISOR_TASK
INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
COMMIT;

-- реиндексация, (как делается реиндексация для контейнерной базы см. выше)
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

-- если все ОК, то удаляем временную таблицу
DROP TABLE WRI$_ADV_OBJECTS_NEW;

--===========================================================================================
-- Восстановление задачи AUTO_STATS_ADVISOR_TASK
--===========================================================================================
-- данная процедура восстанавливает автоматические задачи OSA, если они были удалены
-- Если задачи находятся в статусе EXECUTING, то эта процедура сбрасывает их выполнение и переводит в статус CANCELED
EXEC DBMS_STATS.INIT_PACKAGE();


--===========================================================================================
-- Удаление задачи AUTO_STATS_ADVISOR_TASK
--===========================================================================================
-- при удалении задачи будет запущено удаление всех созданных ей записей
-- если количество записей слишком велико, то их необходимо предварительно почистить (см. выше)
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/