Инструменты по настройке базы данных Oracle: память, пространство и код SQL

Илья Дергунов

Илья Дергунов

Автор статьи. ИТ-специалист с 20 летним стажем, автор большого количества публикаций на профильную тематику (разработка ПО, администрирование, новостные заметки). Подробнее.

Производительность базы данных Oracle: память, SQL, местоOracle Database 11g и 12c включает несколько советников по управлению, которые снабжают автоматическими деталями различных подсистем базы данных. Эти советники представляют собой специализированные инструменты, помогающие в настройке производительности различных компонентов базы данных, идентификации узких мест и вычислении оптимальных размеров различных ресурсов базы. Например, Undo Advisor (Советник по отмене) сообщает оптимальный размер табличного пространства отмены, который должна иметь база данных. Каждый из этих советников базирует свои действия на специфическом пакете PL/SQL, таком как DBMS_ADVISOR.

Каждый раз, когда советник запускает задачу, он выполняет анализ и выдает рекомендации. Обратите внимание, что ADDM и Automatic Segment Advisor — единственные советники, запуск которых осуществляется автоматически через планировщик заданий. Чтобы получить рекомендации от любого из других советников, нужно вручную запланировать или запустить соответствующую задачу.

Каркас советников по управлению предлагает унифицированный интерфейс для всех советников Oracle. Некоторые из них появились еще в Oracle9i. Новым является сам общий каркас советников по управлению, облегчающий работу с ними. Этот новый каркас позволяет использовать похожие методы для запуска всех советников, и каждый советник выдает свои отчеты в некотором согласованном формате. Все советники получают необработанные данные из AWR и сохраняют результаты анализа в AWR.

Первичная функция каркаса советников состоит в том, чтобы помогать в повышении производительности базы данных. ADDM рекомендует использование советников по мере необходимости, всякий раз, когда проблема производительности требует углубленного анализа. Администраторы баз данных могут также применять советники для выполнения анализа типа “что если”.

 

Советники по управлению

Советники можно разделить на следующие группы: связанные с памятью, связанные с настройкой и связанные с пространством. Рассмотрим кратко советники, относящиеся к этим трем группам.

 

Советники памяти и экземпляра

Доступны два советника, связанные с памятью и экземпляром.

  • Memory Advisor (Советник памяти). Этот советник выдает рекомендации относительно оптимального определения размеров общего выделения памяти, а также памяти SGA и PGA. Диаграмма Allocation History (Хронология выделения) показывает хронологию выделения памяти различным компонентам SGA во времени.
  • MTTR Advisor (Советник MTTR). Этот советник позволяет конфигурировать восстановление экземпляра, позволяя настраивать среднее время восстановления (mean time to recover — MTTR) для экземпляра.

Совет. Очевидно, что если используется автоматическое управление разделяемой памятью и глобальной программной областью, для определения размеров этих компонентов памяти советник Memory Advisor не потребуется, поскольку база данных Oracle автоматически управляет ими сама.


 

Советники настройки

Разумеется, ADDM является наиболее важным советником по настройке в базе данных, который предоставляет доступ к автоматическим средствам диагностирования базы данных Oracle. Помимо ADDM есть еще два советника, ориентированных исключительно на настройку операторов SQL и увеличение их производительности.

  • SQL Tuning Advisor (Советник по настройке SQL). Этот советник анализирует сложные операторы SQL и рекомендует способы повышения производительности. SQL Tuning Advisor базирует всю свою работу на внутренней статистике и может включать рекомендации как по сбору новой статистики, так и по реструктуризации кода SQL. В Oracle Database 11g советник SQL Tuning Advisor запускается автоматически в период ежедневных окон обслуживания. Этот советник известен как Automatic SQL Tuning Advisor; он выбирает наиболее тяжело нагруженные операторы SQL и генерирует рекомендации по их настройке.
  • SQL Access Advisor (Советник по доступу SQL). Этот советник предлагает рекомендации в основном по созданию новых индексов, материализованных представлений и журналов материализованных представлений. Для получения рекомендаций его необходимо обеспечить репрезентативной рабочей нагрузкой. SQL Access Advisor уже обсуждался в наших блогах.

На заметку! Большинство предупреждающих сигналов в OEM также имеют ссылку на определенные советники по управлению. Таким образом, их можно вызывать непосредственно из самого такого сообщения.


 

Советники, связанные с пространством

В Oracle Database 11g и 12c предлагаются два советника, касающихся пространства.

  • Segment Advisor (Советник по сегментам). Этот советник позволяет выполнять анализ тенденций роста различных объектов базы данных. Он также помогает проводить сжатие объектов, высвобождая неиспользованное пространство в базе данных. Segment Advisor автоматически запускается во время окна обслуживания и рекомендует объекты-кандидаты на сжатие, а также объекты-кандидаты на реорганизацию из-за проблем вроде чрезмерной фрагментации строк.
  • Undo Advisor (Советник по отмене). Этот советник базирует свою деятельность на статистике использования системы, включая длительность выполнения запросов, а также степени генерации данных отмены. Undo Advisor содействует работе средства автоматического управления отменой (Automatic Undo Management — AUM). Он помогает корректно задавать размер табличного пространства отмены, а так-же интервал хранения данных отмены.

 

Управления каркасом советников

Всеми аспектами каркаса советников можно легко управлять через интерфейс Database Control. Для создания и управления задачами для каждого из советников по управлению также применяется пакет DBMS_ADVISOR.

 

Использование пакета DBMS_ADVISOR

Через интерфейс OEM можно вызвать любой из советников управления, используя различные мастера вроде SQL Access Advisor Wizard, и это — рекомендуемый способ использования любого советника. Однако бывают ситуации, когда понадобится вызывать советник программно, и в этом случае можно прибегнуть к услугам пакета DBMS_ADVISOR для управления модулями в каркасе советников. Методы для создания задания, изменения параметров задания, выполнения анализа и просмотра рекомендаций являются общими для всех советников.


На заметку! Пользователю понадобится выдать привилегию ADVISOR, чтобы он мог применять пакет DBMS_ADVISOR.


Ниже перечислены шаги, которые следует выполнить при использовании пакета DBMS_ADVISOR для управления различными советниками.

  1. Создать задачу.
  2. Установить параметры задачи.
  3. Сгенерировать рекомендации.
  4. Просмотреть рекомендации советника.

Ниже приведенные шаги обсуждаются более подробно.

 

Создание задачи

При использовании советника первым шагом является создание задачи. Задача (task) — это то, где советник хранит всю информацию, связанную с рекомендациями. Задача создается с помощью процедуры CREATE TASK, как показано ниже: 

SQL> VARIABLE task_id NUMBER;
SQL> VARIABLE task_name VARCHAR2(255);
SQL> EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

Как настроить память, пространство и операторы SQL на оптимальную производительность в базе данных Oracle 

Установка параметров задачи

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

SQL> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
'TEST_TASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');

В этом примере параметр VALID_TABLE_LIST относится к группе параметров фильтрации рабочей нагрузки. Вы инструктируете советник (в данном случае — SQL Access Advisor) исключить все таблицы из анализа кроме таблиц sales и customers, принадлежащих схеме SH.
В следующем примере с помощью параметра STORAGE_CHANCE из группы опции рекомендаций осуществляется добавление к рекомендациям пространства размером 100 Мбайт:

SQL> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('TEST_TASK',
'STORAGE_CHANGE', 100000000);

Совет. Представление V$ADVISOR_PROGRESS позволяет отслеживать прохождение задач советника по мере их выполнения.


 

Генерация рекомендаций

Чтобы сгенерировать набор рекомендаций любым советником, необходимо выполнить созданную ранее задачу с помощью процедуры EXECUTE_TASK из пакета DBMS_ADVISOR. Процедура EXECUTE_TASK сгенерирует рекомендации, состоящие из одного или более действий. Например, выполнение SQL Access Advisor может привести к выдаче рекомендации создать материализованное представление и журнал материализованного представления.
Вот как выполняется задача по имени TEST_TASK

SQL> EXECUTE DBMS_ADVISOR.EXECUTE_TASK('TEST_TASK');

 

Просмотр рекомендаций

Для просмотра рекомендаций, сформированных определенной задачей, служит процедура GET_TASK_REPORT. С помощью представления DBA_ADVISOR_RECOMMENDATIONS можно также проверить рекомендации, относящиеся к задаче с определенным именем: 

SQL> SELECT rec_id, rank, benefit
FROM DBA_ADVISOR_RECOMMENDATIONS
WHERE task_name = 'TEST_TASK';
    REC_ID        RANK    BENEFIT
----------  ----------  ---------
         1           2       2754
         2           3       1222
         3           1       5499
         4           4        594

В этом примере столбец RANK показывает расположение четырех рекомендаций друг относительно друга. Столбец BENEFIT отражает снижение стоимости в результате выполнения каждой из рекомендаций.

 

Использование OEM Database Control для управления Advisory Framework

Наилучший способ использования советников управления — через OEM Database Control. Все, что нужно — это щелкнуть на ссылке Advisor Central (Центр советников) на домашней странице Database Control. На странице Advisor Central, показанной на рисунке ниже, можно выбрать любой из советников по управлению в базе данных.

Advisor Central - центр советников в  OEM Database Control

Страница Advisor Central — ваша начальная точка в использовании каркаса советников через OEM Database Control или Grid Control. Для просмотра результатов запуска задачи советника используется раздел Advisor Tasks (Задачи советников). Таблица Results (Результаты) на главной странице Advisor Central показывает вывод последнего запуска советника.

После создания новой базы данных, впервые заглянув на эту страницу, вы увидите результат задачи ADDM. Это потому, что ADDM запускается автоматически сразу после создания базы данных. После того, как вы станете запускать другие советники, таблица Results будет собирать другие результаты.

 

Использование представлений словаря данных для управления каркасом советников

Несколько новых представлений словаря данных содержат информацию о задачах управления и рекомендациях, сгенерированных различными советниками. Ниже перечислены основные представления словаря данных, которые связаны с советниками.

  • DBA_ADVISOR_TASKS. Это представление показывает информацию обо всех задачах в базе данных, включая имя задачи, дату ее создания и частоту использования. Столбец ACTIVITY_COUNTER отражает, была ли выполнена задачей какая-то полезная работа. 
  • DBA_ADVISOR_PARAMETERS. Это представление показывает имена и значения всех параметров всех задач советников в базе данных.
  • DBA_ADVISOR_FINDINGS. Это представление показывает обнаружения, найденные каждым советником, включая уровень их влияния.
  • DBA_ADVISOR_RECOMENDATIONS. Это представление содержит анализ всех рекомендаций в базе данных. Также можно просматривать выигрыш от реализации каждой рекомендации и отсортировать все рекомендации в зависимости от выигрыша, который они дают.
  • DBA_ADVISOR_ACTIONS. Это представление показывает необходимые действия, ассоциированные с каждой рекомендацией советника.
  • DBA_ADVISOR_RATIONALE. Это представление показывает обоснования для всех рекомендаций советника.

Использование Segment Advisor


Запуск Segment Advisor автоматически планируется Oracle Scheduler на время окна обслуживания по умолчанию. Segment Advisor предоставит рекомендации относительно объектов, которые могут потребовать сжатия для высвобождения места, а также рекомендации о реорганизации объектов для исключения таких проблем, как чрезмерная фрагментация строк.

Детали рекомендаций Segment Advisor можно просмотреть через представление DBA_AUTO_SEGADV_CTL. С помощью встроенной функции ASA_RECOMMENDATIONS (из пакета DBMS_SPACE) легко найти сегменты, нуждающиеся в освобождении пространства, и сегменты с чрезмерной фрагментацией строк. Вот как она используется:

SELECT * FROM TABLE (DBMS_SPACE.ASA_RECOMMENDATIONS());

Просматривать рекомендации Segment Advisor можно также на странице Advisor Central в интерфейсе Database Control, щелкнув для этого на ссылке Segment Advisor Recommendations (Рекомендации советника по сегментам) в верхней части страницы. Внизу страницы Segment Advisor Recommendations щелкните на ссылке Recommendations from Last Run of the Automatic Segment Advisor Job (Рекомендации из последнего запуска задания Automatic Segment Advisor).


 

Вас заинтересует / Intresting for you:

Настройка памяти базы данных O...
Настройка памяти базы данных O... 10162 просмотров Stas Belkov Sat, 07 Jul 2018, 15:44:14
Создание БД Oracle 12c с макси...
Создание БД Oracle 12c с макси... 2947 просмотров Андрей Васенин Mon, 20 Aug 2018, 13:43:20
Мониторинг Oracle через метрик...
Мониторинг Oracle через метрик... 3328 просмотров sepia Tue, 21 Nov 2017, 13:18:05
Oracle ADDM - автоматическая д...
Oracle ADDM - автоматическая д... 4925 просмотров Алексей Вятский Tue, 21 Nov 2017, 13:18:05
Войдите чтобы комментировать

apv аватар
apv ответил в теме #8809 31 окт 2017 12:50
Постоянно юзаю memory adviser и sql tuner adviser для настройки памяти и SQL запросов. Весьма полезные инструменты!