Когда пользователь начинает операцию по извлечению данных, SQL-оператор этого пользователя проходит несколько последовательных этапов, которые все вместе называются обработкой запроса. Одно из главных преимуществ языка SQL состоит в том, что он не является процедурным, и потому в нем не нужно перечислять шаги, которые должны выполняться для достижения поставленной перед оператором цели. Другими словами, в SQL не нужно описывать, как что-то должно быть сделано, вместо этого в нем достаточно описывать только то, что требуется получить от базы данных.
Под обработкой запроса подразумевается преобразование SQL-оператора в эффективный план выполнения для возврата запрашиваемых данных из базы. Под оптимизацией запроса понимается процесс выбора наиболее эффективного плана выполнения для достижения результата с наименьшими затратами в плане потребления ресурсов, наподобие ресурсов подсистемы ввода-вывода и ЦП, на том сервере, где работает база данных, а также сокращения общего времени выполнения запроса, представляющего собой просто сумму показателей времени выполнения всех входящих в состав данного запроса операций. Такая оптимизация производительности может выглядеть не так, как сведение к минимуму времени отклика. При необходимости свести к минимуму время, затрачиваемое на извлечение первых n строк, а не всего вывода запроса, оптимизатор может выбирать другой план, а при необходимости свести к минимуму время отклика для всех данных запроса, может также выбираться параллельный режим выполнения операции.
В общем, каждый выполняемый пользователем SQL-оператор проходит этап синтаксического анализа, этап оптимизации и этап выполнения. Если SQL-оператор представляет собой запрос, он подразумевает извлечение данных и потому в таком случае перед завершением процесса обработки еще дополнительно проходит и этап выборки. В следующих подразделах более подробно рассказывается о том, что Oracle делает во время каждого из этих этапов.
Синтаксический анализ SQL-запросов
Этап синтаксического анализа (parsing) главным образом состоит в выполнении проверки синтаксиса и семантики SQL-операторов. В конце этого этапа создается дерево синтаксического разбора (parse tree), отражающее структуру запроса.
В частности, во время этого этапа SQL-оператор преобразуется в запрос реляционной алгебры, который подвергается анализу для выяснения того, является ли он корректным с синтаксической точки зрения. Далее этот запрос подвергается проверке на предмет корректности с семантической точки зрения, во время которой с помощью словаря данных проверяется, чтобы все упоминаемые в запросе таблицы и отдельные столбцы, равно как и все необходимые объектные привилегии, действительно существовали. Вдобавок проверяются типы столбцов для получения уверенности в том, что данные соответствуют определениям столбцов. Потом оператор нормализуется для того, чтобы его можно было обработать более эффективным образом. В случае если запрос сформулирован неправильно, он отклоняется. После того, как дерево синтаксического разбора проходит все синтаксические и семантические проверки, оно признается действительным и отправляется на этап генерации логического плана запроса. Все эти операции происходят в области SGA, представляющей библиотечный кэш части.
Оптимизация SQL запросов
На этапе оптимизации Oracle применяет свой оптимизатор, который называется оптимизатором по стоимости ( Cost-Base Optimizer — CBO), для выбора наилучшего метода доступа для извлечения данных из присутствующих в запросе таблиц и индексов. За счет использования предоставляемых статистических данных и любых указываемых в SQL-запросах подсказок, CBO генерирует для SQL-оператора оптимальный план выполнения.
В общем случае этап оптимизации можно поделить на два отдельных подэтапа: перезапись запроса и генерация физического плана выполнения запроса. Давайте рассмотрим эти два отдельных подэтапа оптимизации более подробно.
Этап перезаписи запроса
На этом этапе дерево синтаксического разбора преобразуется в абстрактный логический план выполнения запроса. Он представляет собой первоначальный вариант реального плана выполнения запроса и содержит только общую алгебраическую переформулированную версию исходного запроса. То есть во время этого этапа различные узлы и ветви дерева синтаксического разбора заменяются операциями реляционной алгебры. Обратите внимание на то, что перезапись запроса здесь означает совсем не то, что перезапись запроса, выполняемая при использовании материализованных представлений.
Этап генерации плана выполнения
На этом этапе Oracle преобразует логический план в физический план запроса. Для обработки запроса оптимизатору может быть доступно на выбор сразу несколько алгоритмов. Он выбирает самый эффективный из этих алгоритмов для ответа на запрос и определяет наиболее эффективный способ для реализации операций. Помимо принятия решения о том, какие операционные шаги лучше всего выполнять, он еще также определяет порядок, в котором необходимо выполнять эти шаги. Например, решив, что нужно выполнять операцию соединения между таблицей A и таблицей B, оптимизатор далее будет определять, какого типа должно быть это соединение, и в каком порядке его лучше выполнять.
В общем, при генерации физического плана или плана выполнения запроса оптимизатор принимает во внимание следующие факторы:
- различные операции (например, операции соединения), которые подлежат выполнению во время запроса;
- порядок, в котором должны выполняться эти операции;
- алгоритм, который должен использоваться для выполнения каждой из них;
- наилучший способ для извлечения данных с диска или из памяти;
- наилучший способ для передачи данных во время запроса из одной операции другой.
Оптимизатор может генерировать несколько действительных физических планов запроса, которые являются потенциальными планами выполнения. Затем оптимизатор делает выбор между ними путем оценки стоимости каждого возможного физического плана на основании доступных ему статистических данных по таблицам и индексам и выбора того плана, подсчитанная стоимость которого оказывается наименьшей. Этот процесс оценки стоимости возможных физических планов запроса называется оптимизацией запроса по стоимости (cost-based optimization). Стоимость выполнения плана напрямую зависит от того, сколько ресурсов (ввода-вывода, памяти и ЦП) для него требуется. Потом оптимизатор передает выбранный самый низкий по стоимости физический план запроса механизму выполнения запросов Oracle. В следующем разделе рассматривается простой пример, чтобы можно было лучше разобраться в том, что собой представляет процесс оптимизации процесса по стоимости.
Пример оптимизации запроса по стоимости
Давайте предположим, что требуется выполнить показанный ниже запрос, предусматривающий поиск информации обо всех руководителях (supervisor), которые работают в Далласе (Dallas):
SQL> SELECT * FROM employee e, dept d WHERE e.dept_no = d.dept_no AND(e.job = 'SUPERVISOR' AND d.city = 'DALLAS'); SQL>
Получить список руководителей можно тремя способами. Давайте рассмотрим три этих способа и вычислим стоимость получения результатов в случае применения каждого из них.
Для произведения вычислений по стоимости давайте исходить из следующих предположений:
- считывать и записывать данные можно только по одной строке за раз (в реальности операции ввода-вывода выполняются обычно на уровне блоков, а не на уровне строк);
- база данных записывает каждый промежуточный шаг на диск (опять-таки, в реальном мире такого может и не быть);
- с таблицами не ассоциированы никакие индексы;
- в таблице employee содержится 2000 строк;
- в таблице dept содержится 40 строк и руководителей тоже 40 (по одному на каждое отделение);
- в Далласе всего функционирует десять отделений.
В следующих разделах показаны три разных запроса, извлекающие одни и те же данные, но с использованием разных методов доступа. Для каждого запроса подсчитывается грубая стоимость, чтобы их можно было сравнить в плане потребления ресурсов. Первый запрос подразумевает выполнение декартового соединения.
Запрос 1: декартово соединение
В случае применения этого запроса сначала получается декартово произведение таблиц employee и dept, а затем проверяться, какие из строк в нем удовлетворяют требованию:
WHERE e.job=supervisor AND d.dept=operations AND e.dept_no=d.dept_no
Общая стоимость выполнения этого запроса будет выглядеть так:
- декартово произведение таблиц employee и dept потребует считывания обеих таблиц, т.е. 2000 + 40 = 2040 операций чтения;
- создание декартова произведения — 2000 * 40 = 80000 операций записи;
- считывание результата декартова произведения для его сравнения с условием выбора строк — 2000 * 40 = 80000 операций чтения;
- итого общая стоимость ввода-вывода составит: 2040 + 80000 + 80000 = 162040.
Запрос 2: соединение двух таблиц
Второй запрос подразумевает выполнение соединения таблиц employee и dept. В случае применения этого запроса сначала будет осуществляться соединение таблиц employee и dept по столбцу dept_no, а затем выборка из результатов этого соединения всех строк, которые удовлетворяют условию:
WHERE e.job=supervisor and city=Dallas
Общая стоимость выполнения этого запроса будет выглядеть так:
- соединение таблиц 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.
