1. Главная страница » Компьютеры » Oracle план запроса оптимизация

Oracle план запроса оптимизация

Автор: | 16.12.2019

Сразу уточню, что описывать буду на примере использования фри утилиты OraDeveloper Studio. Почему? Потому что обычными запросами этого сделать не удалось, а времени и желания разбираться не было, раз уж есть способ проще. 😉

Итак, для чего это вообще нужно? Опишу вам конкретный пример, из-за которого я и был вынужден проводить оптимизацию.

Задача — грузить в базу десятки тысяч строк данных. Для каждой строки необходимо предварительно по базе найти дополнительные данные одним довольно громоздким запросом (4 таблицы через джойны).
Проблема — загрузка 15 тысяч строк занимает 8-9 часов. Так как по условиям задачи загружать надо часто, а не один раз в пятилетку… В общем, надо довести время до приемлемого.

Что я сделал?
1. Выяснил, что тормозит именно селект (данные вставляются и обновляются в таблицах, где куча строк и часть из таблиц не имеет ни индексов, ни ключей — отсюда и сомнения в вине селекта).
2. Проверил наличие индексов на используемых запросом полях. Добавил отсутствующие.
3. Спросил помощи у знающих. 🙂

Знающие посоветовали проанализировать план выполнения запроса и объяснили, как это сделать в OraDev.
Создаём новое окно запроса (Ctrl+N). Копируем в него наш запрос. Жмём Alt+G. Выбираем уже существующую либо создаём новую таблицу плана.
После выполнения появится дерево плана выполнения. Самостоятельно и без поллитры в нём разобраться не так просто. 😉

Что же нас интересует в этом дереве? Нас интересуют узлы (шаги), для которых указан большой Cost шага. Цену шага вы можете увидеть в свойствах шага (у меня окошко свойств постоянно открыто и потому мне надо лишь выбрать нужный шаг; вам же может потребоваться выбирать свойства по правому клику на шаге). Отыскиваем медленный шаг (самый верхний узел, корень дерева плана, в расчёт особенно не берём — там будет указана общая цена запроса, а мы итак уже знаем, что проблема именно в этом запросе). Нашли? Теперь смотрим, с какой таблицей, какими её полями и с каким количеством строк работает шаг — это есть в свойствах и имени шага. Смотрим и думаем, почему у нас так медленно?
У меня, например, один из шагов работал с 4000 записей вместо одной-трёх записей (не тысяч). Такого быть не должно было в принципе — я же ограничиваю выборку именно для того, чтобы выбирать из нужного диапазона, а не из кучи лишнего барахла. Внимательно присмотревшись к условию джойна, я заметил, что упустил одно из полей. Добавил поле в запрос и всё встало на свои места. Цена запроса (полная) уменьшилась с 531 до 6. 🙂

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

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

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

Синтаксический анализ SQL-запросов

Этап синтаксического анализа (parsing) главным образом состоит в выполнении проверки синтаксиса и семантики SQL-операторов. В конце этого этапа создается дерево синтаксического разбора (parse tree), отражающее структуру запроса.

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

Читайте также:  Energenie eg pms2 lan

Оптимизация SQL запросов

На этапе оптимизации Oracle применяет свой оптимизатор, который называется оптимизатором по стоимости ( Cost-Base Optimizer — CBO), для выбора наилучшего метода доступа для извлечения данных из присутствующих в запросе таблиц и индексов. За счет использования предоставляемых статистических данных и любых указываемых в SQL-запросах подсказок, CBO генерирует для SQL-оператора оптимальный план выполнения.

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

Этап перезаписи запроса

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

Этап генерации плана выполнения

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

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

  • различные операции (например, операции соединения), которые подлежат выполнению во время запроса;
  • порядок, в котором должны выполняться эти операции;
  • алгоритм, который должен использоваться для выполнения каждой из них;
  • наилучший способ для извлечения данных с диска или из памяти;
  • наилучший способ для передачи данных во время запроса из одной операции другой.

Оптимизатор может генерировать несколько действительных физических планов запроса, которые являются потенциальными планами выполнения. Затем оптимизатор делает выбор между ними путем оценки стоимости каждого возможного физического плана на основании доступных ему статистических данных по таблицам и индексам и выбора того плана, подсчитанная стоимость которого оказывается наименьшей. Этот процесс оценки стоимости возможных физических планов запроса называется оптимизацией запроса по стоимости (cost-based optimization). Стоимость выполнения плана напрямую зависит от того, сколько ресурсов (ввода-вывода, памяти и ЦП) для него требуется. Потом оптимизатор передает выбранный самый низкий по стоимости физический план запроса механизму выполнения запросов Oracle. В следующем разделе рассматривается простой пример, чтобы можно было лучше разобраться в том, что собой представляет процесс оптимизации процесса по стоимости.

Пример оптимизации запроса по стоимости

Давайте предположим, что требуется выполнить показанный ниже запрос, предусматривающий поиск информации обо всех руководителях (supervisor), которые работают в Далласе (Dallas):

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

Для произведения вычислений по стоимости давайте исходить из следующих предположений:

  • считывать и записывать данные можно только по одной строке за раз (в реальности операции ввода-вывода выполняются обычно на уровне блоков, а не на уровне строк);
  • база данных записывает каждый промежуточный шаг на диск (опять-таки, в реальном мире такого может и не быть);
  • с таблицами не ассоциированы никакие индексы;
  • в таблице employee содержится 2000 строк;
  • в таблице dept содержится 40 строк и руководителей тоже 40 (по одному на каждое отделение);
  • в Далласе всего функционирует десять отделений.

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

Запрос 1: декартово соединение

В случае применения этого запроса сначала получается декартово произведение таблиц employee и dept, а затем проверяться, какие из строк в нем удовлетворяют требованию:

Общая стоимость выполнения этого запроса будет выглядеть так:

  • декартово произведение таблиц employee и dept потребует считывания обеих таблиц, т.е. 2000 + 40 = 2040 операций чтения;
  • создание декартова произведения — 2000 * 40 = 80000 операций записи;
  • считывание результата декартова произведения для его сравнения с условием выбора строк — 2000 * 40 = 80000 операций чтения;
  • итого общая стоимость ввода-вывода составит: 2040 + 80000 + 80000 = 162040.
Читайте также:  Delonghi форум по кофемашинам

Запрос 2: соединение двух таблиц

Второй запрос подразумевает выполнение соединения таблиц employee и dept. В случае применения этого запроса сначала будет осуществляться соединение таблиц employee и dept по столбцу dept_no, а затем выборка из результатов этого соединения всех строк, которые удовлетворяют условию:

Общая стоимость выполнения этого запроса будет выглядеть так:

  • соединение таблиц employee и dep сначала потребует считывания всех строк из обеих таблиц, т.е. 2000 + 40 = 2.040 операций чтения;
  • создание соединения таблиц employee и dep — 2000 операций записи;
  • считывание результатов соединения будет стоить 2000 операций чтения;
  • итого общая стоимость ввода-вывода составит: 2040 + 2000 + 2000 = 6040.

Запрос 3: соединение сокращенных связей

Третий запрос тоже подразумевает выполнение соединения таблиц employee и dept, но с соединением не всех, а только выборочных строк из этих двух таблиц. В случае его применения необходимые данные будут извлекаться так, как описано далее. Сначала будет осуществляться считывание таблицы employee для получения всех строк со значением SUPERVISOR. Затем будет выполняться считывание таблицы dept для извлечения всех строк со значением DALLAS. И, наконец, напоследок будет осуществляться соединение тех строк, которые были извлечены из таблиц employee и dept.

Общая стоимость выполнения этого запроса будет выглядеть так:

  • считывание таблицы employee для извлечения строк со значением SUPERVISOR будет стоить 2000 операций чтения;
  • запись строк со значением SUPERVISOR, которые были извлечены на предыдущем шаге — 40 операций записи;
  • считывание таблицы dept для извлечения всех строк со значением DALLAS — 40 операций чтения;
  • запись строк со значением DALLAS, извлеченных на предыдущем шаге — 10 операций записи;
  • соединение строк со значением SUPERVISOR и со значением DALLAS, извлеченных на предыдущих шагах выполнения данного запроса — всего 40 + 10 = 50 операций записи;
  • считывание результата соединения, полученного на предыдущем шаге — 50 операций чтения;
  • итого всего стоимость ввода-вывода составит: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Этот пример, каким бы простым он не был, показывает, что декартовы произведения обходятся дороже, чем соединения с более ограничивающими условиями. Даже выборочная операция соединения, как показывают результаты, обходится дороже, чем операция выбора. Хотя операция соединения в запросе 3 и представляет собой соединение двух сокращенных связей, размер соединения выглядит гораздо меньше, чем у соединения в запросе 2. Оптимизация запросов часто подразумевает выполнение ранних операций выборки (выбор только некоторых строк) и проекции (выбор только каких-то столбцов) для сокращения размера результирующего вывода или источников строк.

Эвристические стратегии для обработки запросов

Применение методики оптимизации по стоимости не является единственным способом выполнения оптимизации запросов. Для обработки запросов в базе данных могут также применяться и менее систематичные методики, известные как эвристические стратегии (heuristic strategies). Операция соединения является бинарной, а операция вроде выбора — унарной. Успешная стратегия в целом заключается в выполнении унарной операции на раннем этапе, чтобы в более сложных и длительных по времени бинарных операциях далее использовались меньшие операнды. Выполнение в первую очередь как можно большего количества унарных операций сокращает источники строк в операциях соединения. Ниже перечислены некоторые наиболее типичные эвристические стратегии по обработке запросов.

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

Выполнение запросов

На последнем этапе процесса обработки запросов осуществляется выполнение оптимизированного запроса (физического плана запроса, который был выбран). Если он представляет собой оператор SELECT, тогда производится возврат соответствующих строк пользователю, а если оператор INSERT, UPDATE или DELETE, тогда — внесение в строки соответствующих изменений. Исполняющий механизм SQL берет план выполнения, полученный на этапе оптимизации, и выполняет его.

Из трех этапов обработки оператора SQL этап оптимизации является самым важным, поскольку именно от него зависит, насколько быстро будут извлекаться необходимые данные. Понимание того, каким образом работает оптимизатор, играет ключевую роль в понимании процесса оптимизации. Для того чтобы писать эффективный SQL- код, важно знать, как выглядят типичные методы доступа, методы соединения и порядки соединения. Поэтому в следующем разделе приводится подробное описание применяемого в Oracle всемогущего оптимизатора CBO.

Выполнение запросов в Oracle. Общая схема и взаимодействие с клиентским приложением и машиной PL/SQL

Все SQL-запросы , поступающие в СУБД , обрабатываются примерно по одной схеме.

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

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

Читайте также:  Java open source проекты для новичков

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

На четвертом этапе по внутреннему представлению наиболее оптимального плана выполнения запроса формируется выполняемое представление плана.

Наконец, на пятом этапе обработки запроса происходит его реальное выполнение.

Оптимизатор. Его назначение. Этапы работы оптимизатора

Одним из основных преимуществ реляционных СУБД является механизм запросов на основе декларативного языка запросов SQL . При формулировании запроса пользователь указывает ЧТО он хочет получить а за то КАК это получить , отвечает СУБД . Поскольку существует потенциально очень большое множество способов выполнить конкретный запрос (комбинация способов и порядка соединения таблиц, путей доступа к данным и т.д.), появляется задача выбрать из всего множества способов выполнения запроса оптимальный . За эту задачу отвечает оптимизатор запросов .

Функция оптимизатора — выбрать наиболее оптимальный ( исходя из набора критериев) план выполнения запроса. При формировании оптимального плана , оптимизатор решает следующие задачи

  1. Вычисление выражений и операций
  2. Преобразование SQL операторов
  3. Выбор способа оптимизации — по стоимости или по правилам
  4. Выбор путей доступа
  5. Выбор порядка соединений таблиц
  6. Выбор метода соединений таблиц
  7. Определение наиболее эффективного плана выполнения

В Oracle реализовано два подхода к оптимизации запроса , отличающиеся в выборе критериев оптимизации .

  • Оптимизация по правилам ( RULE BASED ). Подход, при котором учитываются только способы доступа к данным , с зафиксированными приоритетами по эффективности доступа. Данный подход использовался в ранних версиях ORACLE и обладает существенным недостатком — он не учитывает реального распределения данных.
  • Оптимизация по стоимости (COST BASED ). Помимо эффективности различных путей доступа к данным , учитывается так же статистика по распределению данных и ресурсов операционной системы .

Режимы работы оптимизатора по стоимости. Установка режимов. Параметры, влияющие на работу оптимизатора

CBO управляется статистикой . Причем, когда он вычисляет стоимость , он учитывает количество блоков в таблице, количество строк, количество различных значений индексов и так далее. Чтобы он корректно работал, статистику надо регулярно собирать. Работа оптимизатора управляется параметром optimizer_mode , который может указываться на уровне сессии или на уровне экземпляра. Он может иметь следующие значения.

optimizer_mode = rule — RBO (был заморожен в версии 7), RBO, например, не умеет пользоваться Bitmap индексами.

optimizer_mode = all_rows — CBO , выбирает план выполнения с оптимальной стоимостью, режим работы оптимизатора по умолчанию.

optimizer_mode = first_rows — CBO , вычисляется стоимость разных планов выполнения , выбирает несколько планов с оптимальной стоимостью, по разным эвристическим соображениям пытается выбрать план, который наиболее быстро возвращает первые строки.

optimizer_mode = choose — Oracle сам выбирает, какой режим оптимизатора выбрать. Самый плохой случай: установлен данный режим и по каким-то таблицам есть статистика , а по каким-то нет. Пример, если хотя бы по одной из таблиц в запросе статистика есть, то в большинстве случаев будет использоваться all_rows , а если ни по одной из таблиц нет, то — rule . Если RBO не поддерживает интерфейс запроса (например, Bitmap -индексы), то используется CBO . CBO может оптимизировать запросы по таблицам, по которым не собрана статистика , используя умолчания для таблиц.

optimizer_mode = first_rows_1, first_rows_10, first_rows_1000 — при использовании first_rows Oracle вычисляет стоимость выполнения всего оператора , потом выбирает оптимальный план, при использовании first_rows_n вычисляет стоимость получения первых n строк, а стоимость выполнения всего оператора не вычисляется (данные режимы оптимальны для форм и первых операторов ).

Статистика. Назначение, способы формирования

Отсутствие статистики оптимизатора или устаревшие статистические данные часто являются причиной неоптимальной производительности обработки запросов. Оптимизатор, работающий на основе стоимости, использует для определения стоимости пути доступа такую статистику , как число элементов ( cardinality ) таблицы, число возможных значений столбца и распределение данных. Стоимость является мерой того, сколько памяти, ресурсов процессора и каналов ввода-вывода потребуется для выполнения запроса. Чтобы эффективно использовать оптимизатор, работающий на основе стоимости, нужно собрать статистику числа элементов ( cardinality ) и распределения данных для каждой таблицы, индекса и материализованного представления. Статистика собирается с помощью пакета DBMS_STATS . Статистические данные можно собирать либо путем чтения всех строк, либо путем проведения оценки на основе чтения только небольшой выборки строк или блоков. В пакете DBMS_STATS предлагаются процедуры для сбора статистики уровня базы данных , схемы или таблицы, а также раздела таблицы.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

code