Oracle mechanics

15.10.2013

Индексный доступ к таблицам удалённого обзора

Filed under: CBO,hints,Oracle,remote,SQL Tuning — Игорь Усольцев @ 08:53
Tags: ,

Сталкиваясь с локально выполняемыми запросами, использующими удалённые UNION ALL обзоры, обратил внимание на особенности формирования рекурсивных запросов, которые могут оказаться полезными для получения гарантированного индексного доступа к удалённым таблицам

UNION ALL обзоры, в свою очередь, иногда используются для обеспечения беспрерывного доступа к данным таблиц (точнее, материализованных представлений, поочерёдно обновляемых в режиме nonatomic complete с целью экономии времени и ресурсов undo/redo). В тескте такого обзора, конечно, используются какие-то PL/SQL функции или SQL конструкции, обеспечиващие в любой момент времени доступ только к одному наиболее «свежему» источнику данных, не влияющие, однако, на наблюдаемые закономерности

Далее нудно и монотонно последовательно описаны тесты, аналогичные проведённым в Уникальные индексы для Join Predicate Push-Down, для механизма, который можно было бы условно назвать remote JPPD для версий 11g/12c

В Oracle 12c в этом месте никаких улучшений не наблюдается, более того, некоторые правила, например, стимулирование удалённого индексного доступа при использовании уникального индексов локальной таблицы в отличие от предыдущих версий уже не работают

Для демонстрации успешно подходит тестовая схема из вышеуказанной заметки:

drop table t1
/
create table t1(l1, l2 not null, char_column) as select level l1, level l2, lpad('x',100,level) char_column from dual connect by level <= 1000
/
create index t1_l2_idx on t1(l2)
/
drop table t21
/
create table t21 as select level l1, lpad('x',100,level) char_column from dual connect by level <= 1000
/
create index t21_l1_idx on t21(l1)
/
drop table t22
/
create table t22 as select level l1, lpad('x',100,level) char_column from dual connect by level <= 1000
/
create index t22_l1_idx on t22(l1)
/
exec dbms_stats.gather_table_stats('','T1')

exec dbms_stats.gather_table_stats('','T21')

exec dbms_stats.gather_table_stats('','T22')

create database link scott_loopback connect to scott identified by tiger using 
'(DESCRIPTION = ( ADDRESS = (PROTOCOL=tcp) (HOST=localhost) (PORT=1521) ) ( CONNECT_DATA = (SERVICE_NAME=pdborcl) ) )'

Далее тесты проводятся на версии 12.1.0.1 с указанием предыдущих версий при необходимости

Запрос к обзору из одной таблицы выполняется красиво и предсказуемо:

12.1.0.1.@ SQL> create or replace view t2_view
  2  as
  3  select * from t21
  4  /

View created.

SQL> select t2.char_column
  2    from t1,
  3         t2_view@scott_loopback t2
  4   where t1.l1 = t2.l1
  5     and t1.l2 = 555
  6  /

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |     4 (100)|          |     |         |
|   1 |  NESTED LOOPS                        |           |     1 |   113 |     4   (0)| 00:00:01 |     |         | --***
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1        |     1 |     8 |     2   (0)| 00:00:01 |     |         |
|*  3 |    INDEX RANGE SCAN                  | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |     |         |
|   4 |   REMOTE                             | T2_VIEW   |     1 |   105 |     2   (0)| 00:00:01 | SCOTT~ | R->S | -- с точной оценкой Rows удалённого обзора/таблицы
------------------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") -- новый хинт
      ...
      USE_NL(@"SEL$1" "T2"@"SEL$1")                      -- метод соединения определяет наличие предиката в удалённом запросе
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1" (accessing 'SCOTT_LOOPBACK' )
  • удалённый рекурсивный запрос формируется с предикатом по полю «L1» и использует индексный доступ к таблице T21, что и отражено в правильной Cardinality (Rows = 1) плана выполнения. Кроме того заметно, что оптимизатор знает статистику удалённой таблицы
  • интересно, что в 12.1 применяется новая операция TABLE ACCESS BY INDEX ROWID BATCHED, вызванную соответствующей подсказкой BATCH_TABLE_ACCESS_BY_ROWID в секции Outline, несмотря на используемое в запросе условие «=» и указанную справедливую оценку в 1 строку — хотя в арсенале 12c присутствует и классическая одноблочная операция TABLE ACCESS BY INDEX ROWID

С интересующими запросами типа UNION ALL дело обстоит не столь радужно, причём независимо от типа объединяемых таблиц — тесты с упрощённым обзором типа:

create or replace view t2_view
as
select * from t21
union all
select 1, 'a' from dual

— показывают такие же результаты, как и тесты с объединением 2-х реальных таблиц, которые я буду использовать в дальнейшем для правдоподобности:

SQL> create or replace view t2_view
  2  as
  3  select * from t21
  4  union all
  5  select * from t22
  6  /

View created.

Первый тест — на влияние уникальности локального индекса:

SQL> drop index t1_l2_idx
  2  /

Index dropped.

SQL> create unique index t1_l2_idx on t1(l2)
  2  /

Index created.

SQL> select t2.char_column            -- запрос из предыдущего поста
  2    from t1,
  3         t2_view@scott_loopback t2 -- с использованием удалённого обзора
  4   where t1.l1 = t2.l1
  5     and t1.l2 = 555
  6  /

2 rows selected.

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

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     8 (100)|          |        |   |
|*  1 |  HASH JOIN                   |           |     2 |   446 |     8   (0)| 00:00:01 |        |   |    -- *
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |     8 |     2   (0)| 00:00:01 |        |   |
|*  3 |    INDEX UNIQUE SCAN         | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |        |   |
|   4 |   REMOTE                     | T2_VIEW   |  2000 |   419K|     6   (0)| 00:00:01 | SCOTT~ | R->S | -- выбираются все строки обзора
----------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      USE_HASH(@"SEL$1" "T2"@"SEL$1")                                                                      -- *
      ...
  */

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

   1 - access("T1"."L1"="T2"."L1")
   3 - access("T1"."L2"=555)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" (accessing 'SCOTT_LOOPBACK' ) -- используемый HASH JOIN определяет вид рекурсивного запроса?

— в 12c уникальность индекса неожиданно не влияет на рекурсивный запрос к удалённому обзору, который не содержит условий. Как следствие, доступ к удалённым таблицам осуществляется без учёта индексов, что можно видеть и в плане выполнения основного запроса — Rows=2000, и в плане рекурсивного запроса (благо «удалённая» бд находится рядом):

SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid('SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2"' || chr(0)) as SQL_ID from dual;

SQL_ID
-------------
5wb3xzd8ubc0y

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('5wb3xzd8ubc0y'));

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |    14 (100)|          | -- план рекурсивного запроса
|   1 |  VIEW               | T2_VIEW |  2000 |   419K|    14   (0)| 00:00:01 |
|   2 |   UNION-ALL         |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T21     |  1000 |   102K|     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T22     |  1000 |   102K|     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------

В версиях 11.2 и ниже факт уникальности имеет значение и план выполнения того же запроса выглядит много приятнее:

11.2.0.3.@ SQL> select t2.char_column
  2    from t1,
  3         t2_view@scott_loopback t2
  4   where t1.l1 = t2.l1
  5     and t1.l2 = 555
  6  /

2 rows selected.

Plan hash value: 452017433

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     8 (100)|          |        |   |
|   1 |  NESTED LOOPS                |           |     2 |   446 |     8   (0)| 00:00:01 |        |   |    -- Тип соединения, определяемый
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |     8 |     2   (0)| 00:00:01 |        |   |
|*  3 |    INDEX UNIQUE SCAN         | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |        |   |    -- наличием уникального ключа
|   4 |   REMOTE                     | T2_VIEW   |     2 |   430 |     6   (0)| 00:00:01 | SCOTT~ | R->S | -- Ожидается всего 2 строки
----------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      USE_NL(@"SEL$1" "T2"@"SEL$1") -- ***
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1" (accessing 'SCOTT_LOOPBACK' )

SQL> -- sql_id и план для рекурсивного запроса
SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid('SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1"' || chr(0)) as SQL_ID from dual;

SQL_ID
-------------
9c1d95gaxu4yt

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9c1d95gaxu4yt','0'));

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |       |       |     2 (100)|          |
|   1 |  VIEW                         | T2_VIEW    |     2 |   210 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL                   |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T22        |     1 |   105 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | T22_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   4 - access("L1"=:1)
   6 - access("L1"=:1)

— рекурсивный запрос по возможности использует индексный доступ независимо от состояния одного из «удалённых» индексов:

11.2.0.3.@ SQL> alter index T22_L1_IDX unusable
  2  /

Index altered.

SQL> select t2.char_column
  2    from t1,
  3         t2_view@scott_loopback t2
  4   where t1.l1 = t2.l1
  5     and t1.l2 = 555
  6  /

2 rows selected.

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

Plan hash value: 452017433                                                                                 -- план не изменился

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     8 (100)|          |        |   |
|   1 |  NESTED LOOPS                |           |     2 |   446 |     8   (0)| 00:00:01 |        |   |    -- --//--
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |     8 |     2   (0)| 00:00:01 |        |   |
|*  3 |    INDEX UNIQUE SCAN         | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |        |   |
|   4 |   REMOTE                     | T2_VIEW   |     2 |   430 |     6   (0)| 00:00:01 | SCOTT~ | R->S |
----------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1" (accessing 'SCOTT_LOOPBACK' ) -- Для правильного запроса с предикатом

SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid('SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1"' || chr(0)) as SQL_ID from dual;

SQL_ID
-------------
9c1d95gaxu4yt

SQL> select child_number from v$sql where sql_id = '9c1d95gaxu4yt';

CHILD_NUMBER
------------
           1                                                                                     -- сгенерирован новый курсор

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9c1d95gaxu4yt','1'));

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |       |       |    12 (100)|          |     -- с новым
|   1 |  VIEW                         | T2_VIEW    |     2 |   210 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL                   |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |     -- частично индексным планом
|*  5 |    TABLE ACCESS FULL          | T22        |     1 |   105 |     7   (0)| 00:00:01 |     -- --//--
--------------------------------------------------------------------------------------------

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

   4 - access("L1"=:1)
   5 - filter("L1"=:1)

В случае неуникального локального индекса Oracle использует FULL TABLE SCAN удалённых таблиц независимо от версии:

12.1.0.1.@ SQL> drop index t1_l2_idx
  2  /

Index dropped.

SQL> create index t1_l2_idx on t1(l2)
  2  /

Index created.

SQL> select t2.char_column
  2    from t1,
  3         t2_view@scott_loopback t2
  4   where t1.l1 = t2.l1
  5     and t1.l2 = 555
  6  /

2 rows selected.

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |     8 (100)|          |     |         |
|*  1 |  HASH JOIN                           |           |     2 |   446 |     8   (0)| 00:00:01 |     |         |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1        |     1 |     8 |     2   (0)| 00:00:01 |     |         |
|*  3 |    INDEX RANGE SCAN                  | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |     |         |
|   4 |   REMOTE                             | T2_VIEW   |  2000 |   419K|     6   (0)| 00:00:01 | SCOTT~ | R->S | -- 2000 Rows
------------------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1") -- отличие 12с
      ...
      USE_HASH(@"SEL$1" "T2"@"SEL$1")                    -- определяющий хинт
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" (accessing 'SCOTT_LOOPBACK' ) -- рекурсивный запрос без кондиций

Похоже, что включение в рекурсивный удалённый запрос условий WHERE :1=»L1″ и, как следствие, индексный доступ к таблицам на дальней стороне линка определяется локально используемым методом соединения NESTED LOOPS,
который можно попробовать искусственно форсировать подсказкой USE_NL:

12.1.0.1.@ SQL> select--+ USE_NL(t1 t2)
  2         t2.char_column
  3    from t1,
  4         t2_view@scott_loopback t2
  5   where t1.l1 = t2.l1
  6     and t1.l2 = 555
  7  /

2 rows selected.

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |     8 (100)|          |     |         |
|   1 |  NESTED LOOPS                        |           |     2 |   446 |     8   (0)| 00:00:01 |     |         |   -- правильный метод
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1        |     1 |     8 |     2   (0)| 00:00:01 |     |         |
|*  3 |    INDEX RANGE SCAN                  | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |     |         |
|   4 |   REMOTE                             | T2_VIEW   |     2 |   430 |     6   (0)| 00:00:01 | SCOTT~ | R->S |   -- правильная оценка для индексного доступа
------------------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      USE_NL(@"SEL$1" "T2"@"SEL$1")                                                                                  -- правильная подсказка
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT /*+ USE_NL ("T2") */ "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1" (accessing 'SCOTT_LOOPBACK' )-- появилось условие в рекурсивном запросе

SQL> -- проверка рекурсивного запроса
SQL> select sys.dbms_sqltune_util0.sqltext_to_sqlid('SELECT /*+ USE_NL ("T2") */ "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2" WHERE :1="L1"' || chr(0)) as SQL_ID from dual;

SQL_ID
-------------
0d29wj0vk022w

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0d29wj0vk022w'));

----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |       |       |     2 (100)|          |
|   1 |  VIEW                                 | T2_VIEW    |     2 |   210 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T22        |     1 |   105 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | T22_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - access("L1"=:1)
   6 - access("L1"=:1)

Или при недоступности одного их индексов:

SQL> alter index T22_L1_IDX unusable;

Index altered.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  0d29wj0vk022w, child number 0
-------------------------------------
SELECT /*+ USE_NL ("T2") */ "L1","CHAR_COLUMN" FROM "T2_VIEW" "T2"
WHERE :1="L1"

----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |       |       |    12 (100)|          |
|   1 |  VIEW                                 | T2_VIEW    |     2 |   210 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T21        |     1 |   105 |     2   (0)| 00:00:01 | -- ***
|*  4 |     INDEX RANGE SCAN                  | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL                  | T22        |     1 |   105 |     7   (0)| 00:00:01 | -- ***
----------------------------------------------------------------------------------------------------

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

   4 - access("L1"=:1)
   5 - filter("L1"=:1)

Подсказки JPPD-типа PUSH_PRED(t2) / OLD_PUSH_PRED(t2) с удалённым обзором не срабатывают, хотя механизм OJPPD может работать с удалёнными таблицами (как будет видно далее)

Получается, что индексный доступ (через формирование правильного рекурсивного запроса с условиями) к таблицам, составляющими удалённый UNION ALL обзор выбирается при использовании NESTED LOOPS в качестве способа соединения с данными локальной таблицы:

  • в версиях до 12.1 при доступе к локальной таблице по уникальному ключу
  • при форсировании использования NESTED LOOPS подсказкой USE_NL

P.S. в качестве альтернативного варианта решения той же задачи логично было бы попробовать использовать локальный обзор для объединения удалённых таблиц:

12.1.0.1.@ SQL> create or replace view t2_local_view
  2  as
  3  select * from t21@scott_loopback
  4  union all
  5  select * from t22@scott_loopback
  6  /

View created.

, однако при недоступности одного из индексов:

SQL> alter index T22_L1_IDX unusable;

Index altered.

— аналогичный тестовому запрос генерирует отличный план, но возвращает ошибку:

SQL> select
  2         t2.char_column
  3    from t1,
  4         t2_local_view t2
  5   where t1.l1 = t2.l1
  6     and t1.l2 = 555
  7  /
ERROR:
ORA-01502: index 'SCOTT.T22_L1_IDX' or partition of such index is in unusable state
ORA-02063: preceding line from SCOTT_LOOPBACK

no rows selected

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

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |       |       |     4 (100)|          |        |      |
|   1 |  NESTED LOOPS                        |               |     2 |   446 |     4   (0)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1            |     1 |     8 |     2   (0)| 00:00:01 |        |      |
|*  3 |    INDEX RANGE SCAN                  | T1_L2_IDX     |     1 |       |     1   (0)| 00:00:01 |        |      |
|   4 |   VIEW                               | T2_LOCAL_VIEW |     2 |   430 |     2   (0)| 00:00:01 |        |      |
|   5 |    UNION-ALL PARTITION               |               |       |       |            |          |        |      |
|   6 |     REMOTE                           | T21           |     1 |   105 |     2   (0)| 00:00:01 | SCOTT~ | R->S |
|   7 |     REMOTE                           | T22           |     1 |   105 |     2   (0)| 00:00:01 | SCOTT~ | R->S |
----------------------------------------------------------------------------------------------------------------------

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

  /*+
      ...
      OLD_PUSH_PRED(@"SEL$1" "T2"@"SEL$1" ("T22"."L1")) -- хинт, определяющий появление подсказки INDEX в рекурсивных запросах
      ...
  */

Remote SQL Information (identified by operation id):
----------------------------------------------------

   6 - SELECT /*+ INDEX ("T21") */ "L1","CHAR_COLUMN" FROM "T21" "T21" WHERE "L1"=:1 (accessing 'SCOTT_LOOPBACK' )

   7 - SELECT /*+ INDEX ("T22") */ "L1","CHAR_COLUMN" FROM "T22" "T22" WHERE "L1"=:1 (accessing 'SCOTT_LOOPBACK' )

— ошибка порождается автоматически сформированным удалённым запросом, и наблюдалась в аналогичных обстоятельствах как минимум с версии 10g (Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2), и легко воспроизводится локально в версии 12.1:

SQL> var B1 number
SQL> SELECT /*+ INDEX ("T22") */ "L1","CHAR_COLUMN" FROM "T22" "T22" WHERE "L1"=:B1
  2  /
SELECT /*+ INDEX ("T22") */ "L1","CHAR_COLUMN" FROM "T22" "T22" WHERE "L1"=:B1
*
ERROR at line 1:
ORA-01502: index 'SCOTT.T22_L1_IDX' or partition of such index is in unusable state

SQL> select sql_id from v$sql where sql_text like 'SELECT /*+ INDEX ("T22") */ "L1","CHAR_COLUMN" FROM "T22" "T22" WHERE "L1"=:B1%'
  2  /

no rows selected

— судя по отсутствию курсора в Shared Pool, ошибка возникает на этапе его компиляции и порождается обязательностью к исполнению опасной подсказки INDEX:

Index Stats::
  Index: T22_L1_IDX  Col#: 1
    LVLS: 1  #LB: 3  #DK: 1000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
    User hint to use this index                                     -- неразрешимое противоречие со статусом индекса
...
  Best:: AccessPath: IndexRange
  Index: T22_L1_IDX
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.00  Bytes: 0

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

  1. […] Наглядная иллюстрация к теоретической демонстрации из предыдущего поста […]

    Уведомление от Индексный доступ к таблицам удалённого обзора-2: практикум | Oracle mechanics — 18.10.2013 @ 00:39 | Ответить


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