Oracle Database 11g: Настройка производительности - Определение проблем в SQL

Oracle Database 11g Настройка производительности

Определение проблем в SQL выражениях

Причины неэффективного выполнения SQL выражений

SQL выражение может выполнятся неэффективно по следующим причинам:

• Устаревшая статистика оптимизатора: Планы выполнения SQL генерируются стоимостным оптимизатором. Для того чтобы оптимизатор генерировал и использовал наиболее эффективный план выполнения, он нуждается в точной информации о хранении и распределении данных в таблицах и индексах, которые участвуют в обработке SQL выражения.  
• Отсутсвие путей доступа к данным (access path): Отсутствие структур доступа к данным, таких как индексы, материализованные представления или партиций - частая причина падения производительности SQL выражений. Создание соответствующих методов доступа к данным на порядок увеличит производительность SQL выражений. 
• Выбор не оптимального плана выполнения: В отдельных случаях оптимизатор может выбрать не оптимальный план исполнения для SQL выражения. Причиной выбора не оптимального плана может быть неправильно оцененная стоимость, кардинальность или селективность для предиката выражения. 
• Неправильно построенный SQL: Если SQL выражение спроектировано неправильно, оптимизатор не сможет построить оптимальный план выполнения. Отсутствующее условия соединения, ведущее к декартову произведению данных, или использование наиболее ресурсоёмких операций таких как UNION вместо менее затратной UNION ALL - некоторые примеры написания неэффективных SQL выражений. 
• Дополнительно на производительность выполнения SQL могут влиять проблемы с аппаратным обеспечением, таким как память, ввод/вывод CPU и.т.д.

Примеры неэффективных SQL выражений

1. Запрос определяет какие продукты имеют стоимость по прайс листу менее чем на 15% выше средней стоимости продукта. В данное выражение встроен подзапрос, который выполняется для каждой строки, найденой в результате основного запроса. Этот запрос можно переписать следующим образом: SELECT COUNT(*) FROM products p, (SELECT prod_id, AVG(unit_cost) ac FROM costs GROUP BY prod_id) c WHERE p.prod_id = c.prod_id AND p.prod_list_price < 1.15 * c.ac
2. В данном запросе применяется функция к операции объединения, ограничивая тем самым возможность использования индекса для объединения. Для нормального выполнения запроса понадобится создать индекс основанный на функции. 
3. Запрос включает в условии неявное преобразование типов данных. 
4. Запрос использует функцию преобразования типов для приведения в соответствие типов данных обрабатываемых в предикате. Проблема заключается в том, что функция будет вызываться для каждой строки в таблице employees. Более оптимальным будет конвертировать условие предиката а не весь столбец: SELECT * FROM employees WHERE salary = TO_NUMBER(:sal)
5. В запросе используется оператор UNION вместо UNION ALL для того чтобы отбросить дублирующиеся строки в процессе выполнения запроса. Данная операция приведет к дополнительной сортировке для исключения не уникальных значений.

Решения Oracle Database для мониторинга производительности

Automatic Workload Repository (AWR): Собирает, обрабатывает и обслуживает статистику производительности для обнаружения проблем и задач самонастройки. Данные статистики хранятся в памяти и на диске в таблицах БД. собираемые данные могут быть просмотрены как при помощи представлений, так и в виде отчета за период.

Active Session History (ASH):  Предоставляет выборочную статистику по активности сессий в экземпляре. Активные сессии выбирающиеся каждую секунду хранятся в цикличном буфере в SGA. 

Snapshots: Набор исторических данных за определенный период времени, используемый для сравнения производительности разных временных промежутков инструментом ADDM.
Automatic Database Diagnostic Monitor (ADDM) 
В дополнение к классическим инструментам настройки производительности появившимся в предыдущих релизах,  таких как Statspack, трассировка SQL и представления производительности в Oracle Database 10g появились новые методы для мониторинга БД: 

• Проактивный мониторинг

1. Automatic Database Diagnostic Monitor (ADDM) автоматически выявляет узкие места в работе Oracle Database. Дополнительно, работая с другими компонентами управления, ADDM выдает рекомендации для устранения узких мест, используя доступные опции.
2. Oracle Database 11g в дальнейшем автоматизирует процесс настройки SQL, идентифицирует проблемные SQL выражения, запускает для них SQL Tuning Advisor и применяет профиль, сгенерированный в результате работы SQL advisor к SQL выражению, повышая тем самым скорость его выполнения без вмешательства пользователя. Для этого, при помощи среды AUTOTASK  создается пакетное задание для  автоматического исследования производительности (Automatic SQL tuning task). Задание Automatic SQL Tuning task запускается по умолчанию каждую ночь. 

• Реактивный мониторинг

1. Предупреждения генерируемые сервером: База данных Oracle может автоматически определять проблемные ситуации. В качестве реакции на обнаруженную проблему, СУБД Oracle отправляет предупреждение о ней с возможными действиями для её устранения
2. В качестве инструмента для реактивного мониторинга производительности БД также может использоваться Oracle Enterprise manager.

Инструменты мониторинга и настройки производительности Oracle Database 11g 

Automatic Database Diagnostic Monitor: Постоянно анализирует данные производительности, собираемые экземпляром БД.
SQL Tuning Advisor: Анализирует SQL выражения, определенные как проблемные, и пытается из настроить. По умолчанию задание на анализ производительности включено. Вы можете также вручную запустить SQL Tuning advisor для анализа производительности определенного SQL, или группы SQL используя SQL Tuning set.
SQL Tuning Sets : Служит репозиторием для хранения набора SQL выражений. Может использоваться SQL Tuning advisor для анализа производительности группы SQL выражений или например для переноса SQL из одной БД в другую для выявления потенциальных проблем с производительностью, которые могут возникнуть после миграции. 
SQL Access Advisor: Анализирует SQL выражение и выдает рекомендации по созданию материализованных представлений, индексов, логов материализованного представления или секционированию таблиц и индексов. 
SQL Monitoring: Позволяет в режиме реального времени наблюдать за процессом исполнения SQL и просматривать план и статистику его выполнения.
SQL Plan Management (SPM): Может быть использован для контроля и стабилизации планов исполнения SQL выражений при помощи создания опорных линий. При использовании опорной линии SQL будет использовать план исполнения с наилучшим временем выполнения, остальные планы, сгенерированные для этого SQL будут помечены как исторические, будут хранится в опорной линии и постоянно анализироваться. В случае нахождения более оптимального плана выполнения, этот план будет применен к SQL выражению, а предыдущий будет помещен в историю. 

SQL Tuning tasks
Многие задачи по настройке SQL должны выполняться на регулярной основе. Вы можете видеть как переписать условие WHERE, но также может потребоваться построить новые индексы для этого условия. Наличие списка задач предоставляет возможность заранее спланировать их выполнение и позволяет получить полное представление о зависимостях между ними.

• Определение высоко нагруженных SQL является одной из наиболее важных задач которые вы должны выполнять. ADDM - идеальный инструмент для выполнения данной задачи.
• По умолчанию СУБД Oracle собирает статистику оптимизатора автоматически. Для сбора статистики работает задание планировщика, которое запускается в maintenance window.
• Статистика операционной системы предоставляет информацию и производительности основных аппаратных компонентов также как и производительность системы в целом. 
• Часто перестройка индексов оказывает положительное влияние на производительность. Например удаление не селективных индексов для увеличения скорости обработки DML или добавление новых столбцов в индекс для увеличения его селективности.  
• Вы можете сохранить существующие планы выполнения SQL выражений используя сохранив статистику или создав опорную линию для этого SQL.

Наиболее часто на пользовательских системах встречаются следующие ошибки: 
Неправильно настроенное подключение: Приложение подключается и отключается при каждой операции взаимодействия с БД. Эта проблема часто встречается в случае работы с stateless сервером приложений. 
Некорректное использование курсоров и разделяемого пула: Не используются результаты хранимые в курсоре при повторном разборе. Если не используются BIND переменные, может выполнятся жесткий разбор при каждом выполнении одного SQL. В случае использования BIND переменных, открытые ранее курсоры могут использоваться повторно необходимое количество раз. Очень часто проблемы с версионностью и большим количеством жестких разборов выражения возникают в приложениях, генерирующих SQL динамически.
Некорректно спроектированный SQL: Плохой SQL - это SQL который использует больше ресурсов, чем уместно при его выполнении. Это может быть например DSS запрос, который выполняется более чем 24 часа или запрос к онлайн приложению, который выполняется более минуты. SQL который потребляет значительную часть системных ресурсов должен также быть обследован на предмет его потенциального улучшения. ADDM идентифицирует высоко нагруженные SQL и SQL Tuning advisor может быть использован в дальнейшем для увеличения его производительности.  
Использование нестандартных параметров инициализации: Установка некорректных значений параметров может быть выполнена на основании неправильного совета или решения. Большинство систем показывает приемлемую производительность с использованием стандартных параметров экземпляра. В частности, недокументированные параметры оптимизатора могут вызвать серьезные проблемы, которые потом тяжело будет определить и устранить. Также, параметры оптимизатора установленные в файле инициализации могут поменять существующие планы выполнения запросов. Исходя из этого схемы, статистика схем и настройки оптимизатора должны управляться вместе для обеспечения целостности конфигурации и производительности.  
Некорректная настройка ввода/вывода: Система хранения данных должна конфигурироваться из расчета доступности, а также пропускной способности, а не легкости хранения и максимизации размера разделов.
Проблемы с настройкой Redo логов: Часто создается недостаточное количество логов Redo. Маленький размер Redo лог файлов может привести к частым операциям checkpoint, что приведет в свою очередь к высокой нагрузке на буферный кэш и систему ввода/вывода. При избыточном количестве Redo логов не сможет выполняться архивирование и БД будет ждать выполнения процесса создания архивных логов. 
Избыточная сериализация ресурсов: Сериализация блоков данных в буферном кэше из за нехватки undo сегментов является частой проблемой для приложений с большим количеством активных пользователей и маленьким количеством undo сегментов. Использование автоматического управления сегментами (ASSM) и автоматического управления UNDO чаще всего решает данную проблему. 
Длительное полное сканирование таблиц: Большое количество продолжительных сканирований таблиц говорит о некорректном дизайне транзакций, возможном отсутствии индексов или не оптимизированном SQL. Операция full table scan вызывает большую продолжительную нагрузку на систему ввода/вывода.
Большое количество рекурсивного SQL: Большое количество рекурсивного SQL выполняемого SYS может обозначать активность, связанную с управлением пространством, например выделением экстентов. Для исключения рекурсивного SQL рекомендуется использовать табличные пространства с локальным управлением экстентами. В этом случае рекурсивный SQL выполняется от пользователей с разными ID и нагрузка распределяется более равномерно. 
Ошибки внедрения и миграции: В большинстве случаев приложение использует слишком много ресурсов, поскольку схема в которой хранятся таблицы необходимые объекты не была должным образом смигрирована из среды разработки или из более поздней версии схемы. Типичными ошибками в процессе миграции являются отсутствие индексов или неактуальная статистика. Данные ошибки являются причиной формирования неоптимальных планов выполнение и как следствие, причиной падения производительности. Когда мигрируется какой либо из разработанных или сущевствующих компонентов, для обеспечения стабильности планов исполнения, необходимо вместе с объектами переносить статистику при помощи пакета DBMS_STATS.

Фазы обработки SQL выражения

Хорошее понимание процесса обработки запроса - ключ к пониманию статистики SQL. Существует четыре основные фазы выполнения SQL выражения: parse, bind, execute, fetch. Процесс fetch выполняется только для выражений, возвращающих значение. 

Фаза Parse

Когда приложение вызывает выполнение SQL выражения, сначала серверу Oracle поступает запрос на выполнение разбора выражения (parse). В процессе выполнения Parse сервер Oracle:

• Проверяет выражение на правильность семантики и синтаксиса.
• Определяет имеются ли у вызывающего пользователя права на выполнение выражения. 
• Ищет совпадения выполняемого кода с уже разобранным кодом, хранящимся в библиотечном кеше.
• Выделяет частную SQL область для обработки выражения

Существует 2 вида разбора выражения: 

Мягкий разбор: Представленное SQL выражение имеется в shared pool. Оно может быть помещено туда в процессе его предыдущего выполнения. В этом случае используется уже разобранная версия выражения, что предотвращает его повторный разбор. 

Жесткий разбор: Представленное SQL выражение выполняется впервые и не имеет доступной копии разобранного кода в shared pool. Операция жесткого разбора наиболее ресурсоёмкая  поскольку в процессе разбора выполняются в полном обьеме все операции parse.

Для сокращения количества выполнения операций жесткого разбора рекомендуется использовать BIND-переменные, в этом случае код запроса после первого выполнения будет помещен в shared pool и повторно разбираться не будет.

Хранение курсоров SQL

Сервер Oracle использует библиотечный кэш и SQL области для хранения SQL выражений и PL/SQL блоков. Когда выражение хранится в кэше сервер Oracle: 

• Приводит выражение в числовой формат на основе таблицы символов ASCII
• Использует значение хэш функции этого числового выражения
• Помещает курсор для этого выражения в цепочку Хэш

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

Если выражение уже было разобрано и и выполнялось раньше, и дескриптор курсора все еще находится в клиентском кэше, курсор может быть вызван и выполнен без поиска выражения в shared pool.  Статистика количества разборов все еще увеличивается, поскольку запрос на разбор выполняется сервером, но повторное использование выражения, найденного в кэше сессии существенно сокращает накладные расходы на выполнение выражения.

Поведение сервера Oracle в отношении разделения курсоров регулируется параметром CURSOR_SHARING и технологией адаптивного разделения курсора, которая будет рассмотрена дальше в разделе "Настройка Shared Pool".

В идеальной ситуации, выражение должно разбираться один раз при его первом выполнении, в последующем должен выполняться мягкий разбор. Это зависит от количества доступного места в кэше сессии и Shared Pool, достаточного для хранения курсоров.

Использование курсоров и разбор

Каждый разработчик хочет чтобы его код работал настолько быстро насколько это возможно. Наиболее быстрым способом получить доступ курсора можно из кэша пользовательской сессии или Shared pool. Каждый открытый курсор в кэше открытых курсоров ссылается на область памяти SGA в которой хранится его дескриптор. Для выполнения курсора используется эта ссылка в SGA. Операция разбора при этом не требуется. Открытый курсор уже разобран и его дескриптор уже хранится в библиотечном кэше. 

Когда курсор закрывается, информация которая в нем хранилась перемещается в кэш закрытых курсоров сессии, если параметр SESSION_CACHED_CURSORS выставлен в не нулевое значение. 

Когда курсор открыт, сессия хеширует SQL выражение и выполняет поиск по хэшу в кэше закрытых курсоров. Если курсор найден, он перемещается в кэш открытых курсоров, затем используется ссылка на дескриптор курсора в shared pool, чтобы выполнить курсор. Разбор не требуется. 

Если курсор не найден в пользовательском кэше курсоров, тогда хэш значение используется для поиска дескриптора курсора по цепочки хэшей в shared pool. Операция поиска регистрируется экземпляром как разбор. Если дескриптор курсора найден в одной из цепочек хэш в shared pool и оставшаяся его часть не устарела,  курсор выполняется.

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

В отдельных случаях когда большое количество курсоров помещается в shared pool, возможна ситуация когда курсоры устаревают очень быстро до повторного обращения у ним. Эта ситуация ведет к большому количеству жестких разборов. 

Фаза BIND

В процессе выполнения фазы BIND выражение проверяется на наличие BIND переменных и в случае их присутствия, BIND переменным присваиваются соответствующие значения. 

BIND переменные оказывают влияние на производительность, когда: 

• Для сокращения операций разбора используется разделяемый курсор.
• Другой план исполнения может дать прирост производительности с другими значениями BIND переменных.

Когда в выражении используются BIND переменные, оптимизатор предполагает что распределение данных отличается от представления распределения данных, используемого оптимизатором по умолчанию. Поэтому многократные вызовы курсора с разными BIND переменными могут получать значительный прирост от различных планов исполнения. В этом случае adaptive cursor sharing будет генерировать новые планы выполнения. Если не будут пробоваться разные планы исполнения для разных наборов значений BIND переменных, производительность обработки этих выражений может со временем ухудшиться.

Если тип данных значения BIND переменной не соответствует типу данных столбца с которым она сравнивается, выполняется неявное преобразование, которое может привести к неэффективному использованию оптимизатором индексов для обрабатываемых в предикате столбцов. Используемые BIND переменными типы данных можно посмотреть в представлении DBA_HIST_SQLBIND.

Фаза Execute
Во время фазы Execute выражение выполняется по сгенерированному оптимизатором плану выполнения, также выполняются необходимые операции ввода/вывода и сортировки данных для DML выражений.
Планы выполнения - это последовательность шагов, которые серверный процесс использует для идентификации и доступа к необходимым строкам с данными, хранящимся в буферах данных или на диске. Несколько пользователей могут использовать один план выполнения. Сервер Oracle выполняет операции физических или логических чтений данных для DML выражений и если это необходимо, выполняет операции сортировки.
Физическое чтение - это чтение данных с диска. Логическое чтение - чтение из буферного кэша тех данных, которые туда были ранее помещены в процессе предыдущей обработки.

Фаза Fetch 
Oracle Database возвращает строки выражению SELECT в процессе фазы захвата данных (fetch). Каждая операция захвата данных в большенстве случаев возвращает множество строк, используя операцию множественного захвата (array fetch). Множественный захват может повысить производительность за счет сокращения количества обращений по сети. Каждый инструмент Oracle использует свое значение для размерности массива захвата данных. В SQL Plus например вы можете задать размер массива данных используя ключевое слово ARRAYSIZE.

SQL> show arraysize
arraysize 15
SQL> set arraysize 50

Обработка DML выражений  

DML выражения выполняются в две фазы: Parse в процессе которой разбирается выражение и фаза Execute, в которую для DML включены операции захвата данных и сортировки. 

Процесс выполнения DML выражения: 

1. Если данные и блоки отката для изменяемых в процессе выполнения DML данных еще не помещены в буферный кэш, серверный процесс считывает их с диска в буферный кэш. Серверный процесс при этом блокирует строки которые будут модифицированы. 
2. Серверный процесс затем применяет изменения данных запрашиваемые в DML к данным, прочитанным в буферный кэш и записывает данные в буфер отката. Изменения записываются в redo log buffer перед тем как данные будут изменены в буферном кэше и буферах отката. Эта технология применения изменений называется "упреждающее логирование" (write-ahead logging)
3. Буферы отката содержат значения данных перед модификацией. Буферы отката используются для хранения снимка данных до изменения и таким образом изменения внесенные DML операциями могут быть отменены в случае необходимости. В буферы данных записываются изменения данных. 
4. Пользователь получает ответ от операции DML (сколько строк изменено в процессе выполнения операции)

Все блоки данных, находящиеся в памяти и блоки отмены (хранящиеся в буферном кэше) которые были изменены в процессе выполнения DML операции помечаются как грязные буферы. Данные, хранящиеся в этих блоках отличаются от данных, хранящихся на диске. Эти измененные блоки не сразу записываются на диск процессом DB Writer (DBWR). Когда выполняется операция commit для транзакции, данные хранящиеся в буферах отката записываются в REDO журналы процессом Log Writer и грязные буферы в итоге записываются на диск процессом DBWR используя алгоритм checkpoint или при достижении порогового значения переполнения буферного кэша. DBWR не запишет данные на диск до тех пор пока процессом Log Writer не будет завершена запись журналов Redo.

Указанный выше порядок обработки применяется к операциям UPDATE, DELETE и INSERT. Снимок данных для DELETE содержит значения столбцов для удаленной строки, а снимок данных для INSERT содержит только информацию о местонахождении вставляемых строк. 

Пока не выполнен commit для транзакции содержатся только в памяти и не записываются на диск. В случае сбоя системы, вызвавшего очистку SGA до выполнения операции commit для DML приведет к потере вносимых изменений. 

Операция Commit

СУБД Oracle использует механизм fast commit, который гарантирует что примененные изменения могут быть восстановлены в случае сбоя экземпляра.

System change number

Когда происходит commit транзакции, БД Oracle присваивает транзакции уникальный системный номер изменения (SCN). Он используется базой данных Oracle в качестве внутренней метки времени для синхронизации данных и для обеспечения консистентности данных при чтении с диска.  SCN предоставляет возможность проводить проверку целостности данных вне зависимости от даты и времени установленной в операционной системе. 

Когда запускается COMMIT, выполняются следующие шаги: 

• Серверный процесс помещает запись о commit c SCN в redo log buffer.
• Фоновый процесс LGWR записывает данные повтора в redo log файлы. Это гарантирует, что в случае отказа экземпляра, данные об изменениях произведенных транзакцией не будут потеряны.
• Серверный процесс отправляет сообщение об окончании транзакции пользовательскому процессу.

DBWR в итоге записывает актуальные данные из грязных блоков на диск используя для этого свой внутренний временной механизм и настойки выполнения checkpoint.

Роль оптимизатора стоимости в выполнении SQL  

Оптимизатор стоимости - инструмент Oracle Database, задачей которого является генерация и выбор оптимального плана выполнения для SQL выражения. Определение плана выполнения является важным шагом в процессе обработки SQL и может существенно повлиять на время его обработки. 

План выполнения содержит в себе серию операций, которые выполняются последовательно для обработки SQL выражения. Оптимизатор рассматривает множество факторов связанных с объектами и условиями, указанными в QL выражении. 

Информация, требующаяся оптимизатору для построения плана включает в себя: 

• Статистику собранную для объектов системы и системную статистику (I/O, CPU и. т. д.)
• Информацию об объектах из словаря данных
• условия предиката WHERE 
• Подсказки (HINT) указанные разработчиком

В СУБД Oracle 11g оптимизатор имеет два названия в зависимости от используемой функциональности:  Оптимизатор запросов или run-time оптимизатор и automatic tuning optimizer (ATO). Значение ATO зависит от разделения доступа к курсорам. На разделение доступа к курсорам влияет использование литералов, значение параметра CURSOR_SHARING и гистограммы. 

Для всех SQL выражений, обрабатываемых сервером Oracle оптимизатор выполняет следующие операции: 

• Оценка регулярных выражений и условий: Сначала оптимизатор оценивает регулярные выражения и условия содержащие константы настолько полно, насколько это возможно. 
• Трансформация выражения: Для комплексных SQL выражений включающих в себя например вложенные подзапросы или представления, оптимизатор может выполнить трансформацию оригинального SQL в еквивалентную оперцию JOIN.
• Выбор подхода оптимизации: Оптимизатор определяет цели оптимизации. 
• Выбор путей доступа к данным (access path): Для каждой таблице, задействованной в SQL выражении оптимизатор выбирает один или более доступных путей доступа к данным для получения данных из таблиц. Оптимизатор пропускает пути доступа для использования которых отсутствует статистика, например оптимизатор может не использовать Bitmap index в случае отсутствия статистики для него. 
• Выбор порядка объединения: Для блока JOIN, если соединяется более двух таблиц, оптимизатор выбирает какая пара таблиц будет объединяться первой, затем какая из таблиц будет соединена с результатом предыдущего объединения и т.д.
• Выбор методов объединения:  Для любой операции JOIN оптимизатор выбирает вид операции JOIN, который будет использован для объединения объектов.