Explain plan oracle описание. Понимание результатов Execute Explain Plan в Oracle SQL Developer

Сразу уточню, что описывать буду на примере использования фри утилиты OraDeveloper Studio. Почему? Потому что обычными запросами этого сделать не удалось, а времени и желания разбираться не было, раз уж есть способ проще. 😉

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

Задача — грузить в базу десятки тысяч строк данных. Для каждой строки необходимо предварительно по базе найти дополнительные данные одним довольно громоздким запросом (4 таблицы через джойны).
Проблема — загрузка 15 тысяч строк занимает 8-9 часов. Так как по условиям задачи загружать надо часто, а не один раз в пятилетку… В общем, надо довести время до приемлемого.

Что я сделал?
1. Выяснил, что тормозит именно селект (данные вставляются и обновляются в таблицах, где куча строк и часть из таблиц не имеет ни индексов, ни ключей — отсюда и сомнения в вине селекта).
2. Проверил наличие индексов на используемых запросом полях. Добавил отсутствующие.
3. Спросил помощи у знающих. 🙂

Знающие посоветовали проанализировать план выполнения запроса и объяснили, как это сделать в OraDev.
Создаём новое окно запроса (Ctrl+N). Копируем в него наш запрос. Жмём Alt+G. Выбираем уже существующую либо создаём новую таблицу плана.
После выполнения появится дерево плана выполнения. Самостоятельно и без поллитры в нём разобраться не так просто. 😉

Что же нас интересует в этом дереве? Нас интересуют узлы (шаги), для которых указан большой Cost шага. Цену шага вы можете увидеть в свойствах шага (у меня окошко свойств постоянно открыто и потому мне надо лишь выбрать нужный шаг; вам же может потребоваться выбирать свойства по правому клику на шаге). Отыскиваем медленный шаг (самый верхний узел, корень дерева плана, в расчёт особенно не берём — там будет указана общая цена запроса, а мы итак уже знаем, что проблема именно в этом запросе). Нашли? Теперь смотрим, с какой таблицей, какими её полями и с каким количеством строк работает шаг — это есть в свойствах и имени шага. Смотрим и думаем, почему у нас так медленно?
У меня, например, один из шагов работал с 4000 записей вместо одной-трёх записей (не тысяч). Такого быть не должно было в принципе — я же ограничиваю выборку именно для того, чтобы выбирать из нужного диапазона, а не из кучи лишнего барахла. Внимательно присмотревшись к условию джойна, я заметил, что упустил одно из полей. Добавил поле в запрос и всё встало на свои места. Цена запроса (полная) уменьшилась с 531 до 6. 🙂

Спасибо камрадам nest и detect за помощь.

P.S. Извините, что не приводу скриншоты. С ними было бы намного нагляднее, но… Из-за конфиденциальности некоторой информации пришлось бы замазывать 80% и тогда опять вышло бы малопонятно.
P.P.S. Общее время загрузки существенно сократилось. На загрузку в базу 17.5 тысяч строк данных ушло 12 минут. В сравнении с 8-9 часами… Ну, вы и сами всё уже поняли. 😉

5 ответов

Вывод EXPLAIN PLAN - это отладочный вывод оптимизатора запросов Oracle. COST - это конечный результат оптимизатора затрат (CBO), целью которого является выбор того, какой из множества возможных планов должен использоваться для запуска запроса. CBO рассчитывает относительную стоимость для каждого плана, затем выбирает план с самой низкой стоимостью.

(Примечание: в некоторых случаях у CBO не хватает времени для оценки всех возможных планов, в этих случаях он просто выбирает план с самой низкой стоимостью, найденной до сих пор)

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

Например, скажем, у вас есть следующий запрос:

SELECT emp_id FROM employees WHERE months_of_service = 6;

(Столбец months_of_service имеет ограничение NOT NULL на нем и обычный индекс на нем.)

Есть два основных плана, которые может выбрать оптимизатор здесь:

План 1: Прочитайте все строки из таблицы "сотрудники", для каждого проверьте, является ли предикат истинным (months_of_service=6).
План 2. Прочитайте индекс, где months_of_service=6 (это приводит к набору ROWID), затем получите доступ к таблице на основе возвращенных ROWID.

Предположим, что таблица "сотрудники" содержит 1 000 000 (1 миллион) строк. Пусть далее представьте, что значения для months_of_service варьируются от 1 до 12 и по какой-то причине довольно равномерно распределены.

Стоимость Plan 1 , которая включает в себя FULL SCAN, будет стоить чтение всех строк в таблице сотрудников, что примерно равно 1 000 000; но поскольку Oracle часто может считывать блоки с использованием многоблочных чтений, фактическая стоимость будет ниже (в зависимости от того, как настроена ваша база данных) - например, предположим, что количество отсчетов с несколькими блоками равно 10 - расчетная стоимость полного сканирования составит 1,000,000/10; Общая стоимость = 100 000.

Стоимость Плана 2 , которая включает в себя сканирование INDEX RANGE SCAN и поиск таблицы по ROWID, будет стоить сканирование индекса, а также затраты на доступ к таблице с помощью ROWID. Я не буду вдаваться в то, как сканирование индексов диапазона будет стоить, но пусть представьте, что стоимость сканирования индекса индекса составляет 1 на строку; мы ожидаем найти совпадение в 1 из 12 случаев, поэтому стоимость сканирования индекса составляет 1,000,000/12 = 83,333; плюс стоимость доступа к таблице (предположим, что 1 блок считывается за доступ, мы не можем использовать многоблочные чтения здесь) = 83,333; Общая стоимость = 166 666.

Как вы можете видеть, стоимость плана 1 (полное сканирование) меньше, чем стоимость плана 2 (индексная проверка + доступ по rowid) - это означает, что CBO будет выбирать ПОЛНОЕ сканирование.

Если предположения, сделанные здесь оптимизатором, верны, то на самом деле план 1 будет предпочтительным и гораздо более эффективным, чем План 2 - который опровергает миф о том, что ПОЛНЫЕ сканирования "всегда плохие".

Результаты были бы совершенно разными, если целью оптимизатора было FIRST_ROWS (n) вместо ALL_ROWS - в этом случае оптимизатор предпочтет план 2, потому что он будет часто возвращать первые несколько строк быстрее, ценой менее эффективной для весь запрос.

CBO строит дерево решений, оценивая затраты на каждый возможный путь выполнения, доступный для каждого запроса. Затраты устанавливаются параметром CPU_cost или I/O_cost, установленным в экземпляре. И CBO оценивает затраты, насколько это возможно, с существующей статистикой таблиц и индексов, которые будут использоваться в запросе. Вы не должны настраивать свой запрос, основываясь только на стоимости. Стоимость позволяет понять, ПОЧЕМУ оптимизатор делает то, что он делает. Без затрат вы могли бы понять, почему оптимизатор выбрал план, который он сделал. Более низкая стоимость не означает более быстрый запрос. Есть случаи, когда это верно, и будут случаи, когда это неправильно. Стоимость основана на вашей таблице статистики, и если они ошибаются, стоимость будет неправильной.

При настройке запроса вы должны взглянуть на мощность и количество строк каждого шага. Имеют ли они смысл? Оптимизатор считается корректным? Правильно ли возвращаются строки. Если информация присутствует неправильно, то, скорее всего, оптимизатор не имеет надлежащей информации, необходимой для принятия правильного решения. Это может быть связано с устаревшими или отсутствующими статистическими данными в таблице и индексе, а также cpu-stats. Лучше всего обновлять статистику при настройке запроса, чтобы максимально использовать возможности оптимизатора. Знание вашей схемы также очень помогает при настройке. Зная, когда оптимизатор выбрал действительно плохое решение и указал его на правильный путь с помощью небольшого подсказки, можно сэкономить время.

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

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

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

Итак, если чтение одного блока занимает 2 мс, а стоимость выражается как "250", запрос может потребоваться для завершения 500 мс.

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

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

Как долго выполняется чтение одного блока.
Как долго многозадачное чтение берет
Насколько велико многоблочное чтение (часто отличное от максимально возможного из-за того, что размер таблицы меньше максимального значения и другие причины).
Производительность процессора

Эти цифры могут сильно различаться в зависимости от операционной среды системы, и различные статистические данные могут храниться для операций "дневного OLTP" и операций "ночной периодической отчетности", а для "отчетов о конце месяца", если вы хотите.

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

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

Обратите внимание, что стоимость не обязательно настенное время, так как операции параллельного запроса потребляют общее количество времени для нескольких потоков.

В старых версиях Oracle стоимость операций с ЦП была проигнорирована, а относительная стоимость одно- и многоблочных чтений была эффективно исправлена в соответствии с параметрами init.

Вы также можете запросить v $sql и v $session для получения статистики о операторах SQL, и это будет иметь подробные показатели для всех видов ресурсов, таймингов и исполнений.

Это как гвоздь в подошве любимого ботинка. Ходить можно, но все чаще ловишь себя на желании остаться на месте или перепоручить дело другим. Мелкие неудобства не только замедляют нашу работу, но и снижают мотивацию, вносят помехи в процесс, снижают качество результата. И если нашелся друг, который научил вас взять молоток и забить этот гвоздь, вы не только будете благодарны ему за помощь, но и сами поможете другим, избавив их от мелкой, но очень раздражающей помехи. Для этого и нужно общаться, делиться не только глубокими и сокровенными знаниями в форумах и на сайтах вроде Хабра, но и своими простыми трюками и «маленькими хитростями»

Как и любой текст, запросы и программы на SQL можно создавать в любом текстовом редакторе. Но если вы профессионал, вы очень много и часто работаете с SQL, то вам уже не будет достаточно наличия подсветки синтаксиса и автоматического переформатирования кода, особенно, если вам приходится переключаться между различными версиями одной СУБД или разными платформами СУБД.

Недавно мне случилось общаться с одним из ведущих профессионалов СУБД Oracle. Он рассказал много интересного про работу с планами выполнения запросов в различных версиях этой СУБД и не постеснялся рассказать всем об используемых им инструментах, приемах и дать немного полезных мелких советов. Я сделал перевод одной из статей в его блоге и хотел бы предложить его вниманию Хабравчан. Несмотря на то, что описанный прием применялся для работы с Oracle, я теперь с успехом применяю тот же подход для MS SQL и Sybase.

Запустите запрос на выполнение, и появится закладка Query Plan, заполненная планом выполнения.

Поместите курсор мыши на любой из узлов на диаграмме и появится дополнительная полезная информация, относящаяся к этому шагу выполнения из плана запроса!
По умолчанию, Rapid SQL показывает план выполнения в графическом виде. Я вышел из старого мира оптимизации…. Предпочитаю текстовую версию, поэтому нажимаю правую кнопку мыши в окне с планом и выбираю “View as Text”.
Предпочитаю видеть текст запроса и план одновременно.

Это легко сделать. Видите закладки окон ISQL внизу главного окна? Для начала мы должны настроить Rapid SQL, чтобы он выдавал план в отдельном окне.

Нажмите кнопку Options (левый красный кружок) и затем установите опцию ‘Unattached’ для Result window. Это приведет к созданию двух отдельных закладок внизу Rapid SQL, после запуска запроса на выполнение. Просто протащите немного это окно за закладку и появится прямоугольник, куда можно переместить это окно.
Или можно воспользоваться пунктом Tile windows из главного меню программы

И еще: все это так же работает и в DBArtisan - решении для администраторов баз данных.