Базы данных Oracle - статьи
За прошедшее десятилетие выявились две
За прошедшее десятилетие выявились две четкие тенденции:
- системы баз данных стали использоваться в новых областях с новыми требованиями, например, электронная торговля,
- приложения баз данных становятся все более и более сложными, обеспечивая работу очень большого числа одновременных пользователей.
- Определить SQL-предложения, имеющие наибольшую нагрузку (другими словами, топ-предложения), которые ответственны за большую часть работы приложений и потребление системных ресурсов, путем просмотра хронологии SQL-деятельности, доступной в системе (например, статистика кэша курсоров сохраняется в динамическом представлении V$SQL).
- Проверить, что планы выполнения, предложенные оптимизатором запросов для данных предложений, выполняются достаточно хорошо.
- Определить действия, необходимые для исправления плохо выполняемых SQL-предложений, для получения улучшенных планов.
- Накапливать и обновлять статистические данные, используемые оптимизатором запросов для построения планов выполнения. Например, создание гистограммы в столбце, содержащем асимметричные (skewed) данные.
- Изменить значение некоторого параметра конфигурации, который будет влиять на построение плана исполнений оптимизатором запросов. Например, применить значение optimizer_mode к first_rows_10.
- Переписать SQL-предложение для использования в соответствующей SQL конструкции. Например, когда возможно, заменить оператор UNION на UNION-ALL.
- Создать или удалить структуру доступа к данным таблицы. Например, создать индекс или материализованное представление.
- Добавить указания оптимизатора в предложение. Например, использовать указатель (hint) INDEX в таблице для изменения full table scan (полное сканирование таблицы) на index range scan (сканирование индексного диапазона).
Поэтому проблема производительности систем баз данных стала очевидной и, тем самым, весьма критичной для использования приложений в бизнесе.
Одна из важных частей настройки работы систем баз данных – это настройка SQL-предложений. Настройка SQL включает в себя три основных этапа:
Эти три этапа повторяются до тех пор, пока система не достигнет удовлетворительного уровня, или до тех пор, пока не останется предложений для настройки. Эксперты – АБД или разработчики приложений, которые имеют глубокие знания в области приложений и систем баз данных, обычно производят именно такую настройку.
Корректирующие действия могут включать один или несколько следующих шагов:
Процесс ручной настройки SQL ставит перед разработчиком несколько задач. Во-первых, он требует высококвалифицированной экспертной оценки в нескольких сложных областях: оптимизация запросов, разработка доступа, SQL-проектирование (design). Во-вторых, это трудоемкий процесс, потому как каждое предложение уникально и требует индивидуального решения, и, кроме того, число предложений может быть очень велико, например, больше тысячи. В-третьих, требуются глубокие знания структуры схемы (то есть, определение представлений, индексов, размеров таблиц, и т.д.) и модели используемых данных приложений. Наконец, SQL настройка является непрерывным процессом, потому что объем SQL-работы все время изменяется, например, когда используется модуль нового приложения. Далее, изменения в структуре доступа к данным (например, когда создается или удаляется индекс или материализованное представление), очень вероятно внесут изменения в планы выполнения, вынуждая разработчика приложений начать все заново. Рисунок 1 иллюстрирует ручной процесс настройки SQL.
Рисунок 1. Ручная настройка SQL
Для помощи администратору базы данных (АБД) и прикладному разработчику, столкнувшихся с этой проблемой, несколько компаний, выпускающих программное обеспечение, разработали инструменты диагностики и мониторинга, которые пытаются найти узкие места работы системы базы данных и предложить действия для их устранения. Некоторые из этих инструментов делают, в большинстве случаев, очень хорошую работу. Но, как правило, это инструменты не интегрированы с системным компонентом, на который они нацелены. Например, оптимизатор запросов – это компонент системы, ответственный за планы выполнения SQL-предложений. На самом же деле, оптимизатор запросов - это “черный ящик” (black box) для этих инструментов, и поэтому они должны интерпретировать информацию вне базы данных, чтобы осуществить настройку. Как следствие, результаты их настройки менее надежны и ограничены в области действия. Кроме того, недостаток интеграции приводит к тому, что внешние инструменты всегда отстают от последних обновлений и улучшений оптимизатора запросов.
В Oracle 10g большее внимание уделяется созданию самоуправляемых систем баз данных. Для осуществления автоматической настройки и мониторинга также как и в системе, работающей по требованию (on-demand), была введена опция, названная AWR (Automatic Workload Repository - автоматический репоиторий нагрузки). AWR каждые 30 минут (по умолчанию) просматривает данные производительности системы и постоянно (по умолчанию в течение 7 дней) хранит их, как историю системной рабочей нагрузки. Например, среди других функции AWR идентифицирует главные SQL-предложения, которые интенсивно расходуют ресурсы: использование процессора, чтение буферов, физические операции с дисками, вызовы синтаксического анализа (parse calls), использование разделяемой памяти и т.д. в каждом интервале времени. Другая управляющая опция ADDM (Automatic Database Diagnostics Monitor – автоматический монитор диагностики) была введена для автоматизации задачи непрерывного контроля действий системы, идентификации высокого уровня потребления системных ресурсов, и узкие места работы. В плане SQL-настройки ADDM определяет наиболнее нагруженные (high load) SQL-предложения. Была добавлена еще одна опции управления, которая автоматизирует сбор статистических данных в текущем интервале. Опция Automatic Statistic Collection доступна по умолчанию для недавно созданных баз данных Oracle 10g.
В Oracle 10g процесс SQL-настройки был автоматизирован путем ввода новой управляющей опции Automatic SQL Tuning. Она одинаково хорошо предназначена для работы с приложениями, как с OLTP, так и Data Warehouse. Automatic SQL Tuning базируется на недавно добавленной автоматической возможности настройки оптимизатора запросов, называемой Automatic Tuning Optimizer. Automatic SQL Tuning доступна посредством советчика (advisor), названного SQL Tuning Advisor. SQL Tuning Advisor берет одно или несколько SQL-предложений и продуцирует хорошо настроенные планы, основываясь на рекомендациях по настройке. SQL-предложения могли быть идентифицированы при помощи ADDM, AWR или вручную. Ручной процесс может содержать, например, тестирование набора SQL-предложений, которые должны быть еще использованы, для измерения индивидуальной производительности и идентификации тех предложений, которые имеют недостаточную производительность. Для обеспечения этого в Oracle 10g мы ввели новый объект настройки, названный SQL Tuning Set (STS), который подробно описывается в главе “”. Рисунок 2 дает высокоуровневое представление о том, как Automatic SQL Tuning работает в Oracle 10g.
Рисунок 2. Автоматическая настройка SQL
Остальная часть статьи организована следующим образом. Во-первых, мы объясним, как работает Automatic SQL Tuning, затем детально рассмотрим Automatic Tuning Optimizer. Далее, мы опишем SQL Tuning Set, который позволяет пользователю создавать и настраивать выбранные объемы работ SQL. Затем мы введем понятие первичного интерфейса Automatic SQL Tuning, использую для этого Enterprise Manager и иллюстрируя это примерами. Мы приведем описание пакета DBMS_SQLTUNE, который группирует процедуры SQL-настройки, используемые для SQL Tuning Advisor API, а также для управления SQL Tuning Set и SQL Profiles. В заключение статьи мы подведем итог, выполнив сравнение настроек в Oracle9i и Oracle 10g.
Процесс ручной настройки SQL ставит перед разработчиком несколько задач. Во-первых, он требует высококвалифицированной экспертной оценки в нескольких сложных областях: оптимизация запросов, разработка доступа, SQL-проектирование (design). Во-вторых, это трудоемкий процесс, потому как каждое предложение уникально и требует индивидуального решения, и, кроме того, число предложений может быть очень велико, например, больше тысячи. В-третьих, требуются глубокие знания структуры схемы (то есть, определение представлений, индексов, размеров таблиц, и т.д.) и модели используемых данных приложений. Наконец, SQL настройка является непрерывным процессом, потому что объем SQL-работы все время изменяется, например, когда используется модуль нового приложения. Далее, изменения в структуре доступа к данным (например, когда создается или удаляется индекс или материализованное представление), очень вероятно внесут изменения в планы выполнения, вынуждая разработчика приложений начать все заново. Рисунок 1 иллюстрирует ручной процесс настройки SQL.
Рисунок 1. Ручная настройка SQL
Для помощи администратору базы данных (АБД) и прикладному разработчику, столкнувшихся с этой проблемой, несколько компаний, выпускающих программное обеспечение, разработали инструменты диагностики и мониторинга, которые пытаются найти узкие места работы системы базы данных и предложить действия для их устранения. Некоторые из этих инструментов делают, в большинстве случаев, очень хорошую работу. Но, как правило, это инструменты не интегрированы с системным компонентом, на который они нацелены. Например, оптимизатор запросов – это компонент системы, ответственный за планы выполнения SQL-предложений. На самом же деле, оптимизатор запросов - это “черный ящик” (black box) для этих инструментов, и поэтому они должны интерпретировать информацию вне базы данных, чтобы осуществить настройку. Как следствие, результаты их настройки менее надежны и ограничены в области действия. Кроме того, недостаток интеграции приводит к тому, что внешние инструменты всегда отстают от последних обновлений и улучшений оптимизатора запросов.
В Oracle 10g большее внимание уделяется созданию самоуправляемых систем баз данных. Для осуществления автоматической настройки и мониторинга также как и в системе, работающей по требованию (on-demand), была введена опция, названная AWR (Automatic Workload Repository - автоматический репоиторий нагрузки). AWR каждые 30 минут (по умолчанию) просматривает данные производительности системы и постоянно (по умолчанию в течение 7 дней) хранит их, как историю системной рабочей нагрузки. Например, среди других функции AWR идентифицирует главные SQL-предложения, которые интенсивно расходуют ресурсы: использование процессора, чтение буферов, физические операции с дисками, вызовы синтаксического анализа (parse calls), использование разделяемой памяти и т.д. в каждом интервале времени. Другая управляющая опция ADDM (Automatic Database Diagnostics Monitor – автоматический монитор диагностики) была введена для автоматизации задачи непрерывного контроля действий системы, идентификации высокого уровня потребления системных ресурсов, и узкие места работы. В плане SQL-настройки ADDM определяет наиболнее нагруженные (high load) SQL-предложения. Была добавлена еще одна опции управления, которая автоматизирует сбор статистических данных в текущем интервале. Опция Automatic Statistic Collection доступна по умолчанию для недавно созданных баз данных Oracle 10g.
В Oracle 10g процесс SQL-настройки был автоматизирован путем ввода новой управляющей опции Automatic SQL Tuning. Она одинаково хорошо предназначена для работы с приложениями, как с OLTP, так и Data Warehouse. Automatic SQL Tuning базируется на недавно добавленной автоматической возможности настройки оптимизатора запросов, называемой Automatic Tuning Optimizer. Automatic SQL Tuning доступна посредством советчика (advisor), названного SQL Tuning Advisor. SQL Tuning Advisor берет одно или несколько SQL-предложений и продуцирует хорошо настроенные планы, основываясь на рекомендациях по настройке. SQL-предложения могли быть идентифицированы при помощи ADDM, AWR или вручную. Ручной процесс может содержать, например, тестирование набора SQL-предложений, которые должны быть еще использованы, для измерения индивидуальной производительности и идентификации тех предложений, которые имеют недостаточную производительность. Для обеспечения этого в Oracle 10g мы ввели новый объект настройки, названный SQL Tuning Set (STS), который подробно описывается в главе “”. Рисунок 2 дает высокоуровневое представление о том, как Automatic SQL Tuning работает в Oracle 10g.
Рисунок 2. Автоматическая настройка SQL
Остальная часть статьи организована следующим образом. Во-первых, мы объясним, как работает Automatic SQL Tuning, затем детально рассмотрим Automatic Tuning Optimizer. Далее, мы опишем SQL Tuning Set, который позволяет пользователю создавать и настраивать выбранные объемы работ SQL. Затем мы введем понятие первичного интерфейса Automatic SQL Tuning, использую для этого Enterprise Manager и иллюстрируя это примерами. Мы приведем описание пакета DBMS_SQLTUNE, который группирует процедуры SQL-настройки, используемые для SQL Tuning Advisor API, а также для управления SQL Tuning Set и SQL Profiles. В заключение статьи мы подведем итог, выполнив сравнение настроек в Oracle9i и Oracle 10g.
