Уважаемые подписчики! Это очередной выпуск, посвященный одному из вопросов, которые комментировал 16 июня 2002 года Том Кайт на сайте asktom.oracle.com. Вопросы про интерпретацию стоимости запроса, выдаваемой оптимизатором Oracle, задают часто...
Я использую стоимостной оптимизатор. У меня есть два запроса, скажем, запрос 1 и запрос 2. Стоимость выполнения запроса один - около 400, а стоимость выполнения запроса 2 - 200. Но времена выполнения находятся практически в обратной пропорции - запрос 1 выполняется быстрее, чем запрос 2. Я проверил это с помощью команды set timing on в sqlplus.
Мне это кажется несколько странным - стоимость выше, а выполняется запрос быстрее. Связана ли стоимость с временем выполнения? По какому критерию настраивать запрос для максимальной производительности (минимального времени отклика)?
Нельзя сравнивать друг с другом стоимости двух запросов. Они просто не сопоставимы.
При получении запроса сервер оценивает множество планов его выполнения. С каждым шагом плана связывается некоторая относительная стоимость. Затем вычисляется функция, определяющая суммарную стоимость запроса для каждого плана. Эти стоимости, (доступные только оптимизатору) можно сравнивать, поскольку они вычислены для одного и того же запроса в одинаковой среде.
Если взять другой запрос, пусть даже подобный, мы проходим через те же стадии - строим множество планов, оцениваем стоимость каждого из них, выбираем план с наименьшей стоимостью из этого множества.
Нельзя, однако, взять эти два запроса и сравнивать относительные стоимости одного с другим. Стоимости строго привязаны к запросам.
На "стоимость" запроса влияет много факторов. "Стоимость" - это просто искусственное значение, используемое для выбора плана выполнения в определенной среде. Берем запрос и получаем его стоимость. Теперь, добавляем подсказку оптимизатору, при которой запрос будет выполняться быстрее, и определяем новую стоимость - она может быть выше, а может быть и ниже, чем прежняя. Почему? Потому что в среде с установленной подсказкой изменяются внутренние значения стоимости, присваиваемые различным операциям, в попытке представить вашу подсказку "лучшим" способом выполнения запроса. Возможно, для этого общая уточненная стоимость затребованного плана была сделана ниже, чем у плана, сгенерированного без подсказки, а возможно план без подсказки сделан слишком дорогостоящим (его стоимость была искусственно завышена).
Не сравнивайте эти стоимости, это недопустимо - с таким же успехом эти стоимости могли бы быть случайными числами.
Если стоимости двух запросов или даже одного запроса с разными планами выполнения (с подсказкой и без) нельзя сравнивать, на основании чего строить стратегии настройки 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. Вы просто использовали подсказку для искусственного завышения стоимостей других планов доступа, используемых внутренне стоимостным оптимизатором. Подсказки работают путем манипулирования внутренними стоимостями, в результате чего предложенный способ доступа выглядит предпочтительнее по сравнению с другими.
Если более низкая стоимость приводит к более быстрому выполнению, стоимостной оптимизатор и так с этим разберется, без вашей помощи!
Вернемся к стоимостям. Я пытаюсь определить стоимость запроса в 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.
Том,
Твое разъяснение очень полезно. Но у меня осталась еще пара вопросов.
1) да.
2) Стоимость - это набор чисел, присваиваемых оптимизатором разным шагам выполнения запроса на основе имеющейся статистики. Затем эти стоимости объединяются для получения общей стоимости запроса - одного числа, представляющего относительную "стоимость" плана выполнения запроса. Затем оптимизатор выбирает для запроса план с минимальной стоимостью. Теоретически, это самый быстрый способ выполнения запроса (это и была цель, в конечном итоге). В действительности так бывает не в 100% случаев.
Привет, Том!
Ты сказал, что нельзя сравнивать стоимости двух запросов, но можно ли сравнивать
стоимости одного и того же запроса, но с разными значениями optimizer_mode, например, first_rows и all_rows????
Если, например, select * from emp в режиме first_rows имеет стоимость 10, а в режиме all_rows - стоимость 20, значит ли это, что режим first_rows лучше?
Нет. Нет. Нет. И еще раз - нет.
Режим оптимизации- это средство для изменения (намеренного) стоимости, связанной с различными шагами.
Именно это и показывает ваш пример. Изменив цель оптимизации, мы увеличили стоимость некоторых процессов, которые были "дешевыми" в случае оптимизации выбора первых строк (например, доступ по индексу для первых строк - "дешевый"). Оптимизация выбора всех строк делает его "более дорогостоящим". С учетом этого, тот же план может получить уже другую стоимость! Ну и как можно сравнивать один с другим?
Нельзя сравнивать стоимости запросов, даже если они "идентичны".
Нельзя делать никаких выводов об относительной производительности запроса на основе стоимости (запросы с небольшой стоимостью могут выполняться несколько дней, а запросы с огромными стоимостями - миллисекунды).
Лучше бы мы эти стоимости вообще так явно не показывали...
Привет, Том!
Спасибо за глоток свежего воздуха в душном и отчужденном мире информации об Oracle!
Можешь ли ты подтвердить, да или нет...
Я понял, что ты хотел сказать следующее:
В общем, нет. Я этого не говорил. Я сказал:
...Лучше бы мы эти стоимости вообще так явно не показывали...
они полезны при правильном использовании (например, когда пытаются повлиять
на оптимизатор с помощью подсказок и хотят понять, почему оптимизатор
выбирает такой план, а не другой).
Сравнение одного и того же запроса по стоимости вполне допустимо, поскольку для этого стоимость и вычисляется. Предполагается, что оптимизатор генерирует все возможные комбинации и выбирает план с наименьшей стоимостью. Если можно переписать тот же 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
Продолжение следует...
Вот такие бывают горячие дискуссии. Оригинал этого обсуждения можно найти здесь. Некоторые несущественные детали и комментарии читателей не переведены.
Следующий выпуск будет посвящен обзору и классификации материалов, которые будут размещаться в рассылке, и, конечно же, ответам дяди Тома. Он выйдет через пару дней.
С наилучшими пожеланиями,
В.К.
|
|