Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 13:

Вопросы оптимизации. Транзакции и блокировки

< Лекция 12 || Лекция 13: 12345 || Лекция 14 >
Аннотация: Приводятся некоторые общие сведения об оптимизации запросов в Oracle. Рассматриваются механизмы и примеры организации транзакций как средства регулирования доступа к данным базы.

Некоторые замечания по оптимизации выполнения предложений SQL

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

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

SQL предполагает возможное влияние формулировки запроса на эффективность вычислений. Кроме этого, он явно вводит специальные структуры в БД для ускорения вычислений: это индексы и овеществленные представления данных (materialized views). Многие советы по формулировкам запросов в SQL ставят своей целью побудить СУБД использовать при доступе к данным индекс.

Oracle при формировании плана обработки поступившего запроса пытается переформулировать запрос в более выгодный для вычислений вид. При этом используются шаблоны равносильных преобразований, наличие вспомогательных структур БД и статистика объектов хранения. Сверх этого Oracle дает такие средства влияния на схему вычисления, как подсказки оптимизатору, особые параметры СУБД и особые конфигурации структур хранения объектов.

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

Сокращение вычислений при локализации объектов доступа

Полное указание имен объектов в запросе пусть незначительно, но сокращает время разбора. Например, есть два запроса:

SELECT emp.ename FROM scott.emp;
SELECT ename FROM emp;

Второй имеет предпосылки обрабатываться дольше, так как при разборе запроса требует дополнительной работы по уточнению принадлежности таблицы схеме и столбца таблице. Заметьте к тому же, что, строго говоря, эти предложения не равносильны: при разборе второго может оказаться, что таблица EMP не принадлежит пользователю SCOTT (если запрос выдавался другим пользователем, имеющим одноименную таблицу, или если переменная сеанса CURRENT_SCHEMA имела значение, отличное от SCOTT).

Отказ от повторных вычислений выражений

В некоторых случаях формулировка запроса позволяет отказаться от повторного вычисления выражений. Примерами могут служить логические выражения BETWEEN и IN. Например, формулировка условного выражения

( sal + comm ) BETWEEN 1000 AND 2000

способна обрабатываться эффективнее равносильной формулировки

( sal + comm >= 1000 ) AND ( sal + comm <= 2000 )

Ответ на вопрос, будет ли первая формулировка действительно обрабатываться быстрее, зависит от сложности выражения. В данном случае подвыражение ( SAL + COMM ) достаточно просто, чтобы при построении плана, на этапе анализа формулировки, оптимизатор заметил, что во втором случае подвыражение повторяется, и фактически вычислял бы его однократно. Однако если бы вместо этого подвыражения стояла более сложная конструкция или если бы подвыражение содержало бы обращение к функции пользователя (все аспекты вычисления которой оптимизатору неизвестны), формулировку условного выражения через BETWEEN следовало бы признать более выгодной с вычислительной точки зрения. (Другие выгоды от BETWEEN — в надежности кода, из-за однократности записи выражения в запросе и в возможности задействовать в плане вычисления индекс, когда таковой имеется).

Аналогичные рассуждения обосновывают выгоду от использования для построения условных выражений операторов IN или = ANY перед употреблением цепочек сравнения, составленных с помощью OR, и выгоду от ссылки на имя столбца, данное во фразе SELECT, перед воспроизведением выражения в другой фразе. Так, возвращаясь к одному из примеров выше, предпочтение следует отдать формулировке

SELECT job, AVG ( sal ) avgsal FROM emp 
GROUP  BY job
ORDER  BY avgsal
;

перед формулировкой

SELECT job, AVG ( sal ) FROM emp 
GROUP  BY job
ORDER  BY AVG ( sal )
;

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

Оптимизация вычисления составного логического выражения

Логические выражения, составленные цепочками с помощью связок OR или AND, при отсутствии у Oracle информации о сложности вычисления подвыражений вычисляются во вполне определенном направлении. Это обстоятельство можно использовать для размещения элементов цепочек, наиболее вероятно дающих TRUE или же FALSE в соответствующих концах цепочки.

При просмотре сотрудников выражение

( sal > 1000 ) OR ( mgr IS NULL )

будет вычисляться скорее, чем

( mgr IS NULL ) OR ( sal > 1000 ) 

Это объясняется тем, что цепочка, построенная с помощью связок OR, вычисляется слева направо, а сотрудников с зарплатой более 1000 — больше, чем "президентов". Мизерная в данном случае разница может оказаться заметной на вычислительно более сложных выражениях.

Переформулировка для сокращения объема обрабатываемых строк

Перестановка фильтров строк способна сократить объем обработки. Так, предложение SELECT с фразой HAVING иногда можно переформулировать в содержательно равносильное, но вычислительно более эффективное за счет перенесения отбора строк из фразы HAVING во фразу WHERE. Вот пример запроса на количество разных сотрудников по специальностям, помимо клерков:

SELECT   job, COUNT ( * ) 
FROM     emp
GROUP BY job 
HAVING   job <> 'CLERK'
;

Из сказанного ранее следует, что логическая последовательность действий по вычислению результата на этот запрос будет следующей:

  1. отбор строк по условию WHERE;
  2. разбиение строк на группы по условию GROUP BY;
  3. вычисление сверток (агрегатов) по каждой группе;
  4. отбор требуемых групп по условию HAVING.

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

SELECT   job, COUNT ( * ) 
FROM     emp
WHERE    job <> 'CLERK'
GROUP BY job
;

Некоторые специалисты вовсе дают рекомендацию избегать отсева групп фразой HAVING.

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

SELECT e.ename, d.dname 
FROM         emp e
INNER JOIN   dept d
USING      ( deptno )
WHERE e.job <> 'SALESMAN'
;
SELECT e.ename, d.dname 
FROM
   ( SELECT * FROM emp WHERE job <> 'SALESMAN' ) e
INNER JOIN
   dept d
USING
   ( deptno )
;

В то же время не следует недооценивать оптимизатор Oracle: для двух последних запросов (пусть и не самых сложных) он дает одинаковый план исполнения, так что выбор одного из них становится всего делом предпочтения программиста.

Возможность использовать индекс для доступа к строкам таблицы

Хотя доступ к строкам таблицы по индексу не гарантирует высокую скорость (а иногда приводит даже к замедлению), часто он оказывается оправдан. Но решению СУБД употребить существующий индекс может, помимо прочего, препятствовать формулировка условного выражения. Например, в случае указания поля, соответствующего индексированному обычным индексом (древовидным и без функционального преобразования ключа) столбцу, в качестве параметра для функции СУБД откажется от использования индекса:

SELECT empno FROM emp WHERE TRUNC ( empno ) = 7369;

СУБД может воспользоваться индексом (если сочтет целесообразным), только если индексированное поле присутствует в одной из частей сравнения без каких-либо преобразований:

SELECT empno FROM emp WHERE empno >= 7369;

но не в этом случае:

SELECT empno FROM emp WHERE empno + 0 >= 7369;

Oracle не будет также отказываться от использования индекса в сравнениях с помощью других операторов:

empno BETWEEN 7000 AND 8000
empno IN ( 7369, 7865, 8888 )
empno = ANY ( 7369, 7865, 8888 )
empno = ALL ( 7369, 7865, 8888 )

В сравнении оператором LIKE СУБД сможет привлекать индекс только если в проверочной маске первый слева символ не является специальным:

job LIKE 'SAL%'

но не:

job LIKE '_SAL%'

Транзакции и блокировки

Транзакции и блокировки есть механизм регулирования доступа к БД из приложений.

Транзакция в SQL есть логическая последовательность операций DML по внесению изменений в БД, принимаемая или же отвергаемая СУБД в конечном итоге ("по завершению транзакции") в целом. В английском языке слово transaction обозначает единицу общения двух агентов (в нашем случае — программы и СУБД), завершающуюся оказанием взаимного воздействия друг на друга.

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

  • восстанавливать данные при аварийном прекращении сеанса связи программы с СУБД ("единица восстановления");
  • гарантировать целостное представление данных программе при одновременной работе с данными нескольких программ;
  • гарантировать целостное хранение данных в БД при одновременной работе с ними нескольких программ.

Широко известны общие требования к механизму транзакций ("свойства ACID"):

  • атомарность: последовательность изменений в БД, поступающая из программы во время транзакции, принимается или отвергается по завершению транзакции целиком, как единое целое;
  • согласованность: каждая транзакция переводит БД в новое согласованное состояние, при том что, пока она не завершена, согласованности может и не быть;
  • изолированность: изменения в БД, совершаемые в рамках транзакции, станут видны другим транзакциям только после ее фиксации;
  • долговечность: изменения в БД, совершенные в рамках транзакции, не могут пропасть из-за сбоев работы СУБД.

Некоторые эксперты полагают, что требование согласованности для транзакций в БД, то есть соблюдение ограничений целостности, должно обеспечиваться на уровне не транзакции (как то допускают и стандарт SQL, и Oracle), а отдельного оператора DML. Получается, что выполнение этого требования механизмом тразнакций свидетельствует о недостаточности языка SQL для моделирования событий в предметной области. К сожалению это не единственная возможная претензия к языку.

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

< Лекция 12 || Лекция 13: 12345 || Лекция 14 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001