План выполнения SQL запроса Oracle. План запроса оптимизация oracle

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

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

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

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

Например, предположим, что у вас есть следующий запрос: (. Колонку months_of_service имеет NOT NULL ограничение на него и обычный индекс на нем)

SELECT emp_id FROM employees WHERE months_of_service = 6;

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

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

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

Стоимость План 1, в котором используется ПОЛНЫЙ СКАНИРОВАНИЕ, будет стоить чтение всех строк в таблице сотрудников, что примерно равно 1 000 000; но поскольку Oracle часто может считывать блоки с использованием многоблочных чтений, фактическая стоимость будет ниже (в зависимости от того, как настроена ваша база данных) - например, давайте представим, что количество отсчетов с несколькими блоками равно 10 - расчетная стоимость полного сканирования составит 1,000,000/10; Общая стоимость = 100 000.

Стоимость плана 2, который включает в себя УКАЗАТЕЛЬ диапазон сканирования и просмотра таблицы по 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, потому что он будет часто возвращать первые несколько строк быстрее, ценой менее эффективной для всего запроса.

stackoverrun.com

oracle - План выполнения запросов Oracle

Я немного запутался в плане выполнения запроса Oracle. Это в Oracle Enterprise Edition 11.2.0.1.0 на платформе IBM AIX 6.1. У меня есть таблица TEST1 (1 миллион строк) и другая таблица TEST2 (50 000 строк). Обе таблицы имеют одинаковые столбцы. Существует представление, созданное как объединение этих двух таблиц. Я запускаю запрос в этом представлении с индексированным столбцом в предложении WHERE. Я могу найти, что индекс не используется и отображается полное сканирование таблицы. С небольшим изменением запроса он начал использовать индекс. Мне интересно, как это изменение может привести к изменению плана.

Ниже вы найдете полный DDL + DML. Я привел упрощенный пример. Фактическая схема и требования немного сложнее. Фактически рассматриваемый запрос динамически строится и выполняется генератором кода OCI. Мое намерение здесь заключается не в том, чтобы получить альтернативы, а в том, чтобы понять, что может быть логическим аргументом в изменении плана (между, я программист приложений, а не администратор базы данных). Ваша помощь очень ценится.

DROP TABLE TEST1 CASCADE CONSTRAINTS ; DROP TABLE TEST2 CASCADE CONSTRAINTS ; CREATE TABLE TEST1 ( ID NUMBER(20) NOT NULL, NAME VARCHAR2(40), DAY NUMBER(20) ) PARTITION BY RANGE (DAY) ( PARTITION P001 VALUES LESS THAN (2), PARTITION P002 VALUES LESS THAN (3), PARTITION P003 VALUES LESS THAN (4), PARTITION P004 VALUES LESS THAN (5), PARTITION P005 VALUES LESS THAN (6), PARTITION P006 VALUES LESS THAN (7), PARTITION P007 VALUES LESS THAN (8), PARTITION P008 VALUES LESS THAN (9), PARTITION P009 VALUES LESS THAN (10), PARTITION P010 VALUES LESS THAN (11), PARTITION P011 VALUES LESS THAN (12), PARTITION P012 VALUES LESS THAN (13), PARTITION P013 VALUES LESS THAN (14), PARTITION P014 VALUES LESS THAN (15), PARTITION P015 VALUES LESS THAN (16), PARTITION P016 VALUES LESS THAN (17), PARTITION P017 VALUES LESS THAN (18), PARTITION P018 VALUES LESS THAN (19), PARTITION P019 VALUES LESS THAN (20), PARTITION P020 VALUES LESS THAN (21), PARTITION P021 VALUES LESS THAN (22), PARTITION P022 VALUES LESS THAN (23), PARTITION P023 VALUES LESS THAN (24), PARTITION P024 VALUES LESS THAN (25), PARTITION P025 VALUES LESS THAN (26), PARTITION P026 VALUES LESS THAN (27), PARTITION P027 VALUES LESS THAN (28), PARTITION P028 VALUES LESS THAN (29), PARTITION P029 VALUES LESS THAN (30), PARTITION P030 VALUES LESS THAN (31) ) ; CREATE INDEX IX_ID on TEST1 (ID) INITRANS 4 STORAGE(FREELISTS 16) LOCAL ( PARTITION P001, PARTITION P002, PARTITION P003, PARTITION P004, PARTITION P005, PARTITION P006, PARTITION P007, PARTITION P008, PARTITION P009, PARTITION P010, PARTITION P011, PARTITION P012, PARTITION P013, PARTITION P014, PARTITION P015, PARTITION P016, PARTITION P017, PARTITION P018, PARTITION P019, PARTITION P020, PARTITION P021, PARTITION P022, PARTITION P023, PARTITION P024, PARTITION P025, PARTITION P026, PARTITION P027, PARTITION P028, PARTITION P029, PARTITION P030 ) ; CREATE TABLE TEST2 ( ID NUMBER(20) PRIMARY KEY NOT NULL, NAME VARCHAR2(40), DAY NUMBER(20) ) ; CREATE OR REPLACE VIEW TEST_V AS SELECT ID, NAME, DAY FROM TEST1 UNION SELECT ID, NAME, DAY FROM TEST2 ; begin for count in 1..1000000 loop insert into test1 values(count, 'John', mod(count, 30) + 1) ; end loop ; end ; / begin for count in 1000000..1050000 loop insert into test2 values(count, 'Mary', mod(count, 30) + 1) ; end loop ; end ; / commit ; set lines 300 ; set pages 1000 ; -- Actual query explain plan for SELECT Key FROM ( WITH recs AS ( SELECT * FROM TEST_V WHERE ID = 70000 ) ( SELECT 1 AS Key FROM recs WHERE NAME = 'John' ) UNION ( SELECT 2 AS Key FROM recs WHERE NAME = 'Mary' ) ) ; select * from table(dbms_xplan.display()) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) | Time | Pstart | Pstop | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1611K | 4721K | | 13559 (1) | 00:02:43 | | | | 1 | VIEW | | 1611K | 4721K | | 13559 (1) | 00:02:43 | | | | 2 | TEMP TABLE TRANSFORMATION | | | | | | | | | | 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6610_34D3B6C | | | | | | | | |* 4 | VIEW | TEST_V | 805K | 36M | | 10403 (1) | 00:02:05 | | | | 5 | SORT UNIQUE | | 805K | 36M | 46M | 10403 (8) | 00:02:05 | | | | 6 | UNION-ALL | | | | | | | | | | 7 | PARTITION RANGE ALL | | 752K | 34M | | 721 (1) | 00:00:09 | 1 | 30 | | 8 | TABLE ACCESS FULL | TEST1 | 752K | 34M | | 721 (1) | 00:00:09 | 1 | 30 | | 9 | TABLE ACCESS FULL | TEST2 | 53262 | 2496K | | 68 (0) | 00:00:01 | | | | 10 | SORT UNIQUE | | 1611K | 33M | 43M | 13559 (51) | 00:02:43 | | | | 11 | UNION-ALL | | | | | | | | | |* 12 | VIEW | | 805K | 16M | | 1429 (1) | 00:00:18 | | | | 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_34D3B6C | 805K | 36M | | 1429 (1) | 00:00:18 | | | |* 14 | VIEW | | 805K | 16M | | 1429 (1) | 00:00:18 | | | | 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6610_34D3B6C | 805K | 36M | | 1429 (1) | 00:00:18 | | | ------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("ID"=70000) 12 - filter("NAME"='John') 14 - filter("NAME"='Mary') -- Modified query (only change is absence of outermost SELECT) explain plan for WITH recs AS ( SELECT * FROM TEST_V WHERE ID = 70000 ) ( SELECT 1 AS Key FROM recs WHERE NAME = 'John' ) UNION ( SELECT 2 AS Key FROM recs WHERE NAME = 'Mary' ) ; select * from table(dbms_xplan.display()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 88 | 6 (67) | 00:00:01 | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6611_34D3B6C | | | | | | | | 3 | VIEW | TEST_V | 2 | 96 | 4 (50) | 00:00:01 | | | | 4 | SORT UNIQUE | | 2 | 96 | 4 (75) | 00:00:01 | | | | 5 | UNION-ALL | | | | | | | | | 6 | PARTITION RANGE ALL | | 1 | 48 | 1 (0) | 00:00:01 | 1 | 30 | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST1 | 1 | 48 | 1 (0) | 00:00:01 | 1 | 30 | |* 8 | INDEX RANGE SCAN | IX_ID | 1 | | 1 (0) | 00:00:01 | 1 | 30 | | 9 | TABLE ACCESS BY INDEX ROWID | TEST2 | 1 | 48 | 1 (0) | 00:00:01 | | | |* 10 | INDEX UNIQUE SCAN | SYS_C001242692 | 1 | | 1 (0) | 00:00:01 | | | | 11 | SORT UNIQUE | | 4 | 88 | 6 (67) | 00:00:01 | | | | 12 | UNION-ALL | | | | | | | | |* 13 | VIEW | | 2 | 44 | 2 (0) | 00:00:01 | | | | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_34D3B6C | 2 | 96 | 2 (0) | 00:00:01 | | | |* 15 | VIEW | | 2 | 44 | 2 (0) | 00:00:01 | | | | 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_34D3B6C | 2 | 96 | 2 (0) | 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("ID"=70000) 10 - access("ID"=70000) 13 - filter("NAME"='John') 15 - filter("NAME"='Mary') quit ;

спасибо и привет, Reji

qaru.site

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

Oracle 12c: введение в SQL 1:

Введение в базу данных Oracle 12c Oracle 12c: введение в SQL 1: Обзор основных возможностей БД Oracle 12c Обсуждение основных концепций, а также теоретических и физических аспектов реляционной базы данных

Подробнее

Использование памяти

Использование памяти Темы Использование оперативной памяти разными операциями Ограничение размера выделяемой памяти Временные файлы 2 Для чего нужна память? Сортировка ORDER BY, подготовка к соединению

Подробнее

Oracle Database 11g: Введение в SQL.

Продолжительность обучения 40 часов. Oracle Database 11g: Введение в SQL. Для успешного усвоения материала курса, слушатель должен обладать следующими знаниями и умениями: Общее представление о реляционной

Подробнее

3.1. Структура запросов

Лекция 3. Язык SQL. Средства манипулирования данными 3.1. Структура запросов Для того, чтобы можно было более или менее точно рассказать про структуру запросов в стандарте SQL/89, необходимо начать со

Подробнее

Запускаем параллельный SQL

Запускаем параллельный SQL Владимир Прушковский CacheConf Введение в SQL Введение в SQL Основные компоненты The Unified Data Dictionary Репозиторий мета-данных (Class Definition) Все хранимые классы автоматически

Подробнее

Firebird 3.0: первое знакомство

Firebird 3.0: первое знакомство Дмитрий Еманов [email protected] Firebird Project http://www.firebirdsql.org/ Приоритеты разработки Первичные цели Поддержка современного «железа» в многопоточном ядре

Подробнее

Подзапросы и предикаты

Подзапросы и предикаты 1 Проблема Требование реляционной модели суперпозиция реляционных операций SELECT обеспечивает ее лиши частично 2 Решение Раннее решение использовать представления CREATE [OR ALTER]

Подробнее

Новые возможности Btree

Новые возможности Btree Лубенникова А.В. Фролков И.А. www.postgrespro.ru Общий принцип Чем меньше данных тем лучше Вырожденный случай одна строка в одной таблице с одной колонкой Так не бывает :-) Почему?

Подробнее

MS Создание запросов в Microsoft SQL Server 2012

MS-10774 Создание запросов в Microsoft SQL Server 2012 Прод олжит ельн о сть ку рса: 40 академических часов Аттестация: удостоверение о повышении квалификации установленного образца (или сертификат ТПУ)

Подробнее

ПРИМЕНЕНИЕ ЯЗЫКА SQL В MS ACCESS

Московский государственный технический университет имени Н. Э. Баумана Калужский филиал Ю. Е. Гагарин, С. В. Пономарев ПРИМЕНЕНИЕ ЯЗЫКА SQL В MS ACCESS Учебно-методическое пособие УДК 681.3.06 ББК 32.973

Подробнее

Procedural Structured Query Language

Procedural Structured Query Language Курс «Базы данных» Вадим Цесько Санкт-Петербургский государственный политехнический университет 29 марта 2012 г. Вадим Цесько (СПбГПУ) Procedural Structured Query Language

Подробнее

МЕТОДЫ ПРОГРАММИРОВАНИЯ. SQL

МЕТОДЫ ПРОГРАММИРОВАНИЯ. SQL Практикум для студентов механико-математмческого факультета специальности 1-31 03 01 «Математика» (по направлениям) МИНСК БГУ 2007 УДК 004.6(075.8) ББК 32.973.26-018.2я73 М54

Подробнее

Введение. Что потребуется для выполнения

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

Подробнее

1С-Битрикс: Управление сайтом 7.x

1С-Битрикс: Управление сайтом 7.x Руководство по управлению каталогом товаров (без использования модуля Торговый каталог) Содержание Введение...3 Глава 1. Создание торгового каталога «Книги»...4 СОЗДАНИЕ

Подробнее

Оглавление. Предисловие...3

Оглавление Предисловие...3 Ч а с т ь I. Базы данных, СУБД и модели данных Глава 1. Назначение технологии баз данных. Функции и основные компоненты систем управления базами данных..........................................

Подробнее

Простейшие конструкции языка SQL

Простейшие конструкции языка SQL Дмитрий Барашев 13 сентября 2016 г. Computer Science Center Этот материал распространяется под лицензией Creative Commons Attribution - Share Alike 3.0 http://creativecommons.org/licenses/by-sa/3.0/us/deed.ru

Подробнее

Я.Е. ПРОКУШЕВ БАЗЫ ДАННЫХ

1 Я.Е. ПРОКУШЕВ БАЗЫ ДАННЫХ практикум для студентов, обучающихся по специальностям и направлениям подготовки 09.00.00 «Информатика и вычислительная техника», 10.00.00 «Информационная безопасность», а также

Подробнее

docplayer.ru