?

Log in

No account? Create an account

Сб, 25 апр, 2009, 00:12
SQL или PL/SQL?

Наблюдаю интересную картину: абсолютное большинство отчётов и программ под Oracle Applications написано не в декларативном SQL-стиле, а в традиционном процедурном стиле PL/SQL. Даже родные оракловые пакеты этим грешат.

А между тем, это чревато потерей производительности, причём иногда совершенно катастрофической. Беглый взгляд в трассу (как в историю болезни), внедрение всей развесистой «программной логики» внутрь SQL-запроса, и — о чудо! — всё работает в сотню-другую раз быстрее. Почему? А вот почитайте, написал статью на эту тему.

SQL или PL/SQL? — взгляд с точки зрения производительности.

Пт, 24 апр, 2009 21:43 (UTC)
hardsign: Имею сказать

0. Оформление кода™ Я так же оформляю, и даже шрифт lucida console.

1. Логические чтения - это хорошо, но не это главное. При table access full используется многоблочное чтение, а при index scan (за исключением fast full scan) - одноблочное. Соответственно, количество операций ввода/вывода на порядок больше.

2. Table full scan может выполняться в параллель, а это не db file scattered read, а direct path read. Во-первых, только многоблочное чтение, а во-вторых, кэш вообще не используется, чтение идёт непосредственно в память процесса. Следовательно, защёлки теряют актуальность.

3. SQL и PL/SQL - братья, но не близнецы. Выполняются они всё-таки в разных контекстах, и вызов функции - это переключение контекста, а выполнение запроса и fetch внутри функции - ещё одно. То есть у тебя на каджую строку происходит 4 переключения контекста. Операция не из дешёвых. Собственно, bulk collect/forall существенно быстрее_чем во многом из-за более редкого переключения контекстов.

То есть мысль™ в статье¤ высказана в целом правильная, то иллюстрация, то пояснение, мягко говоря, неполное.

Вообще чем дальше, тем больше прихожу к убеждению, что использование statspack, awr, autotrace и тому подобных приблуд¤ при оптимизации отчётов в частности и DSS-систем в целом только вредит. Один из упомянутых в статье коллег попрсил прислать снапшоты статспака из хранилища, пообещав всё круто оптимизировать. Посмотрел и заявил: да у вас слишком много данных читается из файлов! На что я смог ответить только "<censored> мать, да что ты говоришь!"

:)

Сб, 25 апр, 2009 10:29 (UTC)
egorius

Перво-наперво, спасибо за многабукф по делу.

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

ъъ) Хотелось показать, что есть некоторая метрика, пусть не идеальная, но вполне применимая на практике, с помощью которой можно сравнивать запросы (неопытные товарищи часто пытаются оценивать эффект оптимизации с секундомером, а иной раз даже по изменению cost в плане запроса...).
Логические чтения посчитать очень просто, а вот как учесть в метрике эффект многоблочных чтений? Я не знаю. А как посчитать затраты на переключение контекста? Тоже не знаю. Понятно, что это всё играет свою роль, и немалую, но можешь ли ты предложить какой-то способ вычисления затрат_на (или эффекта_от)? Так, чтобы оперировать цифрами, а не общими соображениями?

ъъъ) Не хотелось влезать в многочисленные детали, которые определённо не исчерпываются тем, о чём ты написал. Ради ясности мысли пришлось чем-то пожертвовать. С другой стороны, если я соображу, как внятно рассказать_о, то непременно это сделаю.

Кстати, на разницу наших восприятий похоже влияет то, что ваше хранилище™ есть DSS в чистом виде, а наша система гибридная, в ней ещё и постоянно что-то апдейтится. Поэтому, кстати, у нас не используется распараллеливание (вроде обсуждали эту тему).

Сб, 25 апр, 2009 17:34 (UTC)
hardsign

метрика, пусть не идеальная, но вполне применимая на практике, с помощью которой можно сравнивать запросы

Слышал про такую штуку как ожидания? Вот посчитай время и количество ожиданий при одноблочном и многоблочном чтении, будет о чём задуматься. И с латчами то же самое.

Как посчитать переключения контекстов - не знаю, но твою кривую функцию можно как минимум написать чуть менее криво_чем, если уж SQL товарищи писать не умеют:

create or replace function get_authors(p_book_id number)
return varchar2
is
  type t_name is table of writers.name%TYPE index by binary_integer;
  v_name t_name;
begin
  select w.name
  bulk collect into v_name
  from writers w, authors a
  where a.book_id = p_book_id and w.writer_id = a.writer_id
  order by a.seq_num;
  if v_name.count>1 then
    return v_name(1)||' и др.';
  else
    return v_name(1);
  end if;
end;


Количество переключений контекста сокращается вдвое!

А ещё время выполнения PL/SQL кода предсказуемо, в отличие от SQL. Помню, делал халтурку® для одной конторы, так я там написал всё на SQL. Они сказали - иногда работает шустро, а иногда - хоть плачь. А им нужно реальное время.

Конечно, это был Ъ™ 8i, да и вообще использование реляционной СУБД как системы реального времени выглядит по меньшей мере странно, но думаю, что формсы твои любимые написаны на PL/SQL именно из этих соображений. Из чего, разумеется, не следует, что надо упобобляться братьям-индусам.


Распараллеливание плохо потому, что для того, чтобы direct path read был возможен, надо, чтобы все блоки были сброшены на диск, т.е. перед каждым запросом выполняется маленький такой checkpoint. Но если разделить таблицы, которые регулярно абдейтяццо и таблицы, по котоым строятся отчёты, то пуркуа бы и не па даже на гибридной системе :)

Сб, 25 апр, 2009 21:05 (UTC)
egorius

Насчёт ожиданий, боюсь, не всё так просто. Сравнить одноблочные чтения с многоблочными ещё можно, хотя для этого, наверное, понадобится отдельный пример. Подумаю_над. А вот latch free можно и вообще не заметить, железяки-то мощные... Вот когда таких кривых отчётов много и система начинает загибаться — другое дело, но до этого лучше не доводить :)

Про переключение контекстов тоже есть над чем поразмыслить. Если получится продемонстрировать разницу на твоём примере, то завтра же высылаю $10.

А с распараллеливанием у нас облом, потому что хрен-то там чего разделишь. Да на наших жалких объёмах и без распараллеливания всё может очень шустро работать, если, конечно, зарядить могзи.

Пт, 24 апр, 2009 22:54 (UTC)
(Anonymous)

Аффтар жжот!
"сериализуется с помощью защелок" - это упорядочивается с помощью триггеров, что ли? :Е
Сама идея, что микрооптимизация, особенно ориентированная на "знание" внутренней структуры, зачастую ухудшает макропоказатели, в-общем, не нова.
Однако есть наблюдение о ходе эксперимента, точнее о собираемой статистике: оптимизируемый параметр (consistent gets) может не вполне отражать картину, если учитывает только обращение к данным "на носителе", но не учитывает внутренние манипуляции, которых во втором случае гораздо больше. Даже с учетом того, что ОЗУ намного быстрее чем диск, нельзя исключить ненадежность результатов.
СП

Сб, 25 апр, 2009 10:47 (UTC)
egorius

Защёлки — это latches, оракловый механизм ограничения одновременного доступа к памяти. В статье Миллсапа, на которую я ссылаюсь, они неплохо описаны.
Да, внутренние манипуляции по обработке того, что прочиталось, действительно не учитываются. Действительно, обработка одного блока может занимать больше времени, чем обработка другого. Тем не менее, этот параметр всё-таки вполне адекватен, что подтверждается практикой. И у него есть огромный плюс: его легко измерить.

Пн, 19 апр, 2010 09:43 (UTC)
n1919

SQL-запрос в 1000 строк когда-нибудь видели?
то еще зрелище...

Пн, 19 апр, 2010 18:12 (UTC)
egorius

Не только видел, но и писал.
И должен сказать, что зачастую это всё равно лучше, чем процедурный код. Другое дело, что на SQL надо уметь писать аккуратно, чтобы эти 1000 строк не были одним большим месивом. Впрочем, месиво — оно и на процедурном языке месиво...