Oracle mechanics

27.10.2010

Oracle 11.2.0.1: баг типа PERFORMANCE — filtering join elimination

Filed under: CBO,Optimizer features,Oracle,Oracle new features,parameters — Игорь Усольцев @ 23:18
Tags: ,

На версии 11.2.0.1 был замечен неправильный результат выполнения запроса типа (запрос явно избыточен из-за лишнего объединения, но это не повод возвращать неправильный результат)

SQL> SELECT BLOG_OWNER_ID
 2    FROM (SELECT BO.OWNER_ID as BLOG_OWNER_ID
 3            FROM b_blog_post P
 4           INNER JOIN b_blog BO ON (P.BLOG_ID = BO.ID)
 5           INNER JOIN b_blog BS ON (P.BLOG_ID = BS.ID) --< избыточное соединение
 6           ORDER BY P.DATE_PUBLISH, P.ID)
 7   WHERE ROWNUM <= 1
 8  /

BLOG_OWNER_ID
 -------------
(NULL)

возвращает неправильные результат (null). При замене ANSI синтаксиса («INNER JOIN») на классический синтаксис Oracle — запрос возвращает правильный результат  (22), при исключении избыточного соединения (строка №5) — результат также правильный (22), очень похоже на баг

На 10.2.0.4 проблема не наблюдается, на 11.1.0.7 — тоже нет. Специалистами партнёра и клиента было найдено «быстрое» рабочее решение — отключить все улучшения оптимизатора (optimizer features), добавленные в Oracle 11g установкой параметра OPTIMIZER_FEATURES_ENABLE=’10.2.0.4′

Решение радикальное, можно попробовать найти конкретное «улучшение», ответственное за эффект, и отключить только эту фичу оптимизатора

Запрос даёт правильный результат при использовании подсказки /*+ NO_QUERY_TRANSFORMATION*/, указывающей оптимизатору пропустить все операции преобразования запроса, что ещё раз указывает на то, что ошибка связана с оптимизацией плана выполнения

Кроме собственно опций к списку optimizer features, начиная с версии 10.2, добавлен механизм fix control, позволяющий включать/отключать исправления багов оптимизатора, перечисленных в обзорах v$session_fix_control, v$system_fix_control для сессии/системы,  с помощью скрытого параметра _fix_control

Пробуем попеременно на уровне сессии поотключать фиксы, добавленные в версии 11.2.0.1 (т.к. при значении OPTIMIZER_FEATURES_ENABLE=’11.1.0.7′ ошибки нет)

declare
 result_value b_blog.owner_id%type;
 begin
 for reco in (
 select bugno from v$session_fix_control where session_id=sys_context('USERENV', 'SID')
 and optimizer_feature_enable = '11.2.0.1' and value = 1
 )
 loop
 execute immediate 'alter session set "_fix_control"='''||reco.bugno||':OFF''';
 SELECT BLOG_OWNER_ID into result_value
 FROM
 (SELECT
 BO.OWNER_ID as BLOG_OWNER_ID
 FROM
 b_blog_post P
 INNER JOIN b_blog BO ON (P.BLOG_ID = BO.ID)
 INNER JOIN b_blog BS ON (P.BLOG_ID = BS.ID)
 ORDER BY P.DATE_PUBLISH, P.ID
 )
 WHERE ROWNUM <= 1;
 if result_value is not null then dbms_output.put_line(reco.bugno||' is error reason'); end if;
 execute immediate 'alter session set "_fix_control"='''||reco.bugno||':ON''';
 end loop;
 end;
/

безрезультатно

Внимательно смотрим план неправильного выполнения запроса (OPTIMIZER_FEATURES_ENABLE=’11.2.0.1′)

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'+OUTLINE'));
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     7 (100)|          |
|*  1 |  COUNT STOPKEY              |                  |       |       |            |          |
|   2 |   VIEW                      |                  |    31 |   403 |     7(43)  | 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY    |                  |    31 |   651 |     7(43)  | 00:00:01 |
|   4 |     MERGE JOIN              |                  |    31 |   651 |     6(34)  | 00:00:01 |
|   5 |      INDEX FULL SCAN        | SYS_C0012177     |    15 |       |     1 (0)  | 00:00:01 |pk_b_blog(id)
|*  6 |      SORT JOIN              |                  |    31 |   434 |     4(50)  | 00:00:01 |
|   7 |       VIEW                  | index$_join$_002 |    31 |   434 |     3(34)  | 00:00:01 |
|*  8 |        HASH JOIN            |                  |       |       | |          |
|   9 |         INDEX FAST FULL SCAN| IX_BLOG_POST_1   |    31 |   434 |     1 (0)  | 00:00:01 |b_blog_post(blog_id,publish_status,date_publish)
|  10 |         INDEX FAST FULL SCAN| SYS_C0012192     |    31 |   434 |     1 (0)  | 00:00:01 |pk_b_blog_post(id)
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

 1 - SEL$1
 2 - SEL$AF5728A3 / from$_subquery$_001@SEL$1
 3 - SEL$AF5728A3
 5 - SEL$AF5728A3 / BS@SEL$3
 7 - SEL$9073249B / P@SEL$2
 8 - SEL$9073249B
 9 - SEL$9073249B / indexjoin$_alias$_001@SEL$9073249B
 10 - SEL$9073249B / indexjoin$_alias$_002@SEL$9073249B

Outline Data
-------------

 /*+
...
 ELIMINATE_JOIN(@"SEL$54D64B3C" "BO"@"SEL$2")
...
 ELIMINATE_JOIN(@"SEL$54D64B3C" "BO"@"SEL$2")
...
 */

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter(ROWNUM<=1)
 3 - filter(ROWNUM<=1)
 6 - access("P"."BLOG_ID"="BS"."ID")
 filter("P"."BLOG_ID"="BS"."ID")
 8 - access(ROWID=ROWID)

прежде всего непонятно, как Oracle планировал получить значение поля B_BLOG.OWNER_ID, обращаясь к таблице B_BLOG только по индексу первичного ключа pk_b_blog(B_BLOG.id), либо план выполнения — фикция?

Кроме того в секции Outline обращают внимание продублированная подсказка ELIMINATE_JOIN(@»SEL$54D64B3C» «BO»@»SEL$2»), очевидно, относящаяся к интересующей нас таблице B_BLOG под псевдонимом BO, т.е. соединение с этой таблицей исключается?

Если есть подсказка ELIMINATE_JOIN, возможно найдётся и противоположная:

SQL> select * from v$sql_hint where name like '%ELIMINATE_JOIN%';
NAME               SQL_FEATURE       CLASS           INVERSE            TARGET_LEVEL  PROPERTY  VERSION    VERSION_OUTLINE
ELIMINATE_JOIN     QKSFM_TABLE_ELIM  ELIMINATE_JOIN  NO_ELIMINATE_JOIN  4             16        10.2.0.1   10.2.0.1
NO_ELIMINATE_JOIN  QKSFM_TABLE_ELIM  ELIMINATE_JOIN  ELIMINATE_JOIN     4             16        10.2.0.1   10.2.0.1

Выполняем запрос, просто инвертировав подсказку из Outline оптимизатора (используя сгенерированные оптимизатором названия блоков запроса)

SQL> SELECT /*+ NO_ELIMINATE_JOIN(@"SEL$54D64B3C" "BO"@"SEL$2")*/
 2   BLOG_OWNER_ID
 3    FROM (SELECT BO.OWNER_ID as BLOG_OWNER_ID
 4            FROM b_blog_post P
 5           INNER JOIN b_blog BO ON (P.BLOG_ID = BO.ID)
 6           INNER JOIN b_blog BS ON (P.BLOG_ID = BS.ID)
 7           ORDER BY P.DATE_PUBLISH, P.ID)
 8   WHERE ROWNUM <= 1
 9  /

BLOG_OWNER_ID
-------------
 22
----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |       |       |     7 (100)|          |
|*  1 |  COUNT STOPKEY                  |                  |       |       |            |          |
|   2 |   VIEW                          |                  |    31 |   403 |     7  (43)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY        |                  |    31 |   651 |     7  (43)| 00:00:01 |
|   4 |     MERGE JOIN                  |                  |    31 |   651 |     6  (34)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| B_BLOG           |    15 |   105 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | SYS_C0012177     |    15 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |                  |    31 |   434 |     4  (50)| 00:00:01 |
|   8 |       VIEW                      | index$_join$_002 |    31 |   434 |     3  (34)| 00:00:01 |
|*  9 |        HASH JOIN                |                  |       |       |            |          |
|  10 |         INDEX FAST FULL SCAN    | IX_BLOG_POST_1   |    31 |   434 |     1   (0)| 00:00:01 |
|  11 |         INDEX FAST FULL SCAN    | SYS_C0012192     |    31 |   434 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Outline Data
-------------
 /*+
...
 ELIMINATE_JOIN(@"SEL$54D64B3C" "BS"@"SEL$3")
...
 ELIMINATE_JOIN(@"SEL$54D64B3C" "BS"@"SEL$3")
...
 */

Отлично, запрос выполняется правильно, в плане выполнения добавилась (только одна ничего лишнего) необходимая операция доступа TABLE ACCESS BY INDEX ROWID к таблице B_BLOG, в списке  Outline появилась подсказка ELIMINATE_JOIN для исключения действительно избыточного соединения с таблицей B_BLOG под псевдонимом  «BS»

Теперь примерно понятна причина ошибки: в первом запросе было некорректно исключено соединение «INNER JOIN b_blog BO ON (P.BLOG_ID = BO.ID)», после чего в плане осталась только операция индексного доступа INDEX FULL SCAN по первичному ключу таблицы BLOG_ID BS (SYS_C0025898 — уникальный индекс по столбцу «BLOG_ID».»ID»), а операция доступа к таблице BLOG_ID BO для получения значения столбца «BO».»OWNER_ID» просто «потерялась», что совершенно правильно было отражено в плане выполнения первоначальном запроса (с неправильным результатом)

В трейсе 10053 первого запроса (с неправильным результатом) можно найти описание операции исключения «избыточного» соединения (join elimination), а точнее в нашем случае FJE (Filtering Join Elimination — устранение избыточного фильтрующего соединения)

Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT «BO».»OWNER_ID» «BLOG_OWNER_ID» FROM «SCOTT».»B_BLOG_POST» «P»,»SCOTT».»B_BLOG» «BO»,»SCOTT».»B_BLOG» «BS» WHERE «P».»BLOG_ID»=»BS».»ID» AND «P».»BLOG_ID»=»BO».»ID» ORDER BY «P».»DATE_PUBLISH»,»P».»ID»
FJE:   eliminate table: B_BLOG BO
SQL:******* UNPARSED QUERY IS *******
SELECT «BS».»OWNER_ID» «BLOG_OWNER_ID» FROM «SCOTT».»B_BLOG_POST» «P»,»SCOTT».»B_BLOG» «BS» WHERE «P».»BLOG_ID»=»BS».»ID» ORDER BY «P».»DATE_PUBLISH»,»P».»ID»
Registered qb: SEL$AF5728A3 0x894185c (JOIN REMOVED FROM QUERY BLOCK SEL$54D64B3C; SEL$54D64B3C; «BO»@»SEL$2»)

и формально всё выглядит корректно, блок

SELECT «BO».»OWNER_ID» «BLOG_OWNER_ID» FROM…

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

SELECT «BS».»OWNER_ID» «BLOG_OWNER_ID» FROM…

однако, план выполнения (который, как мы убедились, правильно отражает ход выполнения запроса) и, главное, ошибочные результаты запроса говорят о том, что в результате применения опции (optimizer feature) под названием filtering join elimination оптимизатор допускает ошибку

В том же трейсе 10053 в списке параметров, принимаемых оптимизатором к рассмотрению можно найти синтаксически подходящий

_optimizer_eliminate_filtering_join = true

и пробуем изменить значение параметра (отключить опцию) на уровне сессии

SQL> show parameter optimizer_features_enable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.1

SQL> alter session set "_optimizer_eliminate_filtering_join" = false;
Session altered.

SQL> show parameter "_optimizer_eliminate_filtering_join"
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_eliminate_filtering_join  boolean     FALSE
SQL> SELECT BLOG_OWNER_ID
 2    FROM (SELECT BO.OWNER_ID as BLOG_OWNER_ID
 3            FROM b_blog_post P
 4           INNER JOIN b_blog BO ON (P.BLOG_ID = BO.ID)
 5           INNER JOIN b_blog BS ON (P.BLOG_ID = BS.ID)
 6           ORDER BY P.DATE_PUBLISH, P.ID)
 7   WHERE ROWNUM <= 1
 8  /

BLOG_OWNER_ID
-------------
 22

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR);
-----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |       |       |7 (100)|          |
|*  1 |  COUNT STOPKEY                   |                  |       |       ||          |
|   2 |   VIEW                           |                  |    31 |   403 |7  (43)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY         |                  |    31 |   744 |7  (43)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                  |    31 |   744 |6  (34)| 00:00:01 |
|   5 |      MERGE JOIN                  |                  |    31 |   651 |6  (34)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| B_BLOG           |    15 |   105 |2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | SYS_C0012177     |    15 |       |1   (0)| 00:00:01 |
|*  8 |       SORT JOIN                  |                  |    31 |   434 |4  (50)| 00:00:01 |
|   9 |        VIEW                      | index$_join$_002 |    31 |   434 |3  (34)| 00:00:01 |
|* 10 |         HASH JOIN                |                  |       |       ||          |
|  11 |          INDEX FAST FULL SCAN    | IX_BLOG_POST_1   |    31 |   434 |1   (0)| 00:00:01 |
|  12 |          INDEX FAST FULL SCAN    | SYS_C0012192     |    31 |   434 |1   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN           | SYS_C0012177     |     1 |     3 |0   (0)|          |
-----------------------------------------------------------------------------------------------------

Удалось найти конкретную опцию оптимизатора (optimizer feature), отключение которой позволяет избежать бага с ошибочным результатом запроса при сохранении всех прочих прелестей нового оптимизатора версии 11.2.0.1. При этом, правда, из плана выполнения не исключается действительно лишнее фильтрующее соединение с таблицей B_BLOG BS — в плане появились две избыточные операции NESTED LOOPS (4) и INDEX UNIQUE SCAN (13), которых не было в плане выполнения запроса с подсказкой NO_ELIMINATE_JOIN, который и является оптимальным с точки зрения Oracle

Testcase для проверки результатов получить оказалось достаточно просто:

drop table emp_cs;

create table emp_cs
as
select rownum as id, e.* from emp e
connect by level < 6;

alter table emp_cs add CONSTRAINT PK_emp_cs PRIMARY KEY (id );

--create index ind_emp_cs on emp_cs(empno,deptno,hiredate);

drop table dept_cs;

create table dept_cs
as
select rownum as id, d.* from dept d
connect by level < 10;

alter table dept_cs add CONSTRAINT PK_dept_cs PRIMARY KEY (id );

begin dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP_CS'); end;
begin dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'DEPT_CS'); end;

SELECT *
FROM (
SELECT
 ds0.deptno
FROM
emp_cs es
INNER JOIN dept_cs ds0 ON (ds0.id = es.deptno)
INNER JOIN dept_cs ds1 ON (ds1.id = es.deptno)
ORDER BY es.hiredate, es.id
)
WHERE ROWNUM <= 1;
DEPTNO
----------
(NULL)
-------------------------------------------
| Id  | Operation               | Name    |
-------------------------------------------
|   0 | SELECT STATEMENT        |         |
|*  1 |  COUNT STOPKEY          |         |
|   2 |   VIEW                  |         |
|*  3 |    SORT ORDER BY STOPKEY|         |
|*  4 |     HASH JOIN           |         |
|   5 |      TABLE ACCESS FULL  | DEPT_CS |
|   6 |      TABLE ACCESS FULL  | EMP_CS  |
-------------------------------------------

на тестовом кейсе наблюдаются в точности те же эффекты, но план выполнения не такой красноречивый, как в «боевом» примере :(

Кроме того, при тестировании на платформе Windows 7 32-bit выполнение запроса периодически завершалось ошибкой ORA-3113: end-of-file on communication channel на клиенте и ORA-7445: exception encountered: core dump [__intel_new_memcpy()+… на сервере, что похоже на баг Bug : ORA-7445 [INTEL_NEW_MEMCPY] OR WRONG RESULTS, подтверждённый в версии 11.2.0.1, в котором описан очень похожий запрос с двумя ANSI JOIN-ами, результат которого также зависит от синтаксиса^

SELECT * FROM (
 SELECT ja.hnum, 1 AS sorting, jav.lagerbestand, jav.voe_tage
 FROM jpcartikel ja
 INNER JOIN jpcangebote jo ON ja.hnum = jo.hnum
 INNER JOIN v$jpcartikel$availability jav ON jo.hnum = jav.hnum
 WHERE jo.artikelart in ('DVD', 'HD', 'BR')
 ORDER BY  ja.voe_datum DESC, UPPER(ja.interpret) DESC, UPPER(ja.titel)
 DESC
 ) WHERE rownum <= 5;

Методов решения проблемы не описано (Workaround No), зато тип бага подсказывает, что проблема, вероятно, близка к нашей :)

BUG TYPE CHOSEN
===============
Performance

1 комментарий »

  1. Спасибо большое!!!!!
    У нас после миграции на 11.2 была проблема, правда немного другая, благодаря мыслям в статье решил!

    комментарий от villagehacker — 13.12.2010 @ 17:37 | Ответить


RSS feed for comments on this post. TrackBack URI

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

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: