Oracle cost cardinality что это
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 4 years ago .
This is a simple query: select * from customers
When I write this query in PL/SQL Developer and press F5, I see Explain Plan, but I don't know what are Cost, Cardinality and Bytes represent.
Исключение NO_DATA_NEEDED
(NO_DATA_NEEDED Exception)
Конвейерная табличная функция может создать больше данных, чем необходимо запросившему её процессу. Когда такое происходит, конвейерная табличная функция останавливает выполнение, порождая исключение NO_DATA_NEEDED. Оно не должно явно обрабатываться, если только в процедуру не включен обработчик исключений OTHERS.
Приведенная ниже функция возвращает 10 строк, но запрос потребовал от нее только первые 5 строк. В этом случае функция прекращает выполнение, вызывая исключение NO_DATA_NEEDED.
Если имеется обработчик исключений OTHERS, то он захватит исключение NO_DATA_NEEDED и выполнит некоторый код обработки ошибок, что не нужно.
Если вы планируете использовать обработчик исключений OTHERS, то для исключения NO_DATA_NEEDED необходимо задействовать специальное прерывание.
Исключение NO_DATA_NEEDED может быть также использовано для выполнения операций очистки (cleanup).
Проблемы с хинтами в запросе
Проблемы с хинтами могут быть следующие:
- Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
— хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
— в хинте USE_NL содержится не полный перечень алиасов;
— в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
— хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте). - В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
- При создании хинта в запросе есть ряд рекомендаций:
— В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
— При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
— Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
— Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.
Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.
1. При первом разборе происходит полный разбор запроса (hard parse)
План запроса помещается в глобальный кэш БД с определенным sql_id
2. При повторном выполнении происходит частичный разбор (soft parse)
Происходит только синтаксический разбор, проверки прав доступа и проверки bind переменных. Что для разных вариаций sql_id создает дочерние child_sql_id
Из-за такого механизма работы Oracle вытекает частая проблема oltp систем, где существует огромное число маленьких запросов, отличающихся друг от друга только фильтрами или параметрами. Это приводит к быстрому вытеснению планов из кэша и их последующему повторному hard parse.
В итоге может оказаться, что большую часть времени БД занимается разбором запросов, а не собственно их выполнением.
Отсюда вывод: по возможности используйте bind переменные в вариациях одного запроса, замен константных фильтров, т.к. это даст нам только один план запроса (child_sql_id) при разных значениях переменных на равномерно распределенном столбце.
Я не зря сказал ранее "на равномерно распределенном столбце", т.к. с bind переменными есть проблема: по умолчанию Oracle не знает какие данные будут переданы в запрос и из-за этого может сгенерить неверный план запроса.
Посмотрим на примере по умолчанию. Создадим таблицу с неравномерно распределенным столбцом "n" (9 строк со значением = 1, и 1млн-9 строк со значением 2):
Столбец не имеет гистограмм, но есть статистика по уникальным значениям. Построим план запроса с bind переменной = 1:
Oracle закономерно ожидает в результате половину таблицу и выбирает full scan, хотя мы то знаем, что тут был бы лучше Index scan.
К счастью с 11 версии Oracle может заглядывать в значения bind переменных и подбирать под них нужные планы.
Для этого соберем гистограмму с 2 вершинами и повторим эксперимент:
Oracle сгенерировал новый child_sql_id под новое значение bind переменной и выбрал правильный доступ по индексу.
Данный план был закеширован в глобальную память и если прямо сейчас выполнить заново с параметром 2, то мы получим тотже план (child number 2).
Замечу что на этом этапе уже надо смотреть план уже выполненного запроса, т.к. oracle не умеет показывать план и заглядывать в bind переменные, но при реальном выполнении запроса значения bind переменных смотрятся.
но oracle пометит этот запрос на пересмотр, т.к. план совсем не сошелся с реальными данными и при последующем применении сгенерирует новый child_sql_id (child number 3) под нашу bind переменную:
Из всего этого можно сделать вывод, что вопреки частому заблуждению, Oracle умеет генерировать правильные планы по bind переменным, но делает это не сразу, а при повторном вызове и при наличии гистограммы.
Второе: реальный план запроса с bind переменными можно узнать только во время или после выполнения запроса, т.к. "explain plan" не подсматривает в bind переменные.
Проблемы с индексами
Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:
- Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
- Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
— Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
— Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим; - Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
— Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
— Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
— Имеется хинт, блокирующий работу индекса, например NO_INDEX.
— Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1. - Имеются сильные индексы, но они соперничают между собой.
Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов. - Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:
Фактор кластеризации для индекса считает во время сбора статистики. Он используется оптимизатором при расчете стоимости индексного доступа к данным таблицы. Большой ФК (особенно близкий к числу строк в таблице) говорит о неэффективном индексе. Таким образом, ФК является характеристикой индекса, а не таблицы. Первое решение при большом ФК является убрать существующий индекс как не эффективный. Второе решение, если данный индекс наиболее часто применяется в запросах и он нужен, то перестроить структуру таблицы таким образом, чтобы строки в блоках таблицы были упорядочены в том же порядке, в котором расположена информация по данным строкам в индексе, т.е. сделать кластерными блоки таблицы, уменьшив таким образом число перемещений от одного блока к другому при работе индекса.
2 Answers 2
See section 12.10 for a description of the plan table columns.
Cost is the amount of work the optimizer estimates to run your query via a specific plan. The optimizer generally prefers lower cost plans.
Cost is determined by several different factors but the table statistics are one of the largest.
Cardinality is the number of rows the optimizer guesses will be processed for a plan step. If the stats are old, missing, or incomplete - then this can be wildly wrong. You want to look for where the optimizer sees 5 rows (cardinality) but in reality there are 50,000 rows.
Bytes are same concept as cardinality but in sheer terms of data to be processed vs rows in a table.
This is an extremely deep topic that requires active learning and experience. I'm sure many can contribute ideas for places to go. I personally enjoy Maria's blog. She's the former product manager for the optimizer.
The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query. Figure 8: Cost is found in the fifth column of the execution plan The cost of the entire plan (indicated on line 0) and each individual operation is displayed in the execution plan. However, it is not something that can be tuned or changed. The cost is an internal unit and is only displayed to allow for plan comparisons.
You can also look in the Database Performance Tuning Guide, where it says:
The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.
So - COST is a dimensionless value which is a function of CPU and IO cost.
Табличные функции используются для возврата PL/SQL-коллекций, которые имитируют таблицы. Они могут быть запрошены как обычные таблицы с помощью функцию TABLE во фразе FROM. Обычные табличные функции требуют, чтобы коллекции перед возвращением были полностью наполнены (населены). Так как коллекции хранятся в памяти, это может стать проблемой, поскольку на большие коллекции впустую тратится много памяти и времени в ожидании возвращения первой строки. Эти узкие возможности делают обычные табличные функции непригодными в случаях масштабных ETL-операций (ETL — Extraction Transformation Load — Извлечение-Преобразование-Загрузка). Обычные табличные функции требуют создания именованной строки и табличных типов как объектов базы данных.
Заметим, что в этом листинге строки перечислены в обратном порядке, поскольку запрос содержит фразу упорядоченности по убыванию.
Oracle 12: Адаптивные планы
Смена плана во время выполнения запроса. На основе кол-ва данных полученных из шагов запроса генерируется оптимальный план следующего шага.
Адаптивность может сменить:
* Тип соединения NL HJ
* тип parallel distribution
* Bitmap Index Pruning
В теории этого достаточно, чтобы ускорить запрос в разы.
Но есть ряд ситуаций в которых параллельность наоборот мешает.
Для начала разберемся с терминологией - посмотрим на параллельный план с join 2 таблиц:
- Запрос 1
Таблица T2 имеет особенность: fk_id_skew неравномерно заполнен и имеет перекос в сторону 1 - она встречается значительно чаще других.
- Запрос 2
Итак, выполнил простой запрос:
- Запрос 3
* regexp_replace в этом запросе нужен, чтобы данные отбирались не мгновенно и были видны в статистике затраты CPU.
* Хинты вставлены, чтобы запрос в плане выглядел также как написан тут.
Время выполнения выполнения запроса = 49сек.
Добавим хинт parallel(8) замен no_parallel.
Время выполнения = 8с, что в 6 раз быстрей.
Разберем для понимания план запроса:
- План 1
Основопологающие фазы:
* PX BLOCK ITERATOR - чтение таблицы частями в несколько потоков
* PX SEND - 1 поток посылает данные другому. Важно знать, что только один producer (PX SEND) может быть активен в одно время, что накладывает ограничения на параллельный план выполнения, подробней: Вторая часть по распределению данных в параллельных запросах
** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
** HASH - диапазон данных на основе их хэша (hash join, group by)
** RANDOM - случайная отправка
** BROADCAST - отправка таблицы во все потоки (часто на маленькой таблице, совместно с последующей ROUND ROBIN правой таблицы. Может быть проблемой производительности, если левая таблица значительно больше, чем указано в статистике, т.к. данные дублируются во все потоки)
** ROUND ROBIN - данные отправляются в потоки по кругу
Про способы распределения данных по потокам нужно поговорить отдельно:
Стоит заметить, что данные бьются по значениям в столбцах строк, а не просто по строкам.
Это нужно, чтобы один и тотже диапозон данных из разных таблиц попал в один поток для join.
Если бы Oracle делал не так, то в 1 поток могли бы попасть совершенно разные данные и join нельзя было бы совершить.
На это стоит обратить внимание, т.к. это может являться и причиной замедлений выполнения параллельного запроса при сильном перекосе данных (О причинах замделенния параллельных запросов дальше)
** P->P - данные из одной параллельной группы передаются в другую параллельную группу
** P->S - параллельность в последовательное выполнение (узкое место или конец запроса - вторая из основных причин замедления параллельного запроса)
** PCWP - параллельность с родителем: сканируем таблицу и сразу делаем join с другой
** PCWC - наоборот: передаем фильтр из внешнего потока и применяем при сканировании
* PX RECEIVE - получение данных из одного параллельного потока в другой
* PX SEND QC - отправка данных координатору
* PX COORDINATOR - приемник всех параллельных запросов
* TQ - Номер потока
1. Наличие в плане события "P->S - параллельность в последовательное выполнение", кроме перед "PX COORDINATOR"
Это говорит нам о том, что Oracle вынужден был собрать все потоки в одну последовательность (sequence), что дало бутылочное горлышко ожидания выполнения самого долго потока всеми остальными.
Приведу пример с rownum. Добавим отбор номера строки из каждой таблицы:
План поменялся,
* для расчета COUNT rownum параллельный процесс чтения таблицы с диска "PX BLOCK ITERATOR" выстраивается в очередь "P->S", что сводит на нет все перимещуство распределенного чтения.
* теперь JOIN не выполняется в отдельном потоке ( :TQ10002 )
т.к. оба потока уже были раньше преобразованы в последовательный набор данных и не могут использоваться одновременно.
Как следствие, время выполнения запроса стало даже больше ( 51 с ), чем не параллельная версия (49 с ) из-за лишних издержек на поддержку параллельности, которая не используется
2. PX SEND skew - Перекос данных
при формировании диапозонов данных в один из потоков.
Продемонстрировать это просто используя заранее созданный перекошенный столбец t_2.fk_id_skew.
Если выполнить запрос, но для join таблиц использовать условие: t_2.fk_id_skew = t_1.id
То общий план параллельного запроса не поменяется (см. План 1 ), но вот время выполнения возрастет до 38с.
Причина кроется в том, что в колонке t_2.fk_id_skew кроется 1 500 000 значений = 1 и 3 500 000 остальных. И при выполнении "PX SEND HASH" большая часть строк таблицы попадают в один поток для обработки, вместо того, чтобы равномерно распределиться.
Это хорошо видно в статистике выполнения. Для просмотра воспользуемся функцией "DBMS_SQLTUNE.REPORT_SQL_MONITOR".
Нас интересуют вкладки Parallel и Activity:
Рис.1 - Большую часть времени запрос выполнялся в один поток, остальные потоки его ждали.
Рис. 2. - Это же подтверждается на вкладке PARALLEL: 37c от общего времени работал 1 поток.
Oracle поступает верно, т.к. нельзя же сделать join данных из разных диапазонов.
Для сравнения взгляните статистику выполнения для хорошо распараллеленого запроса (План 1) с условием без перекосов t_2.fk_id_uniform = t_1.id
Рис. 3 и Рис. 4
Все выполнялось в 8 потоков и каждый поток равномерно обработал только свою равную часть.
3. Bloom filters
Не вдаваясь в механику создания битовых векторов bloom filter опишу преимущество их использования.
Пример запроса:
1. На таблице T1 с фильтром "filter("T1"."MOD"=42)" создается bloom filter - PX JOIN FILTER CREATE
2. Фильтр из п.1 применяется на таблицу T2 - PX JOIN FILTER USE
Тем самым ограничивая размер правой таблицы.
3. Отфильтрованная таблица T2 соединяется с HASH JOIN BUFFERED
Подробное описание bloom filter: Bloom filter
bloom filter хороши в:
* Параллельных запросах - уменьшается количество передаваемых данных между потоками за счет предфильтрации правой таблицы
* RAC системах - уменьшает число передаваемых по сети данных между нодами
* InMemory таблицах - осуществляя inmemory предфильтрацию таблицы и осуществление не inmemory join на быстро отфильтрованной правой таблице
4. Partition Wise
В целом похоже на предыдущий пункт, но фильтр накладывается на партиции правой таблицы, также уменьшая сканирования.
Дедектируется в плане по фразам:
* PART JOIN FILTER CREATE ( :BF0000 ) - создание фильтра на левой таблице
* Pstart| Pstop = :BF0000|:BF0000 - применение фильтра в операции чтения правой таблицы PCWC
Конвейерные табличные функции, запускаемые параллельно
(Parallel Enabled Pipelined Table Functions)
Чтобы включить параллельные конвейерные табличные функции, должны быть выполнены следующие условия.
- Должна быть включена фраза PARALLEL_ENABLE.
- Должен присутствовать один или более входных параметров REF CURSOR.
- Должна иметь место фраза PARTITION BY, чтобы указать метод секционирования рабочей нагрузки. Слабо связанные ref-курсоры (weakly typed ref cursors) могут использовать только фразу PARTITION BY ANY, которая определяет секционирование рабочей нагрузки случайный образом.
Основной синтаксис показан ниже.
Чтобы увидеть это в действии, сначала надо создать и населить тестовую таблицу.
Следующий пакет определяет включенные параллельные конвейерные табличные функции, которые принимают ref-курсоры по запросу из тестовой таблицы и возвращают те же самые строки вместе с SID (системный идентификатор) сессии, которая их обработала. Можно было бы использовать слабо связанный ref-курсор, подобный SYS_REFCURSOR, но тогда мы были бы ограничены только методом секционирования SYS_REFCURSOR. Следующие три функции представляют три метода секционирования.
Следующий запрос использует функцию CURSOR, чтобы преобразовать запрос к тестовой таблице в ref-курсор, который передан табличной функции в качестве параметра. Результаты группируются по SID сессий, которые обрабатывают строки. Отметим, что все строки обработаны одной и той же сессией. Почему? Потому что, хотя эта функция включена как параллельная, мы не указали ей работать параллельно.
Следующие запросы включают хинт параллельности и вызывают все эти функции.
Уровень параллелизма (DOP — degree of parallelism) может быть ниже чем тот, который указан в хинте.
Дополнительная фраза управления выходным потоком быть использована для упорядочения или кластеризации (объединение в группы) — order or cluster — данных, основанной на списке столбцов, в процессе серверной обработки. Это может быть необходимым, если существуют зависимости в данных. Например, нужно секционировать по определенному столбцу, но также и требовать, чтобы строки были обработаны в определенном порядке в рамках этого же секционирования. Расширенный синтаксис такого случая показан ниже.
Можно сделать нечто подобное:
Dynamic Sampling
Ora Blog
Применимо для запросов со сложными предикатами фильтрации, которые дают существенную ошибку оптимизатора.
Включение dynamic sampling в зависимости от параметра пробирует от 32 блоков таблицы на предикаты фильтрации и определяем реальный лучший план.
Анализ плана выполнения запроса.
Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса
- При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
- Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
- Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
- Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
- Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.
Трансформация конвейеров
(Transformation Pipelines)
В традиционных ETL-процессах необходимо сначала загрузить данные в промежуточную область, затем сделать по ней несколько проходов, чтобы преобразовать и переместить данные в область, откуда они будут загружены в схему назначения. Прохождение данных через промежуточные таблицы может потребовать значительного количества операций дискового ввода/вывода, как для загружаемых данных, так и для данных redo-журнала. Альтернативой должно стать выполнение преобразования конвейерными табличными функциями, поскольку данные читаются из внешней таблицы и вставляются непосредственно в таблицу назначения, сокращая большую часть операций дискового ввода/вывода.
В этой секции мы увидим и проэкзаменуем с использованием обсуждавшихся ранее методов трансформацию конвейера.
Сначала в виде плоского файла нужно выкачать из файловой системы сервера базы данных какие-либо тестовые данные.
Создаем объект "directory", где указывается местоположение этого файла, создаем внешнюю таблицу, чтобы прочитать файл, и создаем таблицу назначения.
Заметим, что в таблице назначения по сравнению с внешней таблицей есть два дополнительных столбца. Каждый из этих столбцов представляет шаг преобразования. Фактические преобразования в этом примере тривиальны, но следует представить, что они могут быть сложными и невыполнимыми одним SQL-предложением. Следовательно, имеет место потребность в табличных функциях.
Пакет ниже определяет два шага процесса преобразования и процедуры для его запуска.
Вставка внутри процедуры LOAD_DATA полностью выполняет загрузку данных, включая преобразования. Предложение выглядит довольно сложно, но оно состоит из следующих простых шагов.
- Строки запрашиваются из внешней таблицы.
- Строки конвертируются в ref-курсор с помощью функции CURSOR.
- Этот ref-курсор передается на первом этапе преобразования (STEP_1).
- Возвращаемая на шаге STEP_1 коллекция запрашивается, когда используется функция TABLE.
- Вывод этого запроса преобразуется в ref-курсор с помощью функции CURSOR.
- Этот ref-курсор передается на второй этап преобразования (STEP_2).
- Возвращаемая на шаге STEP_2 коллекция запрашивается, когда используется функция TABLE.
- Этот запрос используется для выполнения вставки в результирующую таблицу.
Применяя процедуру LOAD_DATA, можно как преобразовывать, так и загружать данные.
Заметим, что этот пример не содержит процедуры обработки ошибок и что в нем нет хинтов параллельности, чтобы упростить запрос в процедуре LOAD_DATA.
В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer.
Нередко возникают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Назовем такие запросы неэффективными или ресурсоемкими.
Причины ресурсоемкости запроса могут быть следующие:
- плохая статистика по таблицам и индексам запроса;
- проблемы с индексами в запросе;
- проблемы с хинтами в запросе;
- неэффективно построенный запрос;
- неправильно настроены параметры инициализации базы данных, отвечающие за производительность запросов.
Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:
- средства, позволяющие получить предполагаемый план выполнения запроса;
- средства, позволяющие получить реальный план выполнения запроса;
К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:
Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже приводиться пример плана выполнения запроса:
полученного в Toad
Из плана видно, что наибольшие значения Cost и Cardinality содержатся во 2-й строке, в которой и надо искать основные проблемы производительности запроса.
Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:
- CPU Cost — процессорная стоимость выполнения;
- IO Cost — стоимость ввода-вывода;
- Temp Space – показатель использования дискового пространства.
Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.
Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:
Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:
Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).
План выполнения запроса получается из представления Oracle по запросу:
где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:
Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:
где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса
Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof (при определенном навыке анализировать можно без расшифровки, в этом случае имеем более детальную информацию).
Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.
Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:
Все строки (кроме текста запроса) являются стандартными.
Далее запуск запрос, который выдает на экран текст рекомендаций:
Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;
В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.
Сравнение использования памяти
(Memory Usage Comparison)
Следующая функция возвращает текущее значение определенной статистики. Она позволит нам сравнивать память, используемую обычными и конвейерными табличными функциями.
Сначала мы протестируем обычную табличную функцию, создав новое соединение и запросив большую коллекцию. Проверяя выделение памяти PGA как до, так и после, тест позволит нам увидеть, сколько памяти было выделено в результате проведения теста.
Затем мы повторим тест для конвейерной табличной функции.
Сокращение памяти, используемой конвейерной табличной функцией, обусловлено тем, что она не требует разместить целую коллекцию в памяти.
Cardinality feedback
Ora Blog
Oracle мониторит и исправляет следующии "estimated rows" оценки на основе реальных "actual rows"
* Single table cardinality (after filter predicates are applied)
* Index cardinality (after index filters are applied)
* Cardinality produced by a group by or distinct operator
Неэффективная статистика.
Прежде чем оптимизировать запрос, целесообразно посмотреть статистику таблиц и индексов, участвующих в запросе. Порой достаточно обновить статистику, чтобы запрос стал работать эффективно. Возможные варианты не эффективной статистики, приводящие к ресурсоемкости запроса:
- Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами: - для таблицы:
- для индекса:
где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.
Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:
Процент сбора статистики по индексу находиться по запросу
Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.
Замечание. При хорошем значении статистики по таблице может быть неблагополучная статистика по какому-то индексу таблицы, в силу чего целесообразно отслеживать статистику не только таблицы, но и индексов таблицы.
Конвейерные табличные функции)
(Pipelined Table Functions
Конвейерная обработка отменяет надобность в создании огромных наборов, передавая строки по каналу из функции по мере их создания, сохраняя память и позволяя запустить последующую обработку еще до окончания генерации всех строк.
Конвейерные табличные функции включают фразу PIPELINED и используют вызов PIPE ROW, чтобы вытолкнуть строки из функции, как только они создадутся, вместо построения табличной коллекции. Заметим, что вызов RETURN пустой, поскольку нет никакой коллекции, возвращаемой из функции.
Когда ETL-операции проводятся на большом хранилище данных, наблюдается существенное повышение производительности, поскольку загрузка данных из внешних таблиц производится табличными функциями непосредственно в таблицы хранилища, избегая промежуточного размещения данных.
Неявные (теневые) типы
(Implicit (Shadow) Types)
В отличие от обычных табличных функций, конвейерные табличные функции могут быть определены с использованием типов "table" и "record", определенных в спецификации пакета.
Это представляется более правильным решением, чем построение всех типов базы данных вручную, а Oracle по умолчанию строит теневые объектные типы неявно.
Как можно видеть, Oracle фактически создал три теневых объектных типа с системно сгенерированными именами для поддержки типов, требуемых конвейерной табличной функцией. По этой причине я всегда строю именованные объектные типы базы данных, вместо того, чтобы полагаться на неявные типы.
Оптимизация запроса
После анализа плана выполнения запроса осуществляется его оптимизация.
Оптимизация запроса предполагает удаление причин неэффективности запроса, среди которых наиболее весомыми являются:
- плохая статистика таблиц и индексов, участвующих в запросе (наиболее важный фактор, на который в первую очередь надо обратить внимание);
- проблемы с индексами: отсутствие нужных индексов, неэффективно построенные индексы, неэффективно используемые индексы, большое значение фактора кластеризации;
- проблемы с хинтами: отсутствие хинтов или они неэффективны;
- неэффективная структура запроса (запрос построен не корректно).
Кардинальность
(Cardinality)
Oracle оценивает кардинальность (мощность, количество элементов) конвейерной табличной функции, базируясь на размере блока базы данных. Когда используется размер блока по умолчанию, оптимизатор всегда предполагает, что кардинальность - 8168 строк.
Это прекрасно, если вы запрашиваете только конвейерную табличную функцию, но если планируется использовать ее в соединении, это может оказать негативное влияние на план выполнения.
- хинт CARDINALITY (9i+): Недокументирован (Undocumented)
- хинт OPT_ESTIMATE (10g+): Недокументирован (Undocumented)
- хинт DYNAMIC_SAMPLING (11gR1+): Вызывает полное сканирование конвейерной табличной функции для оценки количества элементов в самом запросе перед его запуском. Это очень расточительно.
- Расширенный Оптимизатор (Extensible Optimizer) (9i+): возможность расширения оптимизатора позволяет нам сообщить ему, что нужно поддерживать кардинальность.
- Обратная связь по количеству элементов (Cardinality Feedback): в Oracle Database 11gR2 оптимизатор замечает, отличается ли фактическое количество элементов от ожидаемой кардинальности. Последующие запросы будут нести свою кардинальность, скорректированную на основе этой обратной связи. Если предложение выбирается из разделяемого пула или экземпляр стартует заново, эта обратная связь по количеству элементов теряется. В Oracle Database 12c обратная кардинальность по количеству элементов сохраняется в табличной области SYSAUX.
Чтобы использовать расширяемый оптимизатор, в конвейерные табличные функции нужно вручную добавить параметр, который укажет оптимизатору использовать кардинальность.
Заметим, что параметр p_cardinality нигде не используется непосредственно в функции.
Затем мы строим тип и тело типа, чтобы установить кардинальность (количество элементов) вручную. Обратите внимание на ссылку на параметр p_cardinality в типе.
Этот тип может быть связан с любой конвейерной табличной функцией, используя следующую команду.
Мы знаем, что функция возвращает 10 строк, но оптимизатор этого не знает. Независимо от числа строк, возвращенных функцией, оптимизатор использует значение параметра p_cardinality как оценку количества элементов (кардинальности).
Читайте также: