DeepEdit!

Оптимизация неэффективного SQL, несомненно, важнейшее средство повышения производительности в арсенале аналитика по производи­тельности Oracle. Если вызов базы данных порождает более десятка вызовов LIO для каждой строки, возвращаемой из каждой таблицы фразы FROM соответствующей команды SQL, то, скорее всего, произво­дительность такой команды можно повысить. Например, операция со­единения трех таблиц, возвращающая две строки, вероятно, должна требовать не более 60 вызовов LIO.

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

Приложения, выполняющие код SQL, порождающий множество вызо­вов LIO, создает серьезные препятствия для масштабируемости систем с большим количеством пользователей. Лишние вызовы LIO не только отнимают мощность процессора, но и могут вызвать большое количе­ство событий latch free для защелок cache buffers chains [Millsap (2001c)]. Получение и освобождение защелок само по себе может при­вести к избыточному потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для _SPIN_COUNT по умолчанию (обычно не стоит этого делать).

В настоящее время есть ряд полезных ресурсов по оптимизации SQL: [Ensor and Stevenson (1997a, 1997b); Harrison (2000); Lewis (2001b, 2002); Kyte (2001); Adams (2003); Lawson (2003); Holt et al. (2003)].1 Участники разнообразных списков рассылки, например Oracle-L (http://www.cybcon.com/~jkstill), также делают замечательное дело, помогая друг другу писать эффективный код. Все эти ресурсы содер­жат хорошие советы о том, как писать эффективный SQL, применяя методы, часть из которых перечислена ниже:

Диагностика выполнения команд SQL посредством таких инстру­ментов, как tkprof, EXPLAIN PLAN, и отладочных событий 10032, 10033,10046,10079,10104 и 10241.

Диагностика поведения оптимизатора запросов Oracle при помощи отладочного события, подобного 10053.

Работа с текстом SQL, направленная на использование более эффек­тивных планов выполнения.

Выбор эффективной стратегии индексирования с тем, чтобы обес­печить сокращение объема данных для запросов без создания до­полнительной нагрузки в операциях INSERT, UPDATE, MERGE и DELETE.

Применение хранимых планов выполнения с целью заставить оп­тимизатор запросов Oracle использовать выбранный вами план.

Создание приемлемой статистики для таблиц, индексов и базы дан­ных с тем, чтобы наилучшим образом информировать оптимизатор запросов Oracle о ваших данных.

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

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

Оптимизация разбора

Избыточный разбор - это верный путь к невозможности обеспечить масштабируемость приложения для работы с большим количеством  пользователей [Holt and Millsap (2000)]. Студенты обычно приходят к нам, считая, что полные разборы (hard parses) значительно замедля­ют обработку транзакций, а вот в частичном разборе (soft parse) нет ничего страшного. Более того, иногда люди считают, что полного раз­бора можно избежать, а частичного - нет. Оба мнения верны лишь на­половину. Полные разборы действительно так ужасны, как о них ду­мают, и их можно избежать, используя в коде SQL переменные связы­вания вместо литералов. Однако частичный разбор столь же ужасен, и часто без него тоже можно обойтись.

Многие авторы употребляют словосочетание «частичный разбор» (soft parse) как синоним для «вызова разбора» (parse call). Мне больше нра­вится термин «вызов разбора», т. к. он обращает наше внимание на приложение, внутри которого в действительности может быть пред­принято спасительное действие. Если же говорить о «частичном разбо­ре», то внимание акцентируется на базе данных, которая не является местом решения нашей проблемы. И вот почему. Каждый раз, когда серверный процесс Oracle получает вызов разбора от приложения, это­му процессу необходимо использовать процессор сервера базы данных. Если будет обнаружен подходящий для этого запроса разделяемый курсор в кэше курсоров сеанса или библиотечном кэше Oracle, то вызов разбора никогда не приведет к полному разбору, и окажется, что затра­ты на разбор совсем не так велики, как могли бы быть. Однако отсут­ствие разбора обходится еще дешевле, чем частичный разбор. Наилуч­шую масштабируемость для большого количества пользователей име­ют приложения, в которых разбор происходит как можно реже. Следу­ет по возможности избавиться от всех ненужных вызовов разбора.

Для обеспечения масштабируемости лучше всего, чтобы прило­жения осуществляли минимально возможное количество вызовов базы данных. Именно в этом направлении развивается Oracle Call Interface (OCI). Например, в версии 8 OCI предприняты меры для снижения количества пересылок между клиентом и сервером (http://otn.oracle.com/tech/oci/htdocs/Developing_apps.html). Версия 9.2 OCI идет еще дальше, делая так, что многие вызовы базы данных приложения вообще не достигают базы дан ных (http://otn.oracle.com/tech/oci/htdocs/oci9ir2_new_features).

В системах с высокой конкурентностью и неоправданно многочислен­ными вызовами разбора большое количество событий CPU service зачас­тую коррелирует с большим количеством событий ожидания latch free для библиотечного кэша, разделяемого пула и других защелок. Само по себе получение и освобождение защелок может привести к избыточно­му потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для SPINCOUNT по умолчанию (опять-таки, обычно не стоит этого делать). Более того, избыточные вызовы разбора могут привести к ненужным задержкам SQL*Net message from client, способным добавить до нескольких секунд лишнего време­ ни отклика на каждую секунду реальной работы, выполняемой в базе данных. Наконец, вызовы разбора для длинных фрагментов SQL созда­ют ненужные задержки SQL*Net more data from client, которые также мо­гут внести существенный вклад в увеличение времени отклика.

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

Старайтесь обходиться без строковых литералов в инструкциях WHERE. Заменяйте их переменными связывания (заполнителями), особенно когда строковый литерал имеет высокую кардинальность (т. е. строка может иметь множество значений). Использование строковых литералов вместо переменных связывания приводит к затратам процессорного времени (CPU service), а также вызывает в системах с высокой конкурентностью ненужные события ожида­ния освобождения защелок для библиотечного кэша, разделяемого пула и объектов кэша строк.

Старайтесь выносить вызовы разбора за циклы, с тем чтобы при­ложение имело возможность несколько раз повторно выполнить курсор, подготовленный одним вызовом разбора. Псевдокод приме­ра 11.2 показывает, как это сделать.

• Отключите те функции интерфейсного драйвера, которые приводят к увеличению количества вызовов разбора по отношению к их чис­лу в исходном коде приложения. Например, Perl DBI содержит ат­рибут уровня prepare под названием ora_check_sql. Его значение по умолчанию равно 1, что означает два вызова разбора для каждого вызова функции Perl prepare. Первый вызов разбора выполняется с тем, чтобы помочь SQL-разработчику приложения быстрее отла­дить свой исходный код за счет предоставления более подробной диагностической информации для неудачных вызовов разбора. Од­ нако в промышленных системах такую функцию следует отключить, т. к. она приводит к излишним вызовам разбора.

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

Не передавайте в вызовы разбора длинные текстовые строки SQL. Применяйте вместо этого вызовы хранимых процедур. Отправка длинных текстовых строк SQL в вызовах разбора приводит к пере­расходу процессорной мощности на сервере (даже в случае приме­нения переменных связывания). Даже если SQL-текст является полностью разделяемым, ядро Oracle должно проверять объектные привилегии при каждом получении текстовой строки SQL от поль­зователя с новым идентификатором (если ядро получает вызов хра­нимой процедуры, то такая процедура работает в контексте своего владельца, поэтому привилегии на объекты внутри пакета проверя­ются однократно, если разработчик приложения не укажет, что следует использовать привилегии вызывающего) [Adams (2003) 371-372]. Передача длинных текстовых строк SQL также может привести к ненужной нагрузке на сеть, что будет проявляться в за­держке SQL*Net more data from client для процесса, осуществляющего разбор, и в столь же долгих задержках SQL*Net message from client для остальных процессов.

Сведите к минимуму применение публичных синонимов в прило­жении, если количество ссылок на объекты очень велико [Adams (2003) 373-375]. Дополнительную информацию можно найти с помощью google.com на сайте www.ixora.com.au (поиск по словам «pub-lic synonym»).