Насколько точен EXPLAIN PLAN от Oracle?

Существуют ли хорошие способы объективного измерения производительности запросов в Oracle 10g? Есть один конкретный запрос, который я настроил для несколько дней. У меня есть версия, которая работает быстрее (по крайней мере, судя по моим первоначальным тестам), но стоимость EXPLAIN примерно такая же.

  1. Насколько вероятно, что в стоимости EXPLAIN чего-то не хватает?
  2. Существуют ли особые ситуации, когда стоимость EXPLAIN непропорционально отличается от фактической производительности запроса?
  3. Я использовал подсказку first_rows в этом запросе. Оказывает ли это влияние?

sql oracle sql-execution-plan oracle10g
person Jason Baker    schedule 06.05.2009    source источник

Ответы (4)


arrow_upward
12
arrow_downward

Насколько вероятно, что в стоимости EXPLAIN чего-то не хватает?

Маловероятно. На самом деле это будет баг 1 уровня :)

На самом деле, если ваша статистика значительно изменилась с момента запуска EXPLAIN, фактический план запроса будет другим. Но как только запрос будет скомпилирован, план останется прежним.

Примечание EXPLAIN PLAN может показать вам то, что вероятно произойти, но может никогда не произойти в реальном запросе.

Например, если вы запустите EXPLAIN PLAN для иерархического запроса:

SELECT  *
FROM    table
START WITH
        id = :startid
CONNECT BY
        parent = PRIOR id

с индексами id и parent вы увидите дополнительный FULL TABLE SCAN, которого, скорее всего, не будет в реальной жизни.

Используйте STORED OUTLINE для хранения и повторного использования плана, несмотря ни на что.

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

Да, это происходит очень и очень часто на сложных запросах.

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

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

Вот конкретная ситуация, о которой вы спрашивали: HASH JOIN, например, потребуется несколько проходов по probe table, если хеш-таблица не помещается в pga_aggregate_table, но что касается Oracle 10g, я не помню, чтобы это когда-либо принималось во внимание CBO.

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

Я использовал подсказку first_rows в этом запросе. Оказывает ли это влияние?

Эта подсказка заставит оптимизатор использовать план с более низким время отклика: он вернет первые строки как можно быстрее, несмотря на то, что общее время запроса больше.

Практически это почти всегда означает использование NESTED LOOP вместо HASH JOIN.

NESTED LOOP имеют более низкую общую производительность на больших наборах данных, но они быстрее возвращают первые строки (поскольку не нужно строить хеш-таблицу).

Что касается запроса из вашего исходный вопрос< /a> см. мой ответ здесь< /сильный>.

person Quassnoi    schedule 06.05.2009
comment
+1, xlnt, как обычно, проницательный ответ. Итак, вы спите с исходным кодом Oracle RDBMS под подушкой или что-то в этом роде? ;-) - person DCookie; 06.05.2009
comment
@DCookie: нет, меня просто укусил Том Кайт, когда мне было 8 лет :) - person Quassnoi; 06.05.2009
comment
При более сложных запросах будет ли оптимизатор рассматривать все возможные пути или он примет прагматичный вариант отсечки, допуская, что потенциально может пропустить лучшее решение? Я до сих пор скучаю по старому оптимизатору, основанному на правилах. Вы знали, где вы были с оптимизатором, основанным на правилах (бормотание, бормотание)... - person Mike Woodhouse; 06.05.2009
comment
@Mike: с более сложными запросами оптимизатор просто отстой. Я лично намекаю на каждый запрос, который будет выполняться более 1 секунды. Что касается CBO, то с CBO вы можете делать все то же, что и с RBO, и даже больше. Подробнее см. здесь: stackoverflow.com/questions/493492/ - person Quassnoi; 06.05.2009
comment
Я начал читать этот ответ и подумал, что это чертовски хорошо, держу пари, это Quassnoi. И я был прав! - person Mark Harrison; 07.05.2009
comment
Иногда вам нужно взглянуть на v$sql_plan на основе sql_id выполняемого запроса, чтобы увидеть, какой план он фактически использовал. Иногда план, который он говорит, что он будет использовать при запуске плана объяснения, не является планом, который он в конечном итоге выбирает. Я сейчас работаю с такой проблемой. Я вижу оба плана, я вижу разницу, но я не могу убедить его не использовать MERGE JOIN CARTESIAN. - person Grant Johnson; 04.06.2010
comment
Не могли бы вы объяснить дополнительное полное сканирование таблицы соединения по запросу: в каком случае оно будет выполняться? (погуглил, и это единственная ссылка, которую я нашел) - person vmatyi; 14.12.2014
comment
@vmatyi: извините, я не могу. Он просто появляется там, но, насколько я могу судить, никогда не вступает в игру. Вероятно, это некая запасная стратегия, которую оптимизатор мог бы использовать, если сочтет это целесообразным. К сожалению, внутренности оптимизатора Oracle не раскрывает во всех подробностях. - person Quassnoi; 15.12.2014

arrow_upward
6
arrow_downward

Вопрос. Существуют ли хорошие способы объективного измерения производительности запросов в Oracle 10g?

  • Трассировка Oracle — лучший способ измерения производительности. Выполните запрос и позвольте Oracle инструментировать выполнение. В среде SQLPlus использовать AUTOTRACE очень просто.

http://asktom.oracle.com/tkyte/article1/autotrace.html (статья перемещена)
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671636641855

И включить трассировку Oracle в других средах не так уж и сложно.

Вопрос. Есть один конкретный запрос, над которым я работал несколько дней. У меня есть версия, которая работает быстрее (по крайней мере, судя по моим первоначальным тестам), но стоимость EXPLAIN примерно такая же.

  • Фактическое выполнение оператора — это то, что необходимо измерить. EXPLAIN PLAN неплохо предсказывает план оптимизатора, но на самом деле не измеряет производительность.

Вопрос> 1 . Насколько вероятно, что в стоимости EXPLAIN чего-то не хватает?

  • Маловероятно, но я видел случаи, когда EXPLAIN PLAN предлагает план, отличный от оптимизатора.

Вопрос> 2 . Существуют ли особые ситуации, когда стоимость EXPLAIN непропорционально отличается от фактической производительности запроса?

  • Краткий ответ заключается в том, что я не наблюдал ни одного. Но опять же, на самом деле нет прямой корреляции между стоимостью EXPLAIN PLAN и фактической наблюдаемой производительностью. EXPLAIN PLAN может дать очень большое значение стоимости, но фактическое выполнение запроса займет меньше секунды. EXPLAIN PLAN не измеряет фактическую производительность запроса, для этого вам нужна трассировка Oracle.

Вопрос> 3 . Я использовал подсказку first_rows в этом запросе. Оказывает ли это влияние?

  • Любая подсказка (например, /*+ FIRST_ROWS */) может повлиять на выбор плана оптимизатором.

«Стоимость», возвращаемая EXPLAIN PLAN, является относительной. Это показатель производительности, но не точный ее показатель. Вы не можете преобразовать значение стоимости в количество дисковых операций, количество секунд ЦП или количество событий ожидания.

Обычно мы обнаруживаем, что оператор со стоимостью EXPLAIN PLAN, показанной как 1, будет выполняться «очень быстро», а оператор со стоимостью EXPLAIN PLAN порядка пяти или шести цифр займет больше времени для выполнения. Но не всегда.

Оптимизатор сравнивает множество возможных планов выполнения (полное сканирование таблицы, использование индекса, соединение с вложенным циклом и т. д.). Оптимизатор присваивает номер каждому плану, а затем выбирает план с наименьшим номером.

Я видел случаи, когда план оптимизатора, показанный EXPLAIN PLAN, НЕ соответствует фактическому плану, используемому при выполнении оператора. Я видел это десять лет назад в Oracle8, особенно когда оператор включал переменные связывания, а не литералы.

Чтобы получить фактическую стоимость выполнения инструкции, включите трассировку для своей инструкции. Проще всего это сделать с помощью SQLPlus AUTOTRACE.

[http://asktom.oracle.com/tkyte/article1/autotrace.html][4]

Вне среды SQLPlus вы можете включить трассировку Oracle:

    alter session set timed_statistics = true;
    alter session set tracefile_identifier = here_is_my_session;
    alter session set events '10046 trace name context forever, level 12'
    --alter session set events '10053 trace name context forever, level 1'
    select /*-- your_statement_here --*/ ...
    alter session set events '10046 trace name context off'
    --alter session set events '10053 trace name context off'

Это помещает файл трассировки в каталог user_dump_dest на сервере. Созданный файл трассировки будет содержать план оператора И все события ожидания. (Назначенный идентификатор файла трассировки включен в имя файла и упрощает поиск файла в каталоге udump)

    select value from v$parameter where name like 'user_dump_dest'

Если у вас нет доступа к файлу трассировки, вам потребуется помощь администратора базы данных, чтобы получить доступ. (Администратор базы данных может создать простой сценарий оболочки, который разработчики могут запускать с файлом .trc для запуска tkprof и изменять разрешения для файла трассировки и вывода tkprof. Вы также можете использовать более новый trcanlzr. обе.

person spencer7593    schedule 15.05.2009

arrow_upward
1
arrow_downward

Насколько я знаю, EXPLAIN использует некоторую статистику базы данных для расчета стоимости, поэтому она может определенно отличаться от фактической производительности.

person haggai_e    schedule 06.05.2009

arrow_upward
0
arrow_downward

По моему опыту, EXPLAIN была точной и полезной. Если бы это было не так, возможно, это был бы не тот полезный инструмент, которым он является. Когда вы в последний раз анализировали таблицы? Я видел, где план объяснения был почти одинаковым до и после анализа, но анализ дал огромный прирост производительности.

person northpole    schedule 06.05.2009