Что такое план запроса в sql oracle
Перейти к содержимому

Что такое план запроса в sql oracle

  • автор:

Понимаем план выполнения запроса: анализ, определения и ключевые свойства

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

Понимаем план выполнения запроса: анализ, определения и ключевые свойства обновлено: 22 сентября, 2023 автором: Научные Статьи.Ру

Помощь в написании работы

Введение

План выполнения запроса – это структурированное описание того, как система управления базами данных (СУБД) будет выполнять запрос к базе данных. Он содержит информацию о порядке выполнения операций, используемых индексах, объединениях и других деталях запроса.

Нужна помощь в написании работы?

Мы — биржа профессиональных авторов (преподавателей и доцентов вузов). Наша система гарантирует сдачу работы к сроку без плагиата. Правки вносим бесплатно.

Что такое план выполнения запроса

План выполнения запроса – это структурированное описание того, как система управления базами данных (СУБД) будет выполнять запрос к базе данных. Он представляет собой набор операций, которые СУБД будет выполнять для получения результата запроса.

План выполнения запроса является важным инструментом для оптимизации производительности запросов. Он позволяет анализировать, как СУБД будет выполнять запрос, и идентифицировать возможные узкие места или проблемы производительности.

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

План выполнения запроса также может содержать информацию о стоимости выполнения каждой операции, такой как количество операций ввода-вывода или объем используемой памяти. Эта информация позволяет оценить, насколько эффективно будет выполняться запрос и какие ресурсы будут использоваться.

Цель анализа плана выполнения запроса

Цель анализа плана выполнения запроса состоит в том, чтобы оценить эффективность выполнения запроса и определить, какие ресурсы будут использоваться при его выполнении. Анализ плана выполнения запроса позволяет оптимизировать запросы и улучшить производительность базы данных.

Анализ плана выполнения запроса помогает ответить на следующие вопросы:

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

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

Как получить план выполнения запроса

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

Использование команды EXPLAIN

В большинстве СУБД (систем управления базами данных) существует команда EXPLAIN, которая позволяет получить план выполнения запроса. Например, в MySQL можно использовать команду EXPLAIN перед запросом, чтобы получить план выполнения:

EXPLAIN SELECT * FROM table_name;

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

Использование инструментов администрирования базы данных

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

Использование профилировщиков запросов

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

Использование инструментов трассировки запросов

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

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

Структура плана выполнения запроса

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

Корневой узел

Корневой узел является вершиной дерева плана выполнения запроса. Он представляет собой оператор верхнего уровня, который объединяет все остальные операторы и операции в плане выполнения запроса.

Операторы

Операторы представляют собой логические операции, которые выполняются для обработки данных в запросе. Например, оператор SELECT используется для выборки данных из таблицы, оператор JOIN используется для объединения данных из нескольких таблиц, а оператор GROUP BY используется для группировки данных по определенному столбцу.

Физические операции

Физические операции представляют собой конкретные действия, которые выполняются для обработки данных в запросе. Например, операция SCAN используется для сканирования всей таблицы, операция INDEX SEEK используется для поиска данных в индексе, а операция SORT используется для сортировки данных.

Статистика

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

Стоимость

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

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

Основные компоненты плана выполнения запроса

План выполнения запроса представляет собой дерево операторов, которые выполняются для получения результата запроса. Он состоит из нескольких основных компонентов:

Операторы доступа к данным

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

Операторы фильтрации

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

Операторы соединения

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

Операторы сортировки

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

Операторы агрегации

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

Операторы проекции

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

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

Анализ стоимости выполнения запроса

Анализ стоимости выполнения запроса – это процесс оценки затрат, необходимых для выполнения запроса в базе данных. Он позволяет определить, насколько эффективно будет выполнен запрос и какие ресурсы будут использованы.

Стоимость операций

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

Стоимость доступа к данным

Одним из основных факторов, влияющих на стоимость выполнения запроса, является стоимость доступа к данным. Это включает в себя стоимость сканирования таблиц, стоимость использования индексов и стоимость объединения данных из разных таблиц. Чем больше данных нужно просканировать или объединить, тем выше будет стоимость выполнения запроса.

Стоимость операций сортировки и группировки

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

Стоимость соединений и объединений

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

Оценка стоимости выполнения запроса

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

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

Оптимизация плана выполнения запроса

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

Шаги оптимизации плана выполнения запроса:

1. Анализ текущего плана выполнения запроса: В первую очередь необходимо проанализировать текущий план выполнения запроса, чтобы понять, какие операции выполняются и какие ресурсы используются. Это позволит выявить узкие места и проблемные операции, которые требуют оптимизации.

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

3. Индексирование: Создание или изменение индексов на таблицах может значительно улучшить производительность запросов. Индексы позволяют быстро находить нужные данные и ускоряют выполнение запросов.

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

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

6. Мониторинг и настройка: После внесения изменений в план выполнения запроса необходимо мониторить его производительность и настраивать параметры базы данных, если это необходимо. Мониторинг позволяет выявить проблемы и внести дополнительные изменения для оптимизации запросов.

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

Примеры анализа плана выполнения запроса

Анализ плана выполнения запроса является важным шагом в оптимизации производительности базы данных. Рассмотрим несколько примеров анализа плана выполнения запроса:

Пример 1: Простой SELECT запрос

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

SELECT * FROM employees WHERE department = 'IT';

План выполнения запроса может выглядеть следующим образом:

1. TABLE ACCESS FULL employees

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использован полный сканирование таблицы “employees”. Это означает, что все строки таблицы будут просмотрены для поиска строк, удовлетворяющих условию “department = ‘IT’”.

Анализ плана выполнения запроса позволяет нам понять, что данный запрос может быть неэффективным, особенно если таблица “employees” содержит большое количество строк. В таком случае, можно рассмотреть возможность добавления индекса на столбец “department”, чтобы ускорить выполнение запроса.

Пример 2: JOIN запрос

Рассмотрим следующий запрос с использованием оператора JOIN:

SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 50000;

План выполнения запроса может выглядеть следующим образом:

1. TABLE ACCESS FULL employees 2. TABLE ACCESS FULL departments 3. HASH JOIN

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использовано полное сканирование таблицы “employees” и таблицы “departments”. Затем будет выполнено объединение (JOIN) результатов двух таблиц с использованием хэш-соединения.

Анализ плана выполнения запроса позволяет нам понять, что данный запрос может быть неэффективным, особенно если таблицы содержат большое количество строк. В таком случае, можно рассмотреть возможность добавления индексов на столбцы “department_id” в обеих таблицах, чтобы ускорить выполнение запроса.

Пример 3: Использование индекса

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

SELECT * FROM employees WHERE employee_id = 100;

План выполнения запроса может выглядеть следующим образом:

1. INDEX UNIQUE SCAN employees_pk

В данном случае, план выполнения запроса показывает, что для выполнения запроса будет использован индекс “employees_pk” для поиска строки с указанным значением “employee_id”.

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

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

Сравнительная таблица планов выполнения запроса

Компонент Описание Пример
Операторы доступа Определяют, какие данные будут использоваться в запросе и как они будут получены Использование индекса для поиска данных в таблице
Операторы соединения Определяют, какие таблицы будут объединены в запросе и как они будут связаны Объединение таблиц по общему столбцу
Операторы фильтрации Определяют условия, которым должны соответствовать данные, чтобы быть включенными в результат запроса Выборка только тех строк, где значение столбца больше определенного значения
Операторы сортировки Определяют порядок, в котором данные будут отображаться в результате запроса Сортировка данных по возрастанию или убыванию значения столбца
Операторы группировки Определяют, как данные будут группироваться в результате запроса Группировка данных по значению определенного столбца
Операторы агрегации Определяют, какие агрегатные функции будут применены к данным в результате запроса Вычисление суммы или среднего значения столбца

Заключение

В данной лекции мы рассмотрели понятие плана выполнения запроса в базах данных. План выполнения запроса представляет собой оптимальную последовательность операций, которые выполняются для получения результата запроса. Анализ плана выполнения запроса позволяет определить эффективность выполнения запроса и выявить возможности для его оптимизации. Мы изучили структуру плана выполнения запроса и основные компоненты, такие как операторы доступа к данным, операторы объединения и операторы сортировки. Также мы рассмотрели анализ стоимости выполнения запроса и методы оптимизации плана выполнения запроса. Надеюсь, эта лекция помогла вам лучше понять планы выполнения запросов и их роль в оптимизации работы с базами данных.

Понимаем план выполнения запроса: анализ, определения и ключевые свойства обновлено: 22 сентября, 2023 автором: Научные Статьи.Ру

Анализ запроса SQL

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

Щелкните значок рядом с именем источника данных, в раскрывающемся меню выберите пункт «Свойства» и выберите ссылку Щелкните здесь на странице свойств.

План и анализ предназначены для использования специалистами по базам данных Oracle. Результаты позволяют узнать о возможном влиянии запроса на базу данных на основании размера таблицы и наличия индекса.

Для получения помощи по этой функции обратитесь к менеджеру учетной записи Responsys.

  • About Oracle
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights

ПЛАН ВЫПОЛНЕНИЯ SQL-ЗАПРОСА. ИНТЕРПРЕТАЦИЯ ОСНОВНЫХ ОПЕРАЦИЙ

План выполнения SQL-запроса, или план запроса, — это последовательность шагов или инструкций СУБД, необходимых для выполнения SQL-запроса. На каждом шаге операция, инициировавшая данный шаг выполнения SQL-запроса, извлекает строки данных, которые могут формировать конечный результат или использоваться для дальнейшей обработки. Инструкции плана выполнения SQL-запроса представляются в виде последовательности операций, которые ВЫПОЛНЯЮТСЯ СУБД ДЛЯ предложений SQL SELECT, INSERT, delete и update. Содержимое плана запроса, как правило, представляется древовидной структурой и включает в себя следующую информацию:

  • • порядок соединения источников данных (таблиц, представлений и т.п.);
  • • метод доступа для каждого источника данных;
  • • методы соединения источников данных;
  • • операции ограничения выбора данных, сортировки и агрегирования;
  • • стоимость и кардинальность каждой операции;
  • • возможное использование секционирования и параллелизма. Информация, предоставляемая планом выполнения SQL-запроса, позволяет разработчику увидеть, какие подходы и методы выбирает оптимизатор для выполнения SQL-операций.

Интерпретация плана выполнения SQL-запроса

Визуализация плана выполнения SQL-запроса зависит от инструментов и средств разработки, которые могут как входить в состав СУБД, запрос которой представляет интерес для анализа, так и являться отдельными коммерческими или свободно распространяемыми программными продуктами, не имеющими прямого отношения к конкретному производителю СУБД. Использование того или иного инструмента визуализации плана выполнения запроса, как правило, существенно не влияет на восприятие того, что описывает представленный план запроса. Определяющей в процессе анализа того, каким путем пойдет оптимизатор при выполнении конкретного запроса, является способность верно интерпретировать информацию, которая представлена в плане запроса.

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

  • • родитель может иметь одного или нескольких потомков;
  • • потомок имеет только одного родителя;
  • • операция, не имеющая родительской операции, является вершиной дерева;
  • • в зависимости от метода визуализации плана SQL-запроса потомок располагается с некоторым отступом относительно родителя. Потомки одного родителя располагаются на одинаковом расстоянии от своего родителя.

Рассмотрим более подробно информацию, представляемую планом выполнения SQL-запроса. Приведенные примеры выполнены в среде СУБД Oracle. В качестве инструмента выполнения запросов и визуализации плана SQL-запросов был использован Oracle SQL Developer. Фрагмент плана SQL-запроса представлен на рис. 10.11.

I Id I Operation

PR0DUCT_INF0RMATI0N_PK PRODUCT INFORMATION

SELECT STATEMENT SORT ORDER BY NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL INDEX UNIQUE SCAN TABLE ACCESS BY INDEX ROWID

Рис. 10.11. Фрагмент плана выполнения SQL-запроса в среде СУБД Oracle

Используя правила отношения операций плана запроса, можно определить следующее их формальное описание.

Операция 0 — корень дерева плана запроса. Корень имеет одного потомка: операция 1.

Операция 1 — операция имеет одного потомка: операция 2.

Операция 2 — операция имеет двух потомков: операция 3 и операция 6.

Операция 3 — операция имеет двух потомков: операция 4 и операция 5.

Операция 4 — операция не имеет потомков.

Операция 5 — операция не имеет потомков.

Операция 6 — операция не имеет потомков.

Взаимодействие родитель—потомок между операциями плана запроса представлено на рис. 10.12.

Операции, выполняемые в плане запроса, можно разделить на три типа: автономные, операции не связанного объединения и операции связанного объединения [15] (рис. 10.13).

Взаимодействие операций плана запроса

Рис. 10.12. Взаимодействие операций плана запроса

Типы операций плана SQL-запроса Автономные операции

Рис. 10.13. Типы операций плана SQL-запроса Автономные операции

Автономные операции — это операции, которые имеют не более одной дочерней операции.

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

  • 1. Дочерняя операция выполняется перед родительской операцией.
  • 2. Каждая дочерняя операция выполняется только один раз.
  • 3. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.14 представлен план следующего запроса:

SELECT o.order_id ,о.order_status FROM orders о ORDER BY о.order_status

Данный запрос содержит только автономные операции.

1 SELECT STATEMENT I

Рис. 10.14. Автономные

1 SORT ORDER BY |

операции, план запроса

1 TABLE ACCESS FULL I

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

  • 1. В соответствии с правилом следования автономных операций № 1 первой будет выполнена операция с Выполняется последовательное чтение всех строк таблицы orders.
  • 2. Далее выполняется операция с Выполняется сортировка строк, возвращаемых операцией с по условию предложения сортировки ORDER BY.
  • 3. Выполняется операция с Возвращается результирующий набор данных.

Операции несвязанного объединения

Операции несвязанного объединения — это операции, которые имеют более одной независимо выполняемой дочерней операции. Пример: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

Правила следования, по которым работают операции несвязанного объединения, можно сформулировать следующим образом.

  • 1. Дочерняя операция выполняется перед родительской операцией.
  • 2. Дочерние операции выполняются последовательно, начиная с наименьшего значения ID операции в порядке возрастания этих значений.
  • 3. Перед началом работы каждой следующей дочерней операции текущая операция должна быть выполнена полностью.
  • 4. Каждая дочерняя операция выполняется только один раз независимо от других дочерних операций.
  • 5. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.15 представлен план следующего запроса:

SELECT o.order_id from orders о UNION ALL

SELECT oi.order_id from order_items oi

Данный запрос содержит операцию несвязанного объединения UNION all. Остальные две операции являются автономными.

Рис. 10.15. Операции несвязанного объединения, план запроса

1 SELECT STATEMENT I

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

  • 1. В соответствии с правилами 1 и 2 следования операций несвязанного объединения первой будет выполнена операция с Выполняется последовательное чтение всех строк таблицы orders.
  • 2. В соответствии с правилом 5 операция с возвращает считанные на шаге 1 строки родительской операции с Операция с начнет выполняться, только когда закончится операция с После окончания выполнения операции с начинает выполняться операция с Выполняется последовательное чтение всех строк таблицы order_items.
  • 5. В соответствии с правилом 5 операция с возвращает считанные на шаге 4 строки родительской операции с Операция с формирует результирующий набор данных на основе данных, полученных от всех ее дочерних операций (с и Выполняется операция с Возвращается результирующий набор данных.

Таким образом, можно отметить, что операция независимого объединения последовательно выполняет свои дочерние операции.

Операции связанного объединения

Операции связанного объединения — это операции, которые имеют более одной дочерней операции, причем одна из операций контролирует выполнение остальных. Пример: nested loops, update.

Правила следования, по которым работают операции связанного объединения, можно сформулировать следующим образом.

  • 1. Дочерняя операция выполняется перед родительской операцией.
  • 2. Дочерняя операция с наименьшим номером операции (ID) контролирует выполнение остальных дочерних операций.
  • 3. Дочерние операции, имеющие общую родительскую операцию, выполняются, начиная с наименьшего значения ID операции в порядке возрастания этих значений. Остальные дочерние операции выполняются НЕ последовательно.
  • 4. Только первая дочерняя операция выполняется один раз. Все остальные дочерние операции выполняются несколько раз либо не выполняются совсем.

На рис. 10.16 представлен план следующего запроса:

FROM order_items oi, orders о

WHERE o.order_id= oi.order_id

AND о.customer_id between 100 and 1000

Данный запрос содержит операцию связанного объединения NESTED LOOPS.

I Id I Operation

BY INDEX R0UIDI

Рис. 10.16. Операции связанного объединения, план запроса

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

  • 1. В соответствии с правилами 1 и 2 следования операций связанного объединения первой должна быть выполнена операция с Однако операции с 1D = 2 и 1D = 3 являются автономными, и в соответствии с правилом 1 следования автономных операций первой будет выполнена операция с Выполняется просмотр диапазона индекса ORDCUSTOMERIX по условию: о . customer id between 100 and 1000.
  • 2. Операция с возвращает родительской операции (с Ш=2) список идентификаторов строк Rowld, полученных на шаге 1.
  • 3. Операция с выполняет чтение строк в таблице orders, в которых значение Rowld соответствует списку значений Rowld, полученных на шаге 2.
  • 4. Операция с возвращает считанные строки родительской операции (с Для каждой строки, возвращаемой операцией с выполняется вторая дочерняя операция (с операции nested loops. То есть для каждой строки, возвращаемой операцией с выполняется полный последовательный просмотр таблицы order_items с целью найти соответствие по атрибуту соединения.
  • 6. Шаг 5 повторяется столько раз, сколько строк возвращает операция с Операция с возвращает результаты работы родительской операции (с Выполняется операция с Возвращается результирующий набор данных.

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

SELECT с. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) as product_qty,

SUM(oi.quantity* oi.unit_price) as total_cost FROM oe.orders о INNER JOIN customers c ON

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. cust_first_name

Последовательность операций плана данного запроса представлена на рис. 10.17.

SELECT STATEMENT I

SORT GROUP BY ЇГ

TABLE ACCESS FULL

INDEX RANGE SCAN

TABLE ACCESS BY INDEX ROWIDd

TABLE ACCESS FULL

Рис. 10.17. План запроса, последовательность выполнения операций

Опишем возможный подход к интерпретации плана выполнения 80Ь-запроса, представленного на рис. 10.17. Данный подход включает в себя два основных этапа: декомпозиция операций на блоки и определение порядка выполнения операций.

На первом этапе необходимо выполнить декомпозицию выполняемых операций на блоки. Для этого находим все операции объединения, т.е. операции, которые имеют более одной дочерней операции (на рис. 10.17 это операции 2, 3 и 4), и выделяем эти дочерние операции в блоки. В результате, используя пример на рис. 10.17, получаем три операции объединения и семь блоков операций.

На втором этапе определяется последовательность выполнения блоков операций. Для этого необходимо применить правила следования операций, описанные выше. Выполним ряд рассуждений по вопросу выполнения каждой операции относительно ее идентификационного номера (Ш).

Операция Ш = 0 — автономная и является родительской для операции сШ = 1.

Операция Ю = 1 тоже автономная; является родительской для операции Ш = 2 и выполняется перед операцией Ю = 0.

Операция ГО = 2 — операция несвязанного объединения и является родительской для операций Ю = 3, Ю = 8. Операция ГО = 2 выполняется перед операцией ГО = 1.

Операция ГО = 3 — операция связанного объединения, является родительской для операций ГО = 4, ГО = 7. Операция ГО = 3 выполняется перед операцией ГО = 2.

Операция ГО = 4 — операция связанного объединения, является родительской для операций ГО = 5, ГО = 6. Операция ГО = 4 выполняется перед операцией ГО = 3.

Операция ГО = 5 — автономная операция, выполняется перед операцией ГО = 4.

Операция ГО = 6 — автономная операция, выполняется перед операцией ГО = 5.

Операция ГО = 7 —автономная операция, выполняется после выполнения блока операций «С».

Операция ГО = 8 — автономная операция, выполняется после блока операций «Е».

На основе проведенных рассуждений и правил следования сформулируем последовательность выполняемых операций:

  • 1. Первой выполняется автономная операция ГО = 5, см. правила следования операций связанного объединения. Выполняется последовательное чтение всей таблицы.
  • 2. Результат операции ГО = 5 — считанные строки таблицы — передается операции ГО = 4.
  • 3. Выполняется операция ГО = 4: для каждой строки, возвращенной операцией ГО = 5, выполняется операция ГО = 6. То есть выполняется сканирование диапазона индекса по атрибуту соединения. Получение списка идентификаторов строк Яоу1с1.
  • 4. Результат операции ГО = 4 передается операции ГО = 3. То есть передается список идентификаторов строк Кош1с1.
  • 5. Выполняется операция ГО = 3: для каждого значения 11оу1с1, возвращенного в результате работы блока операций «С», выполняется операция ГО = 7, т.е. выполняется чтение строк таблицы по заданному списку идентификаторов строк ИтмЫ, полученных после выполнения операции Ш = 4.
  • 6. Выполняется автономная операция ГО = 8 — последовательное чтение всей таблицы.
  • 7. Выполняется операция несвязанного объединения ГО = 2: выполняется соединение хэшированием результатов работы блоков операций «Е» и «Е».
  • 8. Результат операции ГО = 2 передается операции ГО = 1.
  • 9. Выполняется операция несвязанного объединения ГО = 1: выполняется агрегирование и сортировка данных, полученных в результате работы операции ГО = 2.
  • 10. Выполняется операция ГО = 0. Возвращается результирующий набор данных.

Правила следования, сформулированные для основных типов операций, применимы для большинства планов выполнения БСГО-запроса. Однако существуют конструкции, используемые в БСГО-запросах, которые предполагают нарушение порядка выполнения операций, описанных в правилах следования. Такие ситуации могут появляться в результате использования, например, подзапросов или предикатов антисоединения. В любом случае процесс интерпретации плана выполнения БСГО-запроса не предполагает только использование ряда правил, которые обеспечат именно максимально верный анализ того, что собирается делать оптимизатор при выполнении 8СГО-запроса. Очередной БСГО-запрос — это всегда индивидуальный случай; и то, как он будет выполнен в СУБД, зависит от множества факторов, среди которых версия СУБД, версия и тип операционной системы, на которой развернут экземпляр СУБД, используемая аппаратная часть, квалификация автора 80Ь-запроса и т.д.

Приемы работы с планами выполнения запросов в Oracle

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

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

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

Меня зовут Дан Хотка (Dan Hotka). Я директор Oracle ACE. Одной из моих привилегий в этой группе является помощь в распространении информации и полезных технических знаний, связанных с СУБД Oracle. Меня хорошо знают после моих 12 (скоро 14) опубликованных книг и буквально сотен статей. Я регулярно пишу в блоге и собираюсь делать это в дальнейшем. Мы даже могли встречаться на одном из событий или встреч группы пользователей. Я регулярно выступаю на эти темы по всему миру.
Я собираюсь поделиться с вами как техническими знаниями про Oracle, так и тем, как эти знания применяются в решениях Embarcadero.

Я скачал себе «большую тройку» продуктов Embarcadero: Rapid Sql, DBArtisan, DB PowerStudio. Сейчас я хотел бы рассказать о первом впечатлении и некоторых приемах работы с планами выполнения запросов в RapidSQL. (Я установил версию 8.6.1)
Я покажу пару приемчиков для планов выполнения запросов в и вокруг Rapid SQL.
Мне нравится инструмент. Конечно, это прекрасный инструмент, если у вас есть разные типы СУБД различных производителей, поскольку этот инструмент поддерживает около дюжины разных СУБД. Единый интерфейс для освоения всех БД! Мои приемчики относятся к Oracle. Но приемы для инструментов Embarcadero должны сработать вне зависимости от того, к какой СУБД вы подключились.
При просмотре планов выполнения я люблю видеть план выполнения и сам запрос одновременно.
Этого легко достигнуть.
Для начала, загрузите свой SQL запрос в окно редактора ISQL (используя кнопку Open), затем включите кнопку Explain Plan (отмечена в красном круге). Кнопка останется активированной.

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

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

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

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

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

  • базы данных
  • sql
  • oracle database
  • explain plan
  • Rapid SQL
  • embarcadero
  • embarcadero technologies
  • DB Tools
  • Блог компании «Embarcadero (Borland)»
  • Oracle
  • SQL

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *