Как Вам уже должно быть известно из предыдущих статей блога, материализованные представления очень полезны, но их создание и обслуживание — задача не тривиальная. Спроектировать оптимальное и наилучшее материализованное представление не так-то просто. Для определения того, какие нужно создать материализованные представления, журналы материализованных представлений, индексы и секционированные таблицы, можно с помощью SQL Access Advisor. Этот инструмент может также порекомендовать удаление определенных индексов для повышения производительности.
Для чего нужен SQL Access Advisor?
В дополнение к выдаче рекомендаций относительно создания новых материализованных представлений (а также индексов) и помощи в реализации этих рекомендаций, SQL Access Advisor также поможет оптимизировать материализованные представления, гарантируя переписывание запросов и делая материализованное представление быстро обновляемым.
Для определения идеальных материализованных представлений и индексов SQL Access Advisor может пользоваться одним из следующих источников SQL:
- гипотетическая рабочая нагрузка базы данных;
- действительная нагрузка, обеспеченная вами;
- кэш SQL.
Рабочую нагрузку можно также фильтровать в соответствии с критерием, например, иметь дело с запросами, которые содержат только определенную таблицу или таблицы.
Инструмент SQL Access Advisor можно применять вручную, вызывая определенные процедуры, относящиеся к пакету DBMS_ADVISOR. Вдобавок, можно воспользоваться “интеллектуальным” ярлыком, вызывая SQL Access Advisor через интерфейс OEM Database Control (или OEM Grid Control).
Получить быстрые рекомендации относительно отдельного оператора SQL легко с помощью процедуры QUICK_TUNE из DBMS_ADVISOR. В следующих разделах рассматриваются все три метода, начиная с простейшего — применения OEM Database Control.
Использование OEM Database Control
При вызове инструмента SQL Access Advisor из OEM Database Control он работает точно так же, как в случае обращения к нему напрямую через пакет DBMS_ADVISOR.Это объясняется тем, что внутренняя функциональность OEM полагается на пакет DBMS_ADVISOR. В качестве источника рабочей нагрузки SQL Access Advisor на вход можно передавать рабочую нагрузку, определенную пользователем, текущий и несколько последних SQL-операторов из кэша SQL или же репозиторий SQL.
Использование SQL Access Advisor через OEM приводит к созданию задач и просмотру рекомендаций с помощью интуитивно понятного мастера SQL Access Advisor. На нескольких страницах мастера вводятся операторы SQL, которые будут использовать материализованное представление. Вызов этого мастера осуществляется через ссылку Advisor Central на домашней странице Database Control (в разделе Related Links (Связанные ссылки), находящемся внизу страницы). Можно также обратиться к нему через ссылки, представленные на индивидуальных страницах сигналов (alerts) или производительности.
Совет. SQL Access Advisor может также работать в оценочном (evaluation) режиме. При этом он будет оценивать существующие индексы и материализованные представления, и сообщать о том, какие из них используются базой данных.
Для запуска SQL Access Advisor через Database Control выполните перечисленные ниже шаги.
- Очистите кэш SQL.
- Выдайте необходимые привилегии.
- Создайте кэш SQL.
- Получите рекомендации от SQL Access Advisor.
- Просмотрите эти рекомендации.
- Реализуйте их.
Создание кэша
Первый шаг предусматривает сброс разделяемого пула для очистки кэша от старых операторов SQL:
SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. SQL>
Выдача необходимых привилегий
Затем потребуется выдать пользователю привилегию ADVISOR, чтобы он мог обращаться к SQL Access Advisor:
SQL> GRANT ADVISOR TO sh; Grant succeeded. SQL>
Создание кэша SQL
Для предоставления нагрузка SQL можно воспользоваться одним из ранее упомянутых методов. В данном примере нагрузка создается тремя операторами SQL, которые становятся частью кэша SQL. Подключитесь как пользователь SH и запустите операторы SQL, показанные в листинге ниже.
SQL> SELECT c.cust_last_name, SUM(s.amount_sold),
SUM(s.quantity_sold)
FROM sales s, customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Texas','New Jersey')
SQL> SELECT c.cust_id, SUM(amount_sold)
FROM sales s, customers c
WHERE s.cust_id= c.cust_id GROUP BY c.cust_id;
SQL> SELECT SUM(unit_cost)
FROM costs
GROUP BY prod_id;
Совет. SQL Access Advisor может оказаться довольно прожорливым по части ресурсов, и отрицательно повлиять на производительность рабочей базы данных. Чтобы избежать этого, просто соберите необходимые данные, касающиеся рабочей нагрузки, с рабочей базы данных и применяйте их на тестовой базе данных для запуска шагов анализа и выдачи рекомендаций SQL Access Advisor.
Получение рекомендаций SQL Access Advisor
На предыдущем шаге была создана рабочая нагрузка SQL. Используя эту нагрузку, SQL Access Advisor сформулирует рекомендации относительно необходимых материализованных представлений. Зайдите в OEM Database Control с привилегиями SYSDBA и выполните следующие шаги.
1. Перейдите на домашнюю страницу OEM, щелкните на ссылке Advisor Central (Центр советников) в разделе Related Links (Связанные ссылки) и затем на SQL Access Advisor (Советник по доступу SQL).
2. Отобразится страница Initial Options (Начальные параметры). Здесь можно выбирать из двух вариантов.
- Параметры по умолчанию. Ваша задача будет использовать параметры, рекомендованные Oracle.
- Унаследованные параметры. Ваша задача унаследует параметры от выбранной задачи или шаблона.Для рассматриваемого примера выберите Use Default Options (Использовать параметры по умолчанию) и щелкните на Next (Далее).
3. Отобразится страница Workload Source (Источник рабочей нагрузки). На этой странице потребуется выбрать один из следующих вариантов нагрузки SQL.
- Current and Recent SQL Activity (Текущая и недавняя активность SQL)
- Import Workload from SQL Repository (Импорт рабочей нагрузки из репозитория SQL)
- Create a Hypothetical Workload from the Following Schemas and Tables (Создание гипотетической рабочей нагрузки из следующих схем и таблиц) Вы уже выполнили три оператора SQL, которые хотите использовать в качестве рабочей нагрузки, поэтому выберите опцию Current and Recent SQL Activity.
4. Щелкните на Filter Options (Параметры фильтра) для тонкой настройки контекста рабочей нагрузки SQL. Выберите в Filter Options вариант Filter Workload (Рабочая нагрузка). В разделе USERS (Пользователи) выберите вариант Only SQL Statements Executed by These Users (Только SQL-операторы, выполненные этими пользователями). В поле Users (Пользователи) введите SH.
5. Отобразится страница Recommendation Types (Рекомендованные типы) с двумя разделами: Recommendation Types (Рекомендованные типы) и Advisor Mode (Режим советника). В разделе Recommendation Types потребуется выбрать один вариант из следующих:
- Indexes (Индексы)
- Materialized Views (Материализованные представления)
- Both Indexes and Materialized Views (Индексы и материализованные представления)
- Partitioned Tables (Секционированные таблицы)
- Evaluation Only (Только оценка)
Поскольку наша цель — создание материализованных представлений, выберем второй вариант.
В разделе Advisor Mode выберите один из следующих двух режимов для SQL Access Advisor.
- Limited Mode (Ограниченный режим). Этот режим быстрее и обрабатывает только запросы высокой стоимости.
- Comprehensive Mode (Полный режим). Этот режим длится дольше, но выполняет тщательный анализ. Полный режим требователен к ресурсам, поэтому не стоит запускать его на рабочей базе посреди дня.
Выберите вариант Limited Mode.
6. Отобразится страница Schedule (График). Эта страница позволит запустить анализ немедленно или запланировать его на запуск позднее, в определенное время. В поле Task Name (Имя задания) в верхней части страницы можно ввести имя задания SQL Access Advisor. Перейдите вниз страницы и выберите Immediately (Немедленно) под опцией Start (Запуск). Щелкните на Next.
7. Далее появится страница Review (Обзор), где можно подтвердить выбор перед тем, как Advisor начнет работу.
8. Затем отображается страница Advisor Central (Центр советников) с сообщением об успешном создании задания SQL Access Advisor.
Просмотр рекомендаций Access Advisor
Как только SQL Access Advisor успешно завершит свою работу, вы сможете просмотреть рекомендации и решить, имеет ли смысл их реализовывать. Выполните перечисленные ниже шаги.
- На странице Advisor Central (см. пункт 8 в предыдущем разделе) перейдите к разделу Results (Результаты) в нижней части страницы и выберите имя вашей задачи. Щелкните на View Result (Просмотреть результаты).
- Появится страница Results for Task: номер задания (Результаты для задания: номер задания). Щелкните на Recomendation ID 1 (Идентификатор 1 рекомендации), чтобы увидеть детальные рекомендации.
- Измените имя схемы для создания материализованного представления (Schema Name for the Create Materialized View) на SH и щелкните на OK.
- На следующей странице щелкните на Show SQL (Просмотреть SQL) для просмотра сценария генерации материализованного представления и затем на OK.
Реализация рекомендаций SQL Access Advisor
Чтобы реализовать полученные рекомендации, выполните следующие шаги.
- Щелкните на Schedule Implementation (Реализация рекомендации) на странице Results for Task.
- Введите имя задания и щелкните на Submit (Отправить).
- Щелкните на View (Просмотреть), чтобы увидеть, запущено ли ваше задание.
- Просмотрите итоги, щелкните на Materialized View (Материализованное представление), введите SH в поле схемы и щелкните на Go (Выполнить).
Использование пакета DBMS_ADVISOR
Поскольку OEM Database Control предлагает настолько интуитивно понятный интерфейс для использования SQL Access Advisor с целью генерации рекомендаций относительно индексов и материализованных представлений, трудоемкие шаги, которые нужно будет выполнить для вызова SQL Access Advisor непосредственно через пакет DBMS_ADVISOR, слишком подробно обсуждаться не будут. Ниже дано краткое резюме того, что потребуется сделать при таком подходе.
- Запустите операторы SQL, которые понадобятся для вашего задания.
- Создайте задание с помощью процедуры CREATE_TASK.
- Создайте рабочую нагрузку с использованием процедуры CREATE_SQLWKLD.
- Свяжите задание с рабочей нагрузкой посредством процедуры ADD_SQLWKLD_REF.
- Воспользуйтесь соответствующей процедурой для применения либо гипотетической рабочей нагрузки, либо нагрузки SQL-кэша, либо набора настройки SQL.
- Установите параметры задания с помощью процедуры SET_TASK_PARAMETER.
- Сгенерируйте рекомендации с использованием процедуры EXECUTE_TASK, указав ей имя задания.
- Просмотрите рекомендации в представлении USER_ADVISOR_RECOMENDATIONS.
- Приведем запрос, использующий представление USER_ADVISOR_RECOMENDATIONS для просмотра рекомендаций SQL Access Advisor:
SQL> SELECT rec_id, action_id, SUBSTR(command,1,30) AS command FROM user_advisor_actions WHERE task_name = :task_name ORDER BY rec_id, action_id; REC_ID ACTION_ID COMMAND ----------- --------- -------------------------------- 1 5 CREATE MATERIALIZED VIEW LOG 1 8 ALTER MATERIALIZED VIEW LOG 1 9 CREATE MATERIALIZED VIEW LOG 1 19 CREATE INDEX SQL>
Использование процедуры QUICK_TUNE
Процедуру QUICK_TUNE из пакета DBMS_ADVISOR применяется, когда необходимо настроить единственный оператор SQL. Для этого нужно лишь указать имя задания и сам оператор SQL в качестве параметров процедуры. Вот пример:
VARIABLE task_name VARCHAR2(255); VARIABLE sql_stmt VARCHAR2(4000); EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers WHERE cust_state_province=''TX'''; EXECUTE :task_name := 'MY_QUICKTUNE_TASK'; EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, - :task_name, :sql_stmt);
Это даст тот же результат, что и выполнение шагов, перечисленных в разделе “Использование пакета DBMS_ADVISOR”.
