Oracle mechanics

07.11.2015

ORA-01841, Table Expansion и Interval Partitioning в версии 12.1.0.2

Filed under: Oracle,Oracle new features,Partitioning — Игорь Усольцев @ 00:32
Tags: ,

Коллеги-разработчики с радостью сообщили, что наконец-то сломали 12-й Oracle обнаружили проблему, а Александр Шакура подготовил отличный тесткейс:

12.1.0.2@ SQL> create table partit_Tab tablespace users
  2  PARALLEL ( DEGREE 16 INSTANCES 1 ) PARTITION BY RANGE(DT) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  3  ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2005', 'DD-MM-YYYY')))
  4  as
  5  select sysdate dt from dual
  6  union all
  7  select sysdate-300 dt from dual
  8  union all
  9  select sysdate+300 dt from dual
 10  /
 
Table created
 
SQL> select * from partit_Tab ch
  2   where ch.dt < to_date('01-SEP-15','DD-MON-YY')
  3      or ch.dt between to_date('01-SEP-15','DD-MON-YY') and to_date('30-SEP-15','DD-MON-YY')
  4  /
 
DT
-----------
03.01.2015
 
SQL> select * from partit_Tab ch
  2   where ch.dt < date '2015-09-01'
  3      or ch.dt between date '2015-09-01' and date '2015-09-30'
  4  /
 
select * from partit_Tab ch
 where ch.dt < date '2015-09-01'
    or ch.dt between date '2015-09-01' and date '2015-09-30'
 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

В процессе обследования выяснилось, что:

1) ORA-01841 возникает только для 4-х значных форматов года (YYYY), включая Date Literal вида date’2015-09-01′
2) Explain Plan возвращает ту же ошибку, при этом трейс оптимизатора драматически обрывается на преобразовании Table Expansion (TE):

Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
implied predicates generated: "CH"."START_DT"<TO_DATE('2015-09-01','yyyy-mm-dd') OR "CH"."START_DT">=TO_DATE('2015-09-01','yyyy-mm-dd') AND "CH"."START_DT"<=TO_DATE('2015-09-30','yyyy-mm-dd')

, а запрос при отключении этого преобразования select /*+ NO_EXPAND_TABLE(ch) */ … работает без ошибок

3) проблема характерна именно для 12.1.0.2, в предыдущих версиях оптимизатора проблем не обнаружено:

SQL> select/*+ optimizer_features_enable('12.1.0.1') */ * from partit_Tab ch
  2   where ch.dt < date '2015-09-01'
  3      or ch.dt between date '2015-09-01' and date '2015-09-30'
  4  /
 
DT
-----------
03.01.2015

С помощью тех.поддержки удалось найти свежее улучшение, оформленное в виде фикса:

SQL> @fix 14558315
 
   BUGNO VALUE SQL_FEATURE                DESCRIPTION                                             OPTIMIZER_FEATURE_ENABLE
-------- ----- -------------------------- ------------------------------------------------------- ------------------------
14558315     1 QKSFM_CARDINALITY_16615686 table expansion support for interval partitioned tables 12.1.0.2

, отключение которого на уровне системы, сессии или запроса устраняет проблему

Bug 14558315 (TABLE EXPANSION DOES NOT WORK WITH INTERVAL PARTITIONING) я уже упоминал в
Особенности преобразования Table Expansion, и с помощью кейса из этой заметки легко проверить, что баг 14558315 действительно уже исправлен в 12.1.0.2:

12.1.0.2.@ SQL> alter session set "_fix_control"='14558315:on'; -- включен (по умолчанию)

Session altered.

SQL> select/*+ expand_table(t)*/ * from t where x = 10; -- подсказка форсирует TE несмотря на Cost

         X          Y
---------- ----------
        10          1

1 row selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => '+outline'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  81uwwbx7xp974, child number 0
-------------------------------------
select/*+ expand_table(t)*/ * from t where x = 10

Plan hash value: 3514974290

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |       |       |   122 (100)|          |       |       |
|   1 |  VIEW                                        | VW_TE_1 |     3 |    78 |   122   (3)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE                    |         |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T       |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      INDEX RANGE SCAN                        | T_I     |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   6 |    PARTITION RANGE SINGLE                    |         |     1 |     8 |    41   (3)| 00:00:01 |     2 |     2 |
|*  7 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    41   (3)| 00:00:01 |     2 |     2 |
|   8 |    PARTITION RANGE INLIST                    |         |     1 |     8 |    79   (3)| 00:00:01 |       |       |
|*  9 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    79   (3)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      ...
      EXPAND_TABLE(@"SEL$1" "T"@"SEL$1")      -- TE работает, см.план
      ...
      END_OUTLINE_DATA
  */

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

   4 - filter("T"."Y"<2)
   5 - access("X"=10)
   7 - filter(("X"=10 AND "T"."Y">=2 AND "T"."Y"<3))
   9 - filter("X"=10)


SQL> alter session set "_fix_control"='14558315:off'; -- fix_control выключен

Session altered.

SQL> select/*+ expand_table(t)*/ * from t where x = 10;

         X          Y
---------- ----------
        10          1

1 row selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => '+outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  81uwwbx7xp974, child number 1
-------------------------------------
select/*+ expand_table(t)*/ * from t where x = 10

Plan hash value: 3557914527

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |    79 (100)|          |       |       | -- TE не работает
|   1 |  PARTITION RANGE ALL|      |     1 |     8 |    79   (3)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T    |     1 |     8 |    79   (3)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_caching' 10)
      OPT_PARAM('_fix_control' '14558315:0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter("X"=10)

Из описания бага до сих пор следует, что исправление планируется в 12.2, Bugs fixed in each 12.1.0.2 Patch Set Update также не упоминает баг 14558315, разгадываем загадки с support-ом

UPD Bug 21606703 : ORA-1841 SELECTING FROM INTERVAL-PARTITIONED TABLE
Base Bug 21467081 INCONSISTENT RESULTS WHEN NOT USING TO_DATE FUNCTION

4 комментария »

  1. Игорь,
    Пока не получается воспроизвести эту ошибку в 12.1.0.2 (установлен Patch 21188742: DATABASE PATCH FOR ENGINEERED SYSTEMS AND DB IN-MEMORY 12.1.0.2.10 (JUL2015)).
    fix control все умолчательные выставил.

    Часть соответствующего трейса CBO:

    Query transformations (QT)
    **************************
    JF: Checking validity of join factorization for query block SEL$1 (#0)
    JF: Bypassed: not a UNION or UNION-ALL query block.
    ST: not valid since star transformation parameter is FALSE
    TE: Checking validity of table expansion for query block SEL$1 (#0)
    implied predicates generated: «CH».»DT»=TO_DATE(‘ 2015-09-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) AND «CH».»DT»<=TO_DA
    TE(' 2015-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

    TE: Bypassed: No relevant table found.

    Я вижу отличие в формате даты. У меня: syyyy-mm-dd hh24:mi:ss, у Вас: yyyy-mm-dd:

    implied predicates generated: "CH"."START_DT"=TO_DATE(‘2015-09-01′,’yyyy-mm-dd’) AND «CH».»START_DT»<=TO_DATE('2015-09-30','yyyy-mm-dd')
    Попробовал поменять nls-параметры, но пока не могу воспроизвести ошибку ORA-1841.
    Как добиться изменения формата даты в трейсе CBO, тоже сходу не вижу.

    Листинг выполнения тест-кейса:

    SQL> create table partit_Tab tablespace users
    2 PARALLEL ( DEGREE 16 INSTANCES 1 ) PARTITION BY RANGE(DT) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    3 ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2005', 'DD-MM-YYYY')))
    4 as
    5 select sysdate dt from dual
    6 union all
    7 select sysdate-300 dt from dual
    8 union all
    9 select sysdate+300 dt from dual
    10 /

    Table created.

    SQL>
    SQL> select * from partit_Tab ch
    2 where ch.dt < to_date('01-SEP-15','DD-MON-YY')
    3 or ch.dt between to_date('01-SEP-15','DD-MON-YY') and to_date('30-SEP-15','DD-MON-YY')
    4 /

    DT
    ———-
    14.01.2015

    1 row selected.

    SQL>
    SQL> select * from partit_Tab ch
    2 where ch.dt < date '2015-09-01'
    3 or ch.dt between date '2015-09-01' and date '2015-09-30'
    4 /

    DT
    ———-
    14.01.2015

    1 row selected.

    SQL> alter session set events 'trace[sql_optimizer.*]';

    Session altered.

    SQL> explain plan for
    2 select /*+ expand_table(ch)*/* from partit_tab ch
    3 where ch.dt < date '2015-09-01'
    4 or ch.dt between date '2015-09-01' and date '2015-09-30'
    5 /

    Explained.

    SQL> alter session set events 'trace[sql_optimizer.*] off';

    Session altered.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    ———————————————————————————————————————————
    Plan hash value: 3938306002

    ———————————————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
    ———————————————————————————————————————————
    | 0 | SELECT STATEMENT | | 2 | 16 | 2 (0)| 00:00:01 | | | | | |
    | 1 | PX COORDINATOR | | | | | | | | | | |
    | 2 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 16 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
    | 3 | PX BLOCK ITERATOR | | 2 | 16 | 2 (0)| 00:00:01 |KEY(OR)|KEY(OR)| Q1,00 | PCWC | |
    |* 4 | TABLE ACCESS FULL| PARTIT_TAB | 2 | 16 | 2 (0)| 00:00:01 |KEY(OR)|KEY(OR)| Q1,00 | PCWP | |
    ———————————————————————————————————————————

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

    4 — filter("CH"."DT"<=TO_DATE(' 2015-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CH"."DT">=TO_DATE(' 2015-09-01
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "CH"."DT"<TO_DATE(' 2015-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    Мои NLS-параметры:

    SQL> sho parameter nls

    NAME TYPE VALUE
    ———————————— ——————————— ——————————
    nls_calendar string GREGORIAN
    nls_comp string BINARY
    nls_currency string $
    nls_date_format string dd.mm.yyyy
    nls_date_language string AMERICAN
    nls_dual_currency string $
    nls_iso_currency string AMERICA
    nls_language string AMERICAN
    nls_length_semantics string BYTE
    nls_nchar_conv_excp string FALSE
    nls_numeric_characters string .,
    nls_sort string BINARY
    nls_territory string AMERICA
    nls_time_format string HH.MI.SSXFF AM
    nls_time_tz_format string HH.MI.SSXFF AM TZR
    nls_timestamp_format string dd.mm.yyyy hh24:mi:ssxff
    nls_timestamp_tz_format string dd.mm.yyyy hh24:mi:ssxff tzr

    fix for bug 14558315 установлен:

    SQL> select * from table(dbms_xplan.display_cursor( format=> 'outline'));

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————
    SQL_ID fhps19skgd0ms, child number 0
    ————————————-
    select /*+ expand_table(t)*/* from t where x = 10

    Plan hash value: 400543443

    ————————————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    ————————————————————————————————————————
    | 0 | SELECT STATEMENT | | | | 828 (100)| | | |
    | 1 | VIEW | VW_TE_1 | 3 | 78 | 828 (1)| 00:00:01 | | |
    | 2 | UNION-ALL | | | | | | | |
    | 3 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 |
    |* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T | 1 | 8 | 2 (0)| 00:00:01 | 1 | 1 |
    |* 5 | INDEX RANGE SCAN | T_I | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
    | 6 | PARTITION RANGE SINGLE | | 1 | 8 | 276 (1)| 00:00:01 | 2 | 2 |
    |* 7 | TABLE ACCESS FULL | T | 1 | 8 | 276 (1)| 00:00:01 | 2 | 2 |
    | 8 | PARTITION RANGE INLIST | | 1 | 8 | 550 (1)| 00:00:01 | | |
    |* 9 | TABLE ACCESS FULL | T | 1 | 8 | 550 (1)| 00:00:01 | | |
    ————————————————————————————————————————

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
    DB_VERSION('12.1.0.2')
    OPT_PARAM('optimizer_index_caching' 10)
    ALL_ROWS
    OUTLINE_LEAF(@"SET$28A3B2A5_3")
    OUTLINE_LEAF(@"SET$28A3B2A5_2")
    OUTLINE_LEAF(@"SET$28A3B2A5_1")
    OUTLINE_LEAF(@"SET$28A3B2A5")
    EXPAND_TABLE(@"SEL$1" "T"@"SEL$1")
    OUTLINE_LEAF(@"SEL$1B35BA0F")
    OUTLINE(@"SET$28A3B2A5")
    EXPAND_TABLE(@"SEL$1" "T"@"SEL$1")
    OUTLINE(@"SEL$1")
    NO_ACCESS(@"SEL$1B35BA0F" "VW_TE_1"@"SEL$1B35BA0F")
    INDEX_RS_ASC(@"SET$28A3B2A5_1" "T"@"SEL$1" ("T"."X"))
    BATCH_TABLE_ACCESS_BY_ROWID(@"SET$28A3B2A5_1" "T"@"SEL$1")
    FULL(@"SET$28A3B2A5_2" "T"@"SEL$1")
    FULL(@"SET$28A3B2A5_3" "T"@"SEL$1")
    END_OUTLINE_DATA
    */

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

    4 — filter("T"."Y"<2)
    5 — access("X"=10)
    7 — filter(("X"=10 AND "T"."Y">=2 AND "T"."Y"<3))
    9 — filter("X"=10)

    комментарий от Mikhail Velikikh — 10.11.2015 @ 07:08 | Ответить

    • Интересно, Михаил

      У нас проблема стабильно наблюдается на Database Patch Set Update : 12.1.0.2.3 (20299023) x86_64 (Linux, не-Exadata), и, что важно, специалисты поддержки также наблюдают проблему стабильно

      Ваш PSU — июльский, наш — апрельский, попробую проверить на последнем доступном 12.1.0.2.5

      комментарий от Игорь Усольцев — 10.11.2015 @ 11:59 | Ответить

    • На Database Patch Set Update : 12.1.0.2.5 (21359755) Linux x86-64 (Oct 2015) также наблюдается
      , как и на 12.1.0.2.0 Windows x86-64

      комментарий от Игорь Усольцев — 11.11.2015 @ 09:58 | Ответить

  2. В рез-тате новогодней активности техподдержки по нашему SR выпущен для не-DB Enginering System:

    Patch 21467081: INCONSISTENT RESULTS WHEN NOT USING TO_DATE FUNCTION
    Release Oracle 12.1.0.2.3
    Platform Linux x86-64

    комментарий от Игорь Усольцев — 11.01.2016 @ 17:17 | Ответить


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 такие блоггеры, как: