Вопрос на поверхности простой, но хочется услышать от экспертов разбирающихся в оптимизаторе оракла, или знакомыми с бенчмаркингом динамических запросов в Оракле.

Мне хочется измерить время исполнения запроса. Запрос параметризован переменными хоста, поэтому измерений будет более сотни, их сделает скрипт который предстоит написать.

Для целей моей задачи запрос считается непрозрачным и цельным, как чужая вьюха: вызывается, бурлит секунд 30 и выдает пару килобайтов информации которая забирается одним глотком. Не имеет побочных эффектов (не пишет в таблицы). Полное раздолье для внутреннего оптимизатора Оракла.

Критерии удачной измерялки:
- запрос должен исполниться в один прием как это происходит в рабочей базе. Циклы/кусроры не годятся.
- резалтсет должен быть получен и съеден. select count(*) from V не годится, оптимизатор Оракла не простак и пропустит большинство вычисляемых столбцов (а то и джойнов) которые не влияют на число строчек.
- измерялка не должна перечислять названия столбцов (полей?) резалтсета. Их много, я ленив.
(перечисление столбцов через системные таблицы не является решением, т.к. запрос динамический sql)
- желательно чтоб измерялка только читала и не писАла. Хотя резалтсет невелик и у меня есть права на запись, я на знаю как запись повлияет на точность измерения и с точки зрения чистоты решения измерялка менять состояние базы не должна.
- желательно чтобы кроме интервала времени измерялка дала если не хэш результата, то хотя бы какой нибудь параметр резалтсета, позволяющее следить за нарушением воспроизводимости.

Извращая синтах оракла, идеальное для меня решение выглядит что-то в виде


где значение каждого поля учтено (критерий 2), порядок вычисления строк не важен (критерий 1) и т.д. Стоимостью вычисления контрольной суммы на 2КБ данных можно пренебречь.

Я пока нашел три решения, каждое со своими мелкими недочетами.
Один использует CTAS, два других полагаются на алгебру множеств. Измерения всех трех совпадают на моем 11g.

Из этих трех, лучше всего удовлетворяет моим критериям решение в виде


но у меня нет уверенности что будущая версия оптимизатора Оракла не заметит тавтологию и не схлопнет вычисляемые столбцы.
Решение через CTAS в одну строчку, но нарушает критерий 4 при каждом измерении, гибридный (CTAS/minus) полагается на создание объекта схемы.

Жаль что в 11g нет PTT, они тут были бы кстати:

Вячеслав Любомудров,

спасибо, посмотрю на что влияет оптимизация PL/SQL, может пригодиться для других задач.
Цикл не подходит, по причине первого критерия.

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

Какое-то приложение винды, говорит Ораклу: "исполни этот запрос целиком и полностью со всеми известными тебе оптимизациями, и дай мне результат. значение чостовых переменных следующее:...".

Это отличается от доступа по частям (дай мне только строки 101-200. а теперь 201-300..)

PuM256,

Я хочу получить число в секундах, вызвав SQL запрос (считается черным ящиком) способом, сравнимым с боевым.
Могу это сделать написав клиент в винде, но хотел обойтись силами SQL+.
Я придумал и испытал три метода, они дали одинаковые результаты, совпадающие (+/-5%) с измерением на живой базе.
Методы скорее всего годны.

Решение с использованием PTT мне недоступно на 11g, поэтому некуда девать resultset без записи в базу.

Знания о планах, парсинге и т.д. полезны, и пригодятся когда/если возникнет вопрос об оптимизации запроса, но не в этой теме.

env,

ачипятка. Хостовых переменных, конечно.

set timing помогает увидеть время запроса. Тема про метод вызова запроса.

про jmeter не знал, очень полезно.
Кто не знает, вот ссылка: https://jmeter.apache.org/usermanual/build-db-test-plan.html

Теперь смогу скриптануть и померять снаружи, без написания кода.

Leonid Kudryavtsev,

много рассуждений, но мало кода, а рассуждения не заменяют практику.

PuM256,

Да, пример понятный. Приложение может быть написано плохо, сетевое соединение может быть плохим, и т.д.

Меня интересует замер скорости исполнения определенного запроса дла базиса (baseline).
У меня нет знаний архитектуры приложения, но есть несколько оосновных предположений о его устройстве и есть голова которая позволяет сделать несколько выводов, например:
- если jmeter (например) получает совпадающий результат запроса значительно быстрее чем приложение, то существует узкое бесто вне оракла
- если jmeter получает совпадающий резалтсет значительно медленнее, то я пропустил важные настройки jmeter которые влияют на производительность
- если время примерно одинаковое для jmeter и для приложения, то в худшем случае ошибки настроек совпали :)

Я не использовал jmeter для моих замеров. Я исключил влияние настроек клиента, сделав тест через CTAS.
Мои результаты на 5% быстрее от наблюдаемого времени исполнения запроса в приложении (что хороший признак, значит клиент без грубых ошибок в этой области), и воспроизводятся с точностью около 2%, что тоже радует.
Ради тщательности, я сделал замеры двумя другими методами описанными ранее, результаты совпали.

Теперь если я, или кто-то другой, будет делать изменения в этом важном запросе, у меня есть способ измерения и результаты предыдущих замеров для оценки результативности усилий.

Чтение резалтсета в цикле "for" как было предложенно происходит заметно медленнее.
У меня есть догадки почему это происходит, но независимо от моих догадок цикл тормозит процесс и искажает результаты.

Цикл не предлагать :)

Спасибо за подсказку.

Вы думаете, я хвастаться сюда прихожу? :) За знаниями, которые иногда приходится клещами..

Я не знал про jmeter, и я не знал про AWR/ASH. Новая для меня область.

С jmeter я вроде разобрался, на следующей неделе поковыряю AWR/ASH.
Надеюсь, это как раз подойдет для моих целей.