Inlist iterator oracle это
Using FOR UPDATE allows you to use the WHERE CURRENT OF clause in INSERT , UPDATE , and DELETE commands. A COMMIT will invalidate the cursor, so you will need to reissue the SELECT FOR UPDATE after every commit.
01.11.2013
Example
Execution Plan
For the step with the REMOTE operation, you can query PLAN_TABLE for the syntax of the query sent to the remote node:
SELECT "COMPANY_ID","PERIOD_ID","SALES_TOTAL"
FROM "SALES" SALES WHERE "SALES_TOTAL">1000 AND "PERIOD_ID"=3
01.11.2013
Параллельное выполнение непараллельных запросов
(или какую практическую пользу можно извлечь, стимулируя параллельное выполнение комплексных запросов, использующих непараллельные функции без ключевого слова PARALLEL_ENABLE ?)
при выполнении традиционно крупногабаритного (~ 500 строк и в тексте, и в плане) запроса в бд OEBS
Характерными причинами неспособности Oracle сгенерировать быстрый план кроме избыточной нормализации схем OEBS и, как следствие, громозкости получающихся запросов, является распостранённое использование «непараллельных», в смысле активно читающих переменные пакета, функций типа FND_PROFILE.VALUE как в явном виде — в условиях запроса, так и в составе параметризованных обзоров
Судя по тексту функций основных схем OEBS, разработчики Oracle Applications по какой-то причине не склонны использовать/стимулировать параллельное выполнение: (more…)
Several months ago I wrote about avoiding inlist iterator, but this post about how to force inlist iterator in queries like that:
i.e. when we need to get rows from big table using index by list of values from another table.
A couple of workarounds
TOPICS
Interpreting the Execution Plan
The Execution Plan shows that the DUAL table (comprising 1 row, and owned by SYS ) is scanned. The COMPANY_ID_SEQ sequence is used to generate the value of the NextVal pseudo-column for the returned row, using the SEQUENCE operation.
An INLIST ITERATOR operation appears in the Execution Plan output if an index implements an INLIST predicate.
Unresolved quiz: Avoiding in-list iterator
A couple days ago i had very interesting quiz, which is not resolved yet.
Look at this simplified query:
As you see, in such queries cbo always generating plans with INLIST ITERATOR, and it is reasonably in cases when there are many rows with different values of field B for most values of A, and this number is much larger than number of values in the “INLIST”. But in such case as shown, will be better to use index range scan with access by A and filter by B:
But how we can do it? I know 5 options:
1. Trace event 10157
2. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
3. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
4. Recreating index from xt2(a,b) to xt2(a,1,b)
5. Changing optimizer_mode to “rule” through hint or SQL profile/baseline
But unfortunately all of them are inapplicable for the my real problem, because i cannot for some reasons rewrite query or change query with advanced rewrite, cannot recreate/add index, and can’t change optimizer_mode, because execution plan for the real query will become worst than plan generated with CBO with inlist iterator(some operations aren’t exists in RBO).
Could anybody suggest any another solution?
From 10053 trace on nonpatched 11.2.0.3:
Interpreting the Execution Plan
The Explain Plan shows that the remote SALES table is used as the driving table for the NESTED LOOPS join (see the NESTED LOOPS operation for a brief discussion of driving tables). The text in the PLAN_TABLE . The Other column shows the query that is executed in the remote database. For each Company_ID value returned by the query of the remote SALES table, the COMPANY_PK index will be checked to see if a matching Company_ID value exists in the COMPANY table. When a match exists, that row is returned to the user by using the NESTED LOOPS operation.
FIRST ROWS uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).
The following statement changes the goal of the cost-based optimizer for your session to best response time:
Index Join vs Index Bitmap и использование db file parallel read при доступе к блокам таблицы
Сравнение методов соединения индексов Index Join и Index Bitmap, релизуемых, например, подсказками /*+ INDEX_JOIN */ и /*+ INDEX_COMBINE */ , показывает, что оптимизатор отдаёт предпочтение Index Join, даже в случае проигрыша по стоимости
Далее приведены простые тесты индексных методов, а также попутно полученный тесткейс использования операции db file parallel read на шаге TABLE ACCESS BY INDEX ROWID плана выполнения (more…)
Join Predicate Push-Down, допускающий картезианское произведение
SQL трейс уровня 8+ («с ожиданиями») выявил медленный запрос, частовыполняемый при элементарных бизнес операциях:
Статистика выполнения из того же трейса показывает, что при построении плана запроса не срабатывает ожидаемая операция Join Predicate Push-Down (JPPD), приводя к TABLE ACCESS FULL по хорошо индексированной таблице T2: (more…)
22.09.2013
Example
Since REMOTE requires a remote database access, a database link will be created for this example. The database link connects to the Hobbes account in the database that is identified via the ‘test’ service name in the local tnsnames.ora file.
30.07.2013
30.06.2012
Interpreting the Execution Plan
The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY that satisfy the limiting condition on the City value ( City > ‘Roanoke’ ). The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. The Active_Flag=‘Y’ criteria is implicitly applied during the TABLE ACCESS BY ROWID operation. The FOR UPDATE operation is then applied to give the user row-level locks on each row returned from the query.
FILTER performs a WHERE clause condition when no index can be used to assist in the evaluation. Unfortunately, the FILTER operation is sometimes implicit. Any FILTER condition that is applied when performing a table access (such as during a TABLE ACCESS BY ROWID ) does not show up in the plan. When FILTER shows up in an Explain plan, it usually the result of a missing index or the disabling of an existing index.
The FILTER operation was in a prior example—the CONNECT BY operation’s example. In the query shown in the following listing, the WHERE criteria on the State column is not applied until after the CONNECT BY hierarchy has completed; the resulting rows are filtered to determine which meet the specified State criteria.
03.07.2013
07.08.2012
Index Column
When the INLIST column empno is an index column but not a partition column, then the plan is as follows (the INLIST operator appears above the table operation but below the partition operation):
The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate appears on the index start/stop keys.
ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров
— попутная ошибка ORA-48913 вызвана ограничением размера трейса:
, в отличие от описания выраженного в блоках ОС = 512 байт (Oracle 11.1.0.7 Linux x86_64) — см. [ID 30762.1]
Трейс /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9219.trc достаточно быстро указывает на причину ошибки ORA-600 [kglUnKeepHandle]: (more…)
Комментарии к записи ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров отключены
15.10.2013
Индексный доступ к таблицам удалённого обзора-2: практикум
Наглядная иллюстрация к теоретической демонстрации из предыдущего поста
На рабочей версии 11.2.0.3 был замечен показательный запрос (далее показана самая медленная нетрансформируемая его часть), с грубым нарушением SLA стабильно выполнявшийся за 6 с лишним минут:
При этом план одновременно показывает два разных подхода к выполнению подзапросов: (more…)
Total Pageviews
Example
Since the SEQUENCE operation requires a sequence to exist, a sequence named COMPANY_ID_SEQ will be created.
In the exam ple query, the next value is selected from the sequence by selecting the NextVal pseudo-column from DUAL .
Конкатенация против INLIST ITERATOR
Достаточно давно Саян Малакшинов описал и уже успешно разрешил через поддержку ( патч + фиксация в версии Oracle 12.2 ) проблему применения / управления операцией INLIST ITERATOR — Unresolved quiz: Avoiding in-list iterator
И, хотя, к сожалению, заметка попалась на глаза достаточно поздно, 2drink borjomi is 2late, мне кажется интересным рассмотреть этот отличный тест-кейс и предложить ещё один вариант решения с форсированным подсказкой использованием конкатенации, как операции противожной INLIST (more…)
Индексный доступ к таблицам удалённого обзора
Сталкиваясь с локально выполняемыми запросами, использующими удалённые UNION ALL обзоры, обратил внимание на особенности формирования рекурсивных запросов, которые могут оказаться полезными для получения гарантированного индексного доступа к удалённым таблицам
UNION ALL обзоры, в свою очередь, иногда используются для обеспечения беспрерывного доступа к данным таблиц (точнее, материализованных представлений, поочерёдно обновляемых в режиме nonatomic complete с целью экономии времени и ресурсов undo/redo). В тескте такого обзора, конечно, используются какие-то PL/SQL функции или SQL конструкции, обеспечиващие в любой момент времени доступ только к одному наиболее «свежему» источнику данных, не влияющие, однако, на наблюдаемые закономерности
Далее нудно и монотонно последовательно описаны тесты, аналогичные проведённым в Уникальные индексы для Join Predicate Push-Down, для механизма, который можно было бы условно назвать remote JPPD для версий 11g/12c
В Oracle 12c в этом месте никаких улучшений не наблюдается, более того, некоторые правила, например, стимулирование удалённого индексного доступа при использовании уникального индексов локальной таблицы в отличие от предыдущих версий уже не работают (more…)
Join Factorization
По долгу службы пришлось поразбираться с относительно свежим (11.2+) типом преобразования Cost-Based типа Join Factorization (JF), предназначенного для выделения общих составляющих (факторизации) в запросах с UNION ALL соединениями, и хорошо описанного в блоге разработчиков оптимизатора
Для управления этим типом трансформации на уровне системы / сессии / запроса существует соответствующий параметр:
Кроме того, поскольку JF имеет версионное ограничение применения, запретить преобразование можно понижая версию оптимизатора, например, на уровне запроса подсказкой OPTIMIZER_FEATURES_ENABLE: (more…)
Index and Partition Columns
When empno is an indexed and a partition column, the Explain plan contains an INLIST ITERATOR operation above the partition operation:
22.09.2013
05.08.2013
Execution Plan
DML при ошибочной оценке cardinality удалённых обзоров
Ошибка при транзакционном обновлении материализованного представления (atomic mview refresh):
На неспортивных методах типа увеличения undo tablespace останавливаться смысла нет — запрос работает неприемлемо долго
Формально план по которому выполняется запрос выглядит недорогим и быстрым:
одинаково неточно оценивая cardinality удалённых обзоров:
(more…)
Достаточно давно Саян Малакшинов описал и уже успешно разрешил через поддержку ( патч + фиксация в версии Oracle 12.2 ) проблему применения / управления операцией INLIST ITERATOR — Unresolved quiz: Avoiding in-list iterator
И, хотя, к сожалению, заметка попалась на глаза достаточно поздно, 2drink borjomi is 2late, мне кажется интересным рассмотреть этот отличный тест-кейс и предложить ещё один вариант решения с форсированным подсказкой использованием конкатенации, как операции противожной INLIST
На всякий случай (для быстрой воспроизводимости) — DDL кейса из блога Саяна:
Типичное выполнение тестового запроса:
— показывает условно «плохой» и по стоимости, и по статистике план выполнения — собственно, в этом и заключается одно из проявлений бага: по умолчанию Oracle выбирает более дорогой план, что противоречит основополагающим принципам CBO
Как описал Саян, одним из вариантов решения является установка Event 10157 (CBO disable index access path for in-list), срабатывающий, однако, только в случае отсутствия в кэше курсора с «плохим» планом
Если же в Shared Pool уже есть «плохой» план, установка этого события никак не способствует генерирации нового, условно «хорошего»:
После удаления курсора установка события 10157, как и дОлжно, формирует «хороший» план:
— очевидно, по этой же причине поддержка рекомендует устанавливать event 10157 для всех запросов на уровне системы в spfile/pfile, что не всегда может быть применимо
Интересно, что единственное формальное отличие планов — присутствие в «плохом» Outline подсказки
NUM_INDEX_KEYS(@»SEL$1″ «XT2″@»SEL$1» «IX_XT2» 2), изменение параметра которой в виде NUM_INDEX_KEYS(@»SEL$1″ «XT2″@»SEL$1» «IX_XT2» 1), по определению способное решить проблему, до применения полученного Саяном патча также пока не работает — и это вторая сторона бага
Фиксация «хорошего» плана как бы помогает:
но только до того момента, пока курсор с «хорошим» планом находится в кэше
После удаления правильного курсора из Shared Pool, незамедлительно генерируется новый курсор и запрос продолжает выполняться по «плохому» плану:
, созданный Baseline при этом меняет статус на REPRODUCED=NO:
— но ровно до того момента, пока в Shared Pool опять не окажется «хороший» план, как было показано выше. В точности также работает фиксация этого плана с помощью Stored Outline
Т.о. план в описываемом случае определяется не только набором подсказок Baseline / Stored Outline (а также параметрами инстанса и сессии, статистикой объектов и т.д.), которые формально соответствуют секции Outline «хорошего» плана:
, но и присутствием в кэше SGA курсора с «плохим» или «хорошим» планом выполнения (сгенерированным с помощью определённого флага оптимизатора — event 10157 как в этом случае, например). Т.е. план не является детерминированным — что, как я понимаю, и является одним из важных признаков бага (ранее уже встречаемом для другого бага оптимизатора)
Безполезный Baseline можно удалить:
и, обратив внимание на попадающуюся рекомендацию поддержки использовать подсказку USE_CONCAT для отключения операции INLIST, попробовать хинт в виде:
— что позволяет получить «правильный» план выполнения. Значение параметра 32767 значения здесь не имеет, имеет значение только сам факт присутствия этого параметра в диапазоне 0-32767
Далее можно сгенерировать, например, соответствующий SQL Patch:
— с подсказкой директивного типа USE_CONCAT, которая явно не попадая в Outline плана выполнения, и не генерируя соответствующих операций типа CONCATENATION напрямую:
— успешно генерирует правильный план, форсируя вместо использования «плохой» INLIST итерации:
более простую и дешёвую по расчётам CBO операцию конкатенации из «хорошего» плана:
Внезапно замедлившиеся сессии при выполнении запроса 4y4bvy7bhkqbn согласованно ожидали read by other session / db file sequential read:
В историю выполнения обнаружилась нестабильность времени выполнения/планов запроса: (more…)
18.11.2013
06.10.2013
Скрипты для сравнения планов выполнения
Периодически появляется необходимость сравнить / найти различия в планах выполнения запроса, для последующих глубокомысленных умозаключений и выводов
А поскольку планы (и запросы) встречаются весьма объёмные и сравнивать их на маленьком экране ноутбука не всегда удобно, написал пару скриптов:
-
— для выявления отличий в секции Outline (т.е. в наборах подсказок, собственно, и формирующих сравниваемые планы) — для удобства просмотра / анализа отличий планов по конкретным блокам (Query Block)
Далее — пример использования (more…)
Interpreting the Execution Plan
The plan shows that first the COMPANY_PK index is being used to find the root node ( Company_ID = 1 ), then the index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_ID s is complete, the FILTER operation—the WHERE clause related to the State value—is applied. The query does not use the index on the State column, although it is available and the column is used in the WHERE clause.
REMOTE sends a SQL statement to be executed at a remote node via a database link. The syntax of the SQL statement sent to the remote node is shown in the Other column of PLAN_TABLE .
Execution Plan
Example
select Name, City, State from COMPANY where City > ‘Roanoke’ and Active_Flag = ‘Y’
for update of Name;
Join Factorization
По долгу службы пришлось поразбираться с относительно свежим (11.2+) типом преобразования Cost-Based типа Join Factorization (JF), предназначенного для выделения общих составляющих (факторизации) в запросах с UNION ALL соединениями, и хорошо описанного в блоге разработчиков оптимизатора
Для управления этим типом трансформации на уровне системы / сессии / запроса существует соответствующий параметр:
Кроме того, поскольку JF имеет версионное ограничение применения, запретить преобразование можно понижая версию оптимизатора, например, на уровне запроса подсказкой OPTIMIZER_FEATURES_ENABLE: (more…)
Execution Plan
FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT
Уникальные индексы для Join Predicate Push-Down
Обратил внимание на важность наличия / использования уникальных индексов во внешнем блоке запроса для применения JPPD с обзорами (Inline View) типа UNION ALL в случаях когда не для всех соединяемых в обзоре таблиц возможен индексный доступ
Формулировка получилась достаточно громоздкой, но оказалось, что проблема достаточно просто воспроизводится
Итак, тестовая схема:
22.12.2012
Индексный доступ к таблицам удалённого обзора
Сталкиваясь с локально выполняемыми запросами, использующими удалённые UNION ALL обзоры, обратил внимание на особенности формирования рекурсивных запросов, которые могут оказаться полезными для получения гарантированного индексного доступа к удалённым таблицам
UNION ALL обзоры, в свою очередь, иногда используются для обеспечения беспрерывного доступа к данным таблиц (точнее, материализованных представлений, поочерёдно обновляемых в режиме nonatomic complete с целью экономии времени и ресурсов undo/redo). В тескте такого обзора, конечно, используются какие-то PL/SQL функции или SQL конструкции, обеспечиващие в любой момент времени доступ только к одному наиболее «свежему» источнику данных, не влияющие, однако, на наблюдаемые закономерности
Далее нудно и монотонно последовательно описаны тесты, аналогичные проведённым в Уникальные индексы для Join Predicate Push-Down, для механизма, который можно было бы условно назвать remote JPPD для версий 11g/12c
В Oracle 12c в этом месте никаких улучшений не наблюдается, более того, некоторые правила, например, стимулирование удалённого индексного доступа при использовании уникального индексов локальной таблицы в отличие от предыдущих версий уже не работают (more…)
Execution Plan
Example
select Name, City, State from COMPANY where City > ‘Roanoke’ and Active_Flag = ‘Y’
for update of Name;
18.10.2013
Oracle 11g: стоимость операции FILTER и OR-Expansion
Практическая проблема выглядела следующим образом: при выполнении дизъюнктивного (с использованием OR в разделе WHERE) DML оператора DELETE Oracle 11.1 выбирает формально недорогой, но на практике выполняющийся часами план: (more…)
Example
Popular Posts
ORA-00604: error occurred at recursive SQL level 1 ORA-20061: versioned objects have to be version disabled before being dropped ORA-065.
The datafiles are the physical storage space on the server. Storage in the datafiles is allocated by blocks to each extent that is u.
a) Bono's 6 Thinking Hats b) Walt Disney's method c) Force field analysis d) Problem solving team building Bono's 6 Thin.
ORA-20104: cannot version disable this table ORA-06512: at "SYS.LT", line 9152 ORA-06512: at line 3 the following error will o.
What is necessary for top-tier Web sites, according to proponents of NoSQL, is massive scalability, low latency, the ability to .
UNIQUE Sorts to eliminate duplicate rows. This typically occurs as a result of using the DISTINCT clause. SORT UNI.
Oracle has provided several solutions to ensure and secure the database. An efficient security policy not only prevents the database agai.
Достаточно давно Саян Малакшинов описал и уже успешно разрешил через поддержку ( патч + фиксация в версии Oracle 12.2 ) проблему применения / управления операцией INLIST ITERATOR — Unresolved quiz: Avoiding in-list iterator
И, хотя, к сожалению, заметка попалась на глаза достаточно поздно, 2drink borjomi is 2late, мне кажется интересным рассмотреть этот отличный тест-кейс и предложить ещё один вариант решения с форсированным подсказкой использованием конкатенации, как операции противожной INLIST (more…)
First K Rows optimization-2
Полтора года назад столкнулся с проблемой оптимизатора при параллельном выполнении запросов с ограничением ROWNUM в версиях 11g, записанной в First K Rows optimization
Сегодня по нашей заявке выпущен соответствующий патч 11858963 PLAN REGRESSION UNDER FIRST_ROWS_10 WITH 4887636 ENABLED,пока только для 11.2.0.3 Linux x86-64 — как просили !
SR Severity2 (Significant), не эскалировал, т.к. варианты решения были давно известны и применены на продакшн
Сложнее всего оказалось пробиться с багом оптимизатора сквозь первую линию поддержки, далее — разработчики Oracle справились всего за 5+ месяцев :)
03.03.2013
11.11.2012
19.05.2012
Partition Column
When empno is a partition column and there are no indexes, then no INLIST ITERATOR operation is allocated:
LOAD AS SELECT bypasses the buffer cache when performing a direct path load based on a SELECT statement.
An Execution plan will denote the presence of a direct load operation with the LOAD AS SELECT operation:
Fixed tables are those not in Oracle’s data dictionary. FIXED TABLE is normally used to optimize V$ and X$ statements. However, join order for a V$ query will be entirely determined by the order of tables in the FROM clause. Be careful when using FIXED TABLE because:
· Neither the cost-based optimizer nor the rule-based optimizer recognizes the presence of V$ indexes when determining join order or method.
· There are never any optimizer statistics held against the V$ or X$ tables and consequently the cost-based optimizer has no information to use to determine the best join order.
Where an index exists on a V$ table, it will normally be used whenever the column is used for an exact lookup. The Explain Plan reveals that this is so through the special access path FIXED INDEX. For instance, the following query uses the SID index on V$SESSION:
Remembering that the "index" is not really an Oracle B-tree index and in has more in common with a hash cluster, it’s not surprising to see that the index is disabled if a range scan is attempted:
The TEMP TABLE GENERATION operation creates a temporary dimension table for dimension tables that have been joined by a Star Transformation. For each dimension table in the joined set, TEMP TABLE GENERATION creates a temporary table to replace the table in the Execution Plan. A temporary table is created using two steps: create table and insert each. Since there are at least two tables in a join, this operation creates a minimum of four temporary tables.
The Collection Iterator operation returns certain values from a collection such as VARRAY and nested table.
No comments:
Example
select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID start with Company_ID = 1;
Cardinality Feedback: многочисленные версии курсоров с повторяющимся планом выполнения
Практический запрос запрос (11.2.0.3 Linunx x86_64), по причине «удачного» совпадения условий стабильно генерирует дополнительные курсоры с повторяющимся планом выполнения под влиянием Cardinality Feedback (CF):
Комментарии к записи Cardinality Feedback: многочисленные версии курсоров с повторяющимся планом выполнения отключены
Subscribe To ORACLE4U
Posts
Posts
Comments
Comments
Уникальные индексы для Join Predicate Push-Down
Обратил внимание на важность наличия / использования уникальных индексов во внешнем блоке запроса для применения JPPD с обзорами (Inline View) типа UNION ALL в случаях когда не для всех соединяемых в обзоре таблиц возможен индексный доступ
Формулировка получилась достаточно громоздкой, но оказалось, что проблема достаточно просто воспроизводится
Итак, тестовая схема:
15.10.2013
Подсказки OPTIMIZER_MODE в обзорах
Запрос, использующий (напрямую или через вложенные обзоры) обзор с подсказками OPTIMIZER_MODE типа: RULE, FIRST_ROWS, FIRST_ROWS(n), будет выполняться с указанной в обзоре OPTIMIZER_MODE — что может значительно и не всегда очевидно влиять на план выполнения
Для возвращения оптимизатора к нормальному поведению (состоянию по умолчанию) OPTIMIZER_MODE придётся менять на уровне системы, сессии или запроса (подсказкой «верхнего уровня» на уровне всего запроса), либо убирать подсказку из обзора-виновника , который ещё нужно найти
Например, при создании обзора с подсказкой /*+ RULE */: (more…)
Patch for “Bug 16516751 : Suboptimal execution plan for query with join and in-list using composite index” is available now
Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine
UPD: Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:
From 10053 trace on nonpatched 11.2.0.3:
Читайте также: