OSA (Optimizer Statistics Advisor)

В Oracle Database 12c появился новый инструмент, помощник по оптимизации статистики OSA (Optimizer Statistics Advisor).

В заданные интервалы обслуживания системы выполняются автоматические задания связанные с OSA и собирают данные по которым OSA строит свои рекомендации.

Включение/выключение OSA

-- статус 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;
/

Проблема с PDB

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

Чистка данных OSA

-- 1. сначала определите дату самой старой операции сбора данных
SELECT EXECUTION_NAME, EXECUTION_START, STATUS FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK'
ORDER BY EXECUTION_START;

-- 2. определите дату и статус последней операции сбора данных
SELECT EXECUTION_NAME, EXECUTION_START, STATUS FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'AUTO_STATS_ADVISOR_TASK' and EXECUTION_START < sysdate-30
ORDER BY EXECUTION_START DESC;

-- 3. если статус последней операции "EXECUTING", а дата старше нескольких дней, то необходимо переинициализировать задачу
-- повторно проверьте статус последней задачи, он должет стать "CANCELED"
BEGIN
  DBMS_STATS.INIT_PACKAGE();
END;
/

-- 4. задайте период хранения соответствующий самой старой операции сбора данных
-- выполняйте чистку, уменьшая период хранения по 1 дню до желаемого значения
-- контролируйте объем архив-логов и UNDO, чистка 30 дней за раз может создать
-- более 80 ГиБ архив-логов и сегмент отката более 50 ГиБ
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10000);
  prvt_advisor.delete_expired_tasks;
  commit;
END;
/

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

-- определение текущего периода хранения
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';

-- задание периода хранения
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 30);
  commit;
END;
/

-- чистка данных старше текущего периода хранения (ОСТОРОЖНО!!! не выполняйте без проверок, см. выше "Чистка данных OSA")
BEGIN
  prvt_advisor.delete_expired_tasks;
  commit;
END;
/

-- список операций сбора OSA
SELECT * FROM DBA_ADVISOR_EXECUTIONS WHERE TASK_NAME='AUTO_STATS_ADVISOR_TASK';

(НЕ РЕКОМЕНДУЕТСЯ) быстрая ручная чистка всех данных задачи 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 PURGE;

Удаление/Восстановление задачи OSA

-- Удаление задачи AUTO_STATS_ADVISOR_TASK
-- (ОСТОРОЖНО!!!) при удалении задачи будет запущено удаление всех созданных ей записей
-- если количество записей слишком велико, то их необходимо предварительно почистить частями (см. "Чистка данных OSA")
BEGIN
  DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');
END;
/

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