Открыто о СУБД Oracle на русском : сравнение стоимости запросов

Выпуск 4

сравнение стоимости запросов

Уважаемые подписчики! Это очередной выпуск, посвященный одному из вопросов, которые комментировал 16 июня 2002 года Том Кайт на сайте asktom.oracle.com. Вопросы про интерпретацию стоимости запроса, выдаваемой оптимизатором Oracle, задают часто...

Дядю Тома спросили, что учитывать, стоимости или время при сравнении двух запросов:

Я использую стоимостной оптимизатор. У меня есть два запроса, скажем, запрос 1 и запрос 2. Стоимость выполнения запроса один - около 400, а стоимость выполнения запроса 2 - 200. Но времена выполнения находятся практически в обратной пропорции - запрос 1 выполняется быстрее, чем запрос 2. Я проверил это с помощью команды set timing on в sqlplus.

Мне это кажется несколько странным - стоимость выше, а выполняется запрос быстрее. Связана ли стоимость с временем выполнения? По какому критерию настраивать запрос для максимальной производительности (минимального времени отклика)?

и он ответил

Нельзя сравнивать друг с другом стоимости двух запросов. Они просто не сопоставимы.

При получении запроса сервер оценивает множество планов его выполнения. С каждым шагом плана связывается некоторая относительная стоимость. Затем вычисляется функция, определяющая суммарную стоимость запроса для каждого плана. Эти стоимости, (доступные только оптимизатору) можно сравнивать, поскольку они вычислены для одного и того же запроса в одинаковой среде.

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

Нельзя, однако, взять эти два запроса и сравнивать относительные стоимости одного с другим. Стоимости строго привязаны к запросам.

На "стоимость" запроса влияет много факторов. "Стоимость" - это просто искусстевнное значение, используемое для выбора плана выполнения в оперделенной среде. Берем запрос и получаем его стоимость. Теперь, добавляем подсказку оптимизатору, при которой запрос будет выполняться быстрее, и определяем новую стоимость - она может быть выше, а может быть и ниже, чем прежняя. Почему? Потому что в среде с установленной подсказкой изменяются внутренние значения стоимости, присваиваемые различным операциям, в попытке представить вашу подсказку "лучшим" способом выполнения запроса. Возможно, для этого общая уточненная стоимость затребованного плана была сделана ниже, чем у плана, сгенерированного без подсказки, а возможно план без подсказки сделан слишком дорогстоящим (его стоимость была искусственно завышена).

Не сравнивайте эти стоимости, это недопустимо - с таким же успехом эти стоимости могли бы быть случайными числами.

Обсуждение

Комментарий читателя 26 мая 2002 года

Если стоимости двух запросов или даже одного запроса с разными планами выполнения (с подсказкой и без) нельзя сравнивать, на основании чего строить стратегии настройки SQL-операторов? Единственный способ - выполнить и посмотреть, какой выполняется быстрее? Это какая-то ерунда.

select nom_proceso 
from md_grupo a, md_aplicacion b, md_tipo_carga c, md_proceso d 
where a.cod_grupo=b.cod_grupo 
and b.cod_aplicacion=c.cod_aplicacion 
and c.cod_tipo_carga=d.cod_tipo_carga 
and d.cod_tipo_carga=4

Его стоимость - 5. Если добавить подсказку

select /*+ ORDERED */ nom_proceso 
from md_grupo a, md_aplicacion b, md_tipo_carga c, md_proceso d 
where a.cod_grupo=b.cod_grupo 
and b.cod_aplicacion=c.cod_aplicacion 
and c.cod_tipo_carga=d.cod_tipo_carga 
and d.cod_tipo_carga=4

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

Ответ дяди Тома

Ну и в чем тут ерунда?

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

Если более низкая стоимость приводит к более быстрому выполнению, стоимостной оптимизатор и так с этим разберется, без вашей помощи!

Стоимость. Комментарий читателя (Андре) 27 мая 2002 года

Вернемся к стоимостям. Я пытаюсь определить стоимость запроса в SQL*Plus с помощью autotrace. Я обратил внимание, что показатель "physical reads" в результатах autotrace, по-видимому, учитывает только блоки, извлеченные с помощью операций ввода/выода, тогда как показатель "physical reads" в представлении V$SYSSTAT - это комбинация db block gets и consistent gets, как полученных в результате ввода/вывода, так и непосредственно из памяти. Так ли это?

Ответ дяди Тома

physical reads в представлении v$sysstat - это тоже физический ввод/вывод. Выполним в однопользовательской системе:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select name, value from v$sysstat where name 
= 'physical reads';

NAME                                VALUE
------------------------------ ----------
physical reads                       7612

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from all_objects;


Statistics
----------------------------------------------------------
        261  recursive calls
          4  db block gets
      76412  consistent gets
        497  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> select name, value from v$sysstat where name 
= 'physical reads';

NAME                                VALUE
------------------------------ ----------
physical reads                       8116

ops$tkyte@ORA817DEV.US.ORACLE.COM> select 8116-7612 from dual;

 8116-7612
----------
       504

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

Это показывает, что хотя запрос потребовал 76412 логических операций ввода/вывода, физически было прочитано лишь около 500 блоков - причем, это видно как по отчету autotrace, так и по запросу к v$sysstat. Помните - v$sysstat содержит статистику системы, а autotrace использует статистику сеанса, v$sessstat.

Пара вопросов... Комментарий Рака (Ruck) из Сингапура, 27 мая 2002 года

Том,

Твое разъяснение очень полезно. Но у меня осталась еще пара вопросов.

1. Значит ли это, что минимальная стоимость не обязательно соответствует самому быстрому способ выполнения запроса?
2. Как вычисляется эта стоимость? Просто по использованию ресурсов и никак не учитывая скорость выполнения запроса?

Ответ дяди Тома

1) да.

2) Стоимость - это набор чисел, присваиваемых оптимизатором разным шагам выполнения запроса на основе имеющейся статистики. Затем эти стоимости объединяются для получения общей стоимости запроса - одного числа, представляющего оносительную "стоимость" плана выполнения запроса. Затем оптимизатор выбирает для запроса план с минимальной стоимостью. Теоретически, это самый быстрый способ выполнения запроса (это и была цель, в конечном итоге). В действительности так бывает не в 100% случаев.

Можем ли мы сравнивать стоимости ОДНОГО И ТОГО ЖЕ запроса? Вопрос читателя, 14 июня 2002 года

Привет, Том!

Ты сказал, что нельзя сравнивать стоимости двух запросов, но можно ли сравнивать стоимости одного и того же запроса, но с разными значениями optimizer_mode, например, first_rows и all_rows????

Если, например, select * from emp в режиме first_rows имеет стоимость 10, а в режиме all_rows - стоимость 20, значит ли это, что режим first_rows лучше?

Ответ дяди Тома

Нет. Нет. Нет. И еще раз - нет.

Режим оптимизации- это средство для изменения (намеренного) стоимости, связанной с различными шагами.

Именно это и показывает ваш пример. Изменив цель оптимизации, мы увеличили стоимость некоторых процессов, которые были "дешевыми" в случае оптимизации выбора первых строк (например, доступ по индексу для первых строк - "дешевый"). Оптимизация выбора всех строк делает его "более дорогостоящим". С учетом этого, тот же план может получить уже другую стоимость! Ну и как можно сравнивать один с другим?

Нельзя сравнивать стоимости запросов, даже если они "идентичны".

Нельзя делать никаких выводов об относительной производительности запроса на основе стоимости (запросы с небольшой стоимостью могут выполняться несколько дней, а запросы с огромными стоимостями - миллисекунды).

Лучше бы мы эти стоимости вообще так явно не показывали...

Наконец-то мы разобрались с этой путаницей!!! Комментарий Роберта Вуда из Мемфиса, 14 июня 2002 года

Привет, Том!

Спасибо за глоток свежего воздуха в душном и отчужденном мире информации об Oracle!

Можешь ли ты подтвердить, да или нет...

Я понял, что ты хотел сказать следующее:

1. Значение стоимости не несет никакой информации.
2. Мы можем полностью игнорировать значение стоимости.

Ответ дяди Тома

Вобщем, нет. Я этого не говорил. Я сказал:

...Лучше бы мы эти стоимости вообще так явно не показывали...

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

Я думаю, мы тут все ошибаемся. Комментарий поклонника Oracle из США, 14 июня 2002 года

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

Ответ дяди Тома

Я согласен с одним вашим утверждением:

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

Это верно. Дальнейшие рассуждения ошибочны.

Я не знаю, как это объяснить.

Я буду кричать!

СТОИМОСТЬ НИКАК НЕ СВЯЗАНА СО СКОРОСТЬЮ ВЫПОЛНЕНИЯ ЗАПРОСА.

НЕЛЬЗЯ СРАВНИВАТЬ СТОИМОСТИ И УТВЕРЖДАТЬ НА ЭТОМ ОСНОВАНИИ, ЧТО "ЭТО БУДЕТ РАБОТАТЬ БЫСТРЕЕ"

ЭТО НЕ ОШИБКА, - ТАК И БЫЛО ЗАДУМАНО, ТАК ВСЕ И РАБОТАЕТ. ТАК БЫЛО СДЕЛАНО СПЕЦИАЛЬНО.

ВЫ ОБЪЯСНЯЕТЕ, КАК БЫ ВЫ ЭТО СДЕЛАЛИ, вместо того, чтобы понять, как это на самом деле сделано. (Между прочим, в моей книге рассмотрены реальные случаи - что бывает, когда предполагают одно, а на самом деле верно совсем другое.)

Я здаюсь. Если можете, объясните мне следующий результат. Запросыф делают одно и то же, они имеют одну и ту же стоимость. Почему же настолько различаются показатели производительности? (Подсказка - это не ошибка, повторяю, не ошибка. Ответ, ну, просто нельзя их сравнивать... стоимости эти.)

ops$tkyte@ORA817DEV.US.ORACLE.COM> variable x varchar2(25)
ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from big_table a
  2  where object_name like '%' || :x;

1101008 rows selected.

Elapsed: 00:05:21.46

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=54951 Bytes=5495100)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=431 Card=54951 
Bytes=5495100)
   2    1     INDEX (RANGE SCAN) OF 'OBJECT_NAME_IDX' (NON-UNIQUE) (Cost=52 
Card=54951)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1166039  consistent gets
      21649  physical reads
        256  redo size
  102352990  bytes sent via SQL*Net to client
    8147825  bytes received via SQL*Net from client
      73402  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1101008  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ full( a ) */ * from big_table a
  2  where object_name like '%' || :x;

1101008 rows selected.

Elapsed: 00:01:50.15

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=54951 Bytes=5495100)
   1    0   TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=431 Card=54951 
Bytes=5495100)




Statistics
----------------------------------------------------------
          0  recursive calls
         27  db block gets
      87396  consistent gets
      14409  physical reads
          0  redo size
  128246913  bytes sent via SQL*Net to client
    8147825  bytes received via SQL*Net from client
      73402  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1101008  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

Продолжение следует...

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

В следующем выпуске

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

С наилучшими пожеланиями,

  В.К.