Oracle mechanics

15.06.2013

Планозависимая ошибка ORA-00600 [kkpamKRange:List]

Filed under: CBO,Oracle — Игорь Усольцев @ 23:01
Tags: ,
11.1.0.7.@ SQL> delete from tb_task
  2   where id in (select
  3                       t.id
  4                  from tb_task t
  5                  join tb_session s
  6                    on t.session_id = s.id
  7                 where s.type_id in (78)
  8                   and t.status = 0)
  9  /
delete from tb_task
                  *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpamKRange:List], [0], [0], [18], [0], [], [], [], [], [], [], []

Понятно, что это — Bug 8529594 OERI[kkpamKRange:List] on DML to table with constraint enforced with partitioned index — superceded, есть патчи, по описанию относится к компоненте:

kkpam — support for mapping predicate keys expressions to equivalent partitions

и возникает после разбора SQL, на этапе проверки записей в дочерних по FK партициях индексов

Тем не менее, ошибка зависит от плана выполнения, и до установки патча/обновления возможны workaround-ы

Итак, «плохой план», приводящий к ошибке, в действительности выглядит вовсе неплохо:)

============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id  | Operation              | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT       |            |       |       |    26 |           |
| 1   |  DELETE                | TB_TASK    |       |       |       |           |
| 2   |   NESTED LOOPS         |            |   584 |   21K |    26 |  00:00:01 |
| 3   |    VIEW                | VW_NSO_1   |   584 |  7592 |    24 |  00:00:01 |
| 4   |     SORT UNIQUE        |            |   584 |   15K |       |           |
| 5   |      NESTED LOOPS      |            |   584 |   15K |    24 |  00:00:01 |
| 6   |       INDEX RANGE SCAN | TB_TASK_IDX|   72K | 1216K |    23 |  00:00:01 |
| 7   |       INDEX RANGE SCAN | TB_SESS_IDX|     1 |    10 |     1 |  00:00:01 |
| 8   |    INDEX UNIQUE SCAN   | SYS_C004300|     1 |    24 |     1 |  00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
6 - access("T"."STATUS"=0)
7 - access("T"."SESSION_ID"="S"."ID" AND "S"."TYPE_ID"=78)
8 - access("ID"="ID")

Поскольку ORA-600 [kkpamKRange:List] связана с партицированными FK индексами, можно лишний раз удостовериться:

SQL> select c.table_name, c.constraint_name, c.r_constraint_name
  2    from dba_constraints c, dba_tables t
  3   where c.constraint_type = 'R'
  4     and (r_owner, r_constraint_name) in
  5         (select owner, constraint_name
  6            from dba_constraints
  7           where table_name in ('TB_TASK', 'TB_SESSION'))
  8     and c.owner = t.owner
  9     and c.table_name = t.table_name
 10     and t.partitioned = 'YES'
 11  /

TABLE_NAME    CONSTRAINT_NAME         R_CONSTRAINT_NAME
------------- ----------------------- ------------------------------
TB_TASK_ATTR  FK_TASK_ATTR_TASK_PART  SYS_C004300                    -- родительский индекс SYS_C004300

SQL> select table_name, column_name, position from dba_cons_columns where constraint_name = 'FK_TASK_ATTR_TASK_PART'
  2  intersect
  3  select table_name, column_name, column_position from dba_ind_columns where table_name = 'TB_TASK_ATTR'
  4  /

TABLE_NAME    COLUMN_NAME             POSITION
------------- ----------------------- ----------
TB_TASK_ATTR  TASK_ID                 1

Интересно, что, меняя план запроса, можно добиться безошибочного выполнения, хотя, естественно, при выполнении по-прежнему требуется доступ к партицированным FK индексам:

SQL> delete/*+ RULE */ from tb_task
  2   where id in (select
  3                       t.id
  4                  from tb_task t
  5                  join tb_session s
  6                    on t.session_id = s.id
  7                 where s.type_id in (78)
  8                   and t.status = 0)
  9  /

28 rows deleted.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Plan hash value: 2005871626

----------------------------------------------------------------
| Id  | Operation                        | Name                |
----------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                     |
|   1 |  DELETE                          | TB_TASK             |
|   2 |   NESTED LOOPS                   |                     |
|   3 |    VIEW                          | VW_NSO_1            |
|   4 |     SORT UNIQUE                  |                     |
|   5 |      NESTED LOOPS                |                     |
|   6 |       TABLE ACCESS BY INDEX ROWID| TB_SESSION          |
|*  7 |        INDEX RANGE SCAN          | IDX_TB_SESSION_TYPE |
|*  8 |       INDEX RANGE SCAN           | TB_TASK_IDX         |
|*  9 |    INDEX UNIQUE SCAN             | SYS_C004300         |
----------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$FEA40FB2" "T"@"SEL$1")
      LEADING(@"SEL$FEA40FB2" "S"@"SEL$1" "T"@"SEL$1")
      INDEX(@"SEL$FEA40FB2" "T"@"SEL$1" ("TB_TASK"."STATUS" "TB_TASK"."ID"
              "TB_TASK"."SESSION_ID"))
      INDEX_RS_ASC(@"SEL$FEA40FB2" "S"@"SEL$1" ("TB_SESSION"."TYPE_ID"))
      USE_NL(@"SEL$FBA59047" "TB_TASK"@"DEL$1")
      LEADING(@"SEL$FBA59047" "VW_NSO_1"@"SEL$FBA59047" "TB_TASK"@"DEL$1")
      INDEX(@"SEL$FBA59047" "TB_TASK"@"DEL$1" ("TB_TASK"."ID"))
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"DEL$1")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$58A6D7F6")
      UNNEST(@"SEL$58A6D7F6")
      OUTLINE_LEAF(@"SEL$FBA59047")
      OUTLINE_LEAF(@"SEL$FEA40FB2")
      RBO_OUTLINE
      OPT_PARAM('optimizer_index_caching' 95)
      OPT_PARAM('optimizer_index_cost_adj' 10)
      DB_VERSION('11.1.0.7')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   7 - access("S"."TYPE_ID"=78)
   8 - access("T"."STATUS"=0 AND "T"."SESSION_ID"="S"."ID")
       filter("T"."SESSION_ID"="S"."ID")
   9 - access("ID"="ID")

Note
-----
   - rule based optimizer used (consider using cbo)

Возможно, RBO помогает исключить ошибку?

Нет, если взять значимую / функциональнцую часть RBO Outline из последнего запроса и использовать в качестве набора подсказок, можно убедиться, что с использованием CBO тот же запрос может безошибочно выполняться с таким же, как и в случае RBO Plan hash value:

SQL> delete
  2    /*+
  3        USE_NL(@"SEL$FEA40FB2" "T"@"SEL$1")
  4        LEADING(@"SEL$FEA40FB2" "S"@"SEL$1" "T"@"SEL$1")
  5        INDEX(@"SEL$FEA40FB2" "T"@"SEL$1" ("TB_TASK"."STATUS" "TB_TASK"."ID"
  6                "TB_TASK"."SESSION_ID"))
  7        INDEX_RS_ASC(@"SEL$FEA40FB2" "S"@"SEL$1" ("TB_SESSION"."TYPE_ID"))
  8        USE_NL(@"SEL$FBA59047" "TB_TASK"@"DEL$1")
  9        LEADING(@"SEL$FBA59047" "VW_NSO_1"@"SEL$FBA59047" "TB_TASK"@"DEL$1")
 10        INDEX(@"SEL$FBA59047" "TB_TASK"@"DEL$1" ("TB_TASK"."ID"))
 11        OUTLINE(@"SEL$1")
 12        OUTLINE(@"SEL$2")
 13        OUTLINE(@"DEL$1")
 14        MERGE(@"SEL$1")
 15        OUTLINE(@"SEL$58A6D7F6")
 16        UNNEST(@"SEL$58A6D7F6")
 17        OUTLINE_LEAF(@"SEL$FBA59047")
 18        OUTLINE_LEAF(@"SEL$FEA40FB2")
 19    */
 20  from tb_task
 21   where id in (select t.id
 22                  from tb_task t
 23                  join tb_session s
 24                    on t.session_id = s.id
 25                 where s.type_id in (78)
 26                   and t.status = 0)
 27  /

28 rows deleted.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '8k21fkg2xs12v','0','+alias +outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Plan hash value: 2005871626

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                     |       |       |   724 (100)|          |
|   1 |  DELETE                          | TB_TASK             |       |       |            |          |
|   2 |   NESTED LOOPS                   |                     |   584 | 21608 |   724   (7)| 00:00:11 |
|   3 |    VIEW                          | VW_NSO_1            |   584 |  7592 |   722   (6)| 00:00:11 |
|   4 |     SORT UNIQUE                  |                     |   584 | 15768 |            |          |
|   5 |      NESTED LOOPS                |                     |   584 | 15768 |   722   (6)| 00:00:11 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TB_SESSION          |   564 |  5640 |     3   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | IDX_TB_SESSION_TYPE |   564 |       |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN           | TB_TASK_IDX         |     1 |    17 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN             | SYS_C004300         |     1 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   1 - SEL$FBA59047
   3 - SEL$FEA40FB2 / VW_NSO_1@SEL$FBA59047
   4 - SEL$FEA40FB2
   6 - SEL$FEA40FB2 / S@SEL$1
   7 - SEL$FEA40FB2 / S@SEL$1
   8 - SEL$FEA40FB2 / T@SEL$1
   9 - SEL$FBA59047 / TB_TASK@DEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      OPT_PARAM('optimizer_index_cost_adj' 10)
      OPT_PARAM('optimizer_index_caching' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$FEA40FB2")
      OUTLINE_LEAF(@"SEL$FBA59047")
      UNNEST(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$58A6D7F6")
      MERGE(@"SEL$1")
      OUTLINE(@"DEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$FBA59047" "VW_NSO_1"@"SEL$FBA59047")
      INDEX(@"SEL$FBA59047" "TB_TASK"@"DEL$1" ("TB_TASK"."ID"))
      LEADING(@"SEL$FBA59047" "VW_NSO_1"@"SEL$FBA59047" "TB_TASK"@"DEL$1")
      USE_NL(@"SEL$FBA59047" "TB_TASK"@"DEL$1")
      INDEX_RS_ASC(@"SEL$FEA40FB2" "S"@"SEL$1" ("TB_SESSION"."TYPE_ID"))
      INDEX(@"SEL$FEA40FB2" "T"@"SEL$1" ("TB_TASK"."STATUS" "TB_TASK"."ID" "TB_TASK"."SESSION_ID"))
      LEADING(@"SEL$FEA40FB2" "S"@"SEL$1" "T"@"SEL$1")
      USE_NL(@"SEL$FEA40FB2" "T"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$FEA40FB2")
      END_OUTLINE_DATA
  */

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

   7 - access("S"."TYPE_ID"=78)
   8 - access("T"."STATUS"=0 AND "T"."SESSION_ID"="S"."ID")
       filter("T"."SESSION_ID"="S"."ID")
   9 - access("ID"="ID")

В то же время попытка съэмулировать такой же план простыми пользовательскими подсказками по-прежнему возвращает ошибку:

SQL> delete from tb_task
  2   where id in (select--+ no_index(S TB_SESS_IDX) use_nl(T S) index(S IDX_TB_SESSION_TYPE) index(T TB_TASK_IDX)
  3                       t.id
  4                  from tb_task t
  5                  join tb_session s
  6                    on t.session_id = s.id
  7                 where s.type_id in (78)
  8                   and t.status = 0)
  9  /
delete from tb_task
                  *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpamKRange:List], [0], [0], [18], [0], [], [], [], [], [], [], []

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

Plan hash value: 2005871626

--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                     |       |       |   724 (100)|          |
|   1 |  DELETE                          | TB_TASK             |       |       |            |          |
|   2 |   NESTED LOOPS                   |                     |   584 | 21608 |   724   (7)| 00:00:11 |
|   3 |    VIEW                          | VW_NSO_1            |   584 |  7592 |   722   (6)| 00:00:11 |
|   4 |     SORT UNIQUE                  |                     |   584 | 15768 |            |          |
|   5 |      NESTED LOOPS                |                     |   584 | 15768 |   722   (6)| 00:00:11 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TB_SESSION          |   564 |  5640 |     3   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | IDX_TB_SESSION_TYPE |   564 |       |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN           | TB_TASK_IDX         |     1 |    17 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN             | SYS_C004300         |     1 |    24 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
...

— несмотря на полное совпадение Plan hash value и секций Alias, Outline, Predicate и Projection!

Тем не менее первый workaround — с использованием «RBO» плана — работает

Другим рабочим workaround-ом оказалось упрощение запроса, в смысле избавления от лишних сущностей:

SQL> delete from BACKA.tb_task t
  2   where t.session_id in
  3         (select s.id from BACKA.tb_session s where s.type_id in (78))
  4     and t.status = 0
  5  /

28 rows deleted.

Plan hash value: 1243722827

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |             |       |       |    24 (100)|          |
|   1 |  DELETE            | TB_TASK     |       |       |            |          |
|   2 |   NESTED LOOPS     |             |   584 | 19856 |    24   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| TB_TASK_IDX | 73229 |  1716K|    23   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| TB_SESS_IDX |     1 |    10 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - access("T"."STATUS"=0)
   4 - access("T"."SESSION_ID"="S"."ID" AND "S"."TYPE_ID"=78)

— безошибочное выполнение с логично более дешёвым планом, естественно ;)

Похоже, что профиль рекурсивных запросов зависит от плана выполнения, и стало быть — в определённом смысле — управляем

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

Комментариев нет.

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