Связываемые переменные при работе с Oracle

[axbegin]

Недавно начал работать с Аксаптой, так что простите, если спрашиваю общеизвестные факты.

1) Правильно ли я понимаю, что Аксапта сама генерит запросы для Oracle. Причем делает это не используюя связываемые переменные. То есть вместо конструкции:

select ... where a = :a and b = :b

Все запросы на Oracle уходят с уже подставленными числами ?
Насколько я понял, ситуация выглядит именно так и это приводит к тому, что Oracle приходится разбирать и строить планы выполнения для всех запросов. Другими словами, для каждого запроса Oracle приходится выполнять стадию parse, что приводит к долгим ожиданиям latch free.

2) Можно ли средствами Axapta повлиять на нее так, чтобы в ряде запросов она все-таки использовала связываемые переменные?

3) Кто-нибудь пробовал на рабочей базе Axapta включать опцию CURSOR_SHARING и к чему это приводило? Теоритические плюсы и минусы такого шага я понимаю - интересует именно специфика Аксапты.

[kashperuk]

Ну, если я правильно понял, что связываемые переменные - это placeholders, то вообще-то везде должны по-умолчанию использоваться placeholders (начиная с версии 4.0)
А до этого для некоторых запросов (простых - без джоина таблиц определенных групп) тоже использовались placeholders.
Можно явно указать системе, чтобы она использовала их в запросе. (для каждого запроса в отдельности)
Но возможно с Ораклом что-то напортачили.

[fed]

Цитата:

Сообщение от axbegin

Недавно начал работать с Аксаптой, так что простите, если спрашиваю общеизвестные факты.

3) Кто-нибудь пробовал на рабочей базе Axapta включать опцию CURSOR_SHARING и к чему это приводило? Теоритические плюсы и минусы такого шага я понимаю - интересует именно специфика Аксапты.

Ну я пробовал. (Точнее - разбирался с последствием того что знакомые попробовали. ) Эффект - поразительный. Меня позвали разбираться почему Аксапта тормозит очень. Выяснилось - что Oracle почему-то не использует индексы по строковым полям. Как показало вскрытие - именно из за баловства с cursor_sharing. Ситуация следующая:
1. Из за того что Axapta везде использует case-insensitive сравнение, все индексы по строковым полям в Аксапте, при использовании Oracle, строятся как функциональные. То есть индекс строится не по полю custAccount, а по NLS_LOWER(Substr(custAccount,1,10)) (10 - длина поля)
2. При формировании запросов, литералы тоже заключаются в функцию NLS_LOWER. То есть - на сервер отправляется запрос вида select * from custTable where NLS_LOWER(Substr(custAccount,1,10)=NLS_LOWER(Substr('Рога и копыта'),1,10). Оптимизатору Оракла хватает ума сопоставить выражение в левой части равенства с выражением индекса
3. Если поставить CURSOR_SHARING=EXACT, Oracle транслирует полученный запрос в select * from custTable where NLS_LOWER(Substr(custAccount,:bind1,:bind2)=NLS_LOWER(Substr(:bind3,:bind4,:bind5), после чего подставляет конкретные значения bind-переменных. Нетрудно догадаться, что левая часть сравнения перестает сопоставляться с выражением индекса и индекс перестает использоваться для исполнения запроса.

Это все было справедливо для Oracle 10g Release 1. Может в более поздних версиях это дело как-то изменили.

Кстати - даже в отрыве от проблем с ораклом, я не вижу особого смысла в замене литералов на переменные запроса. На практике - узким местом в 95% случаев является не процессор, а жесткий диск. Использование переменных запроса слегка снижает нагрузку на процессор, но при этом значительно повышает вероятность генерации неправильного плана запроса. Кроме того - Аксапта не использует (ну хорошо - почти не использует ) хранимые процедуры, триггера, и вообще средства программирования сервера БД. Так что нагружать именно процессора сервера БД - там просто нечем. Так что, на мой взгляд, любые попытки массовой параметризации запросов приносят негативный результат именно в случае Аксапты.

[Vadik]

Цитата:

Сообщение от fed

Кстати - даже в отрыве от проблем с ораклом, я не вижу особого смысла в замене литералов на переменные запроса. На практике - узким местом в 95% случаев является не процессор, а жесткий диск.
..
Так что, на мой взгляд, любые попытки массовой параметризации запросов приносят негативный результат именно в случае Аксапты.

А вот с этим утверждением г-н Кайт думаю поспорил бы Неиспользование variable binding как раз очень часто является причиной "заторможенности" системы (axbegin об этом и пишет кстати). А forceliterals где надо и так по системе разбросаны достаточно щедро

axbegin, почитайте, попробуйте покрутить настройки на AOS-е, связанные с литералами. Кстати, версию Вы не указали, а их немало

[fed]

Цитата:

Сообщение от Vadik

А вот с этим утверждением г-н Кайт думаю поспорил бы Неиспользование variable binding как раз очень часто является причиной "заторможенности" системы (axbegin об этом и пишет кстати). А forceliterals где надо и так по системе разбросаны достаточно щедро

Возможно. Но я на своей практике много раз видел ситуации, когда из за использования параметризации порождался КРИВЕЙШИЙ план запроса и ни разу не видел чтобы из за использования литералов перегружались процессора сервера БД. Топикстартер, кстати, пишет про долгие ожидания latch free, но не пишет насколько катастрофична ситуация. То есть - если система долго ждет компиляции запросов, это совсем не означает что все плохо. Если среднее время компиляции запроса - более или менее пристойное (пользователи ждут исполнения запроса, а не его компиляции) и средняя загрузка процессоров сервера БД в пределах 25-30% - то ничего страшного нету...

А Кайт, кстати, не пишет о том, нафига нужна статистика и гистограмы, если все запросы жестко параметризованы ?

[Vadik]

Цитата:

Сообщение от fed

Возможно. Но я на своей практике много раз видел ситуации, когда из за использования параметризации порождался КРИВЕЙШИЙ план запроса

Это не кривой план. Вероятно, это был хороший (возможно, даже оптимальный) план - для набора параметров, с которыми он строился. Я же вроде написал - там, где изменение параметров значительно влияет на выбор оптимального плана исполнения, разумеется НАДО использовать литералы. Forceliterals в стандартном коде достаточно широко и осмысленно используется, так что тут разработчикам зачет. "Легкие" запросы - вполне достойно перевариваются с prepared execution

Цитата:

и ни разу не видел чтобы из за использования литералов перегружались процессора сервера БД

А сталкивался с ситуацией, когда время подготовки (разбор, генерация плана исполнения) запроса превышает время его исполнения? И таких запросов - сотни?

Пока что ни axbegin, ни я не говорили о ПЕРЕГРУЗКЕ сервера из-за того, что не используются связанные переменные. Речь шла об увеличении времени отклика

Цитата:

А Кайт, кстати, не пишет о том, нафига нужна статистика и гистограмы, если все запросы жестко параметризованы ?

Переведи, пожалуйста. Нафига нужна статистика? Не понимаю

Мой посыл таков - идеальной настройки для всех случаев жизни нет. Но prepared execution по умолчанию (для основной массы OLTP-шных запросов) и осознанное использование forceliterals в коде x++ там, где это требуется - работает вполне прилично. По крайней мере, средняя температура по больнице (результаты любимого benchmark tool) это подтверждает

[ZVV]

Цитата:

Сообщение от fed

....
3. Если поставить CURSOR_SHARING=EXACT, Oracle транслирует полученный запрос в select * from custTable where NLS_LOWER(Substr(custAccount,:bind1,:bind2)=NLS_LOWER(Substr(:bind3,:bind4,:bind5), после чего подставляет конкретные значения bind-переменных. Нетрудно догадаться, что левая часть сравнения перестает сопоставляться с выражением индекса и индекс перестает использоваться для исполнения запроса.
.....

Наверное всё таки имелся ввиду CURSOR_SHARING=FORCE?

Было бы интересно ещё, как он поведёт себя с SIMILAR...

[fed]

Угу - конечно имелось в виду CURSOR_SHARING=FORCE

[fed]

В общем - общий вывод простой. Универсального подхода не существует. Надо разбираться с каждым конкретным случаем. Использование литералов дает гарантированное увеличение нагрузки на процессора, использование переменных - увеличивает (но не гарантирует) шансы на генерацию некорректного плана запроса, что увеличиваетт нагрузку на жесткие диски и память. По моему убеждению, чаще всего на современных серверах, узким местом являются именно диски. Скажем - в 2003 году типичным сервером БД на внедрении Аксапты был двухпроцессорный Xeon. Теперь - двухпроцессорный ЧЕТЫРЕХЯДЕРНЫЙ Xeon. За счет увеличения числа ядер, реальная производительность выросла где-то раза в 2-2,5. В то же время - за последние 5 лет быстродействие дисковых массивов увеличилось, но наверное процентов на 30-40 примерно. Если тенденция сохранится - процессорная мощность будет намного дешевле чем дисковая. Учитывая что генерация плана запроса хорошо паралелиться - правильнее использовать литералы, а не bind-переменные.