Oracle mechanics

21.07.2014

JPPD в присутствии удалённой таблицы и View Merging

Filed under: CBO,heuristics,Oracle — Игорь Усольцев @ 00:50
Tags: ,

На продакшн системе версии 11.2.* наблюдал, как сам факт использования удалённой (remote) таблицы блокировал использование Join Predicate Push-Down (JPPD) в запросе следующего типа:

select t.char_column, analytic_view.max_id, analytic_view.max_char_column
  from t,
       t@SCOTT_LOOPBACK
         t2,
       (select id1,
               max(id2) keep(dense_rank first order by id1 desc) max_id,
               max(char_column) max_char_column
          from t
         group by id1) analytic_view
 where t.id1 = t2.id1
   and t.id2 = analytic_view.id1
   and t.id1 = 10

- несмотря на то, что удалённая таблица, казалось бы, никакими условиями запроса с inline view связана не была. Т.е. JPPD работает для локальной таблицы T2 и не работает для удалённой.
Использование аналитического запроса в inline view препятствием для применения JPPD не является

Тестовая схема для версий 11.2.0.3/12.1.0.1:

drop table t
/
create table t
as
select level as id1,
       level as id2,
       rpad('x', 100) as char_column
  from dual
connect by level <= 1000000
/
exec dbms_stats.gather_table_stats('','T')
create unique index t_unique_indx1 on t(id1) -- уникальные индексы непринципиальны,
/                                            -- используются лишь для усиления привлекательности индексного доступа /
create unique index t_unique_indx2 on t(id2) -- исключения возможных зависимостей трансформаций от типов индексов
/

Запрос с локальной таблицей T2:

12.1.0.1.ORCL1201@SCOTT SQL> select--+ opt_param('_optimizer_adaptive_plans' 'false') -- для исключения посторонних эффектов
  2         t.char_column, analytic_view.max_id, analytic_view.max_char_column
  3    from t,
  4         t--@SCOTT_LOOPBACK
  5           t2,
  6         (select--+ NO_MERGE
  7                 id1,
  8                 max(id2) keep(dense_rank first order by id1 desc) max_id,
  9                 max(char_column) max_char_column
 10            from t
 11           group by id1) analytic_view
 12   where t.id1 = t2.id1
 13     and t.id2 = analytic_view.id1
 14     and t.id1 = 10
 15  /

1 row selected.

SQL> /

1 row selected.

Elapsed: 00:00:00.00

--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |     1 |   176 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                |     1 |   176 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T              |     1 |   111 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN            | T_UNIQUE_INDX1 |     1 |       |     2   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE         |                |     1 |    65 |     3   (0)| 00:00:01 | -- JPPD работает
|*  5 |    FILTER                       |                |       |       |            |          |
|   6 |     SORT AGGREGATE              |                |     1 |   111 |            |          |
|   7 |      TABLE ACCESS BY INDEX ROWID| T              |     1 |   111 |     3   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | T_UNIQUE_INDX1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - access("T"."ID1"=10)
   5 - filter(COUNT(*)>0)
   8 - access("ID1"="T"."ID2")



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
...
          1  rows processed

- выполняется предсказуемо быстро, используя дважды INDEX UNIQUE SCAN, JPPD красиво формирует VIEW PUSHED PREDICATE, для чего я использовал подсказку NO_MERGE, никак не влияющую на эффект использования/неиспользования JPPD

Важно отметить, что в этом запросе с аналитическим inline view, согласно трейсу CBO подсказка NO_MERGE отключает трансформацию типа simple view merging, в то время как CBQT complex view merging продолжает безуспешно пытаться:

*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$64CF9AE7 (#1) that are valid to merge.
...
CVM:   Checking validity of merging in query block SEL$2 (#2)
...
SVM:     SVM bypassed: Query NO MERGE hint.

- показывает, что обе view merging трансформации (CVM + SVM) работают неразрывно друг от друга, поэтому в дальнейшем буду использовать термин просто view merging
В качестве подтверждения/проверки: того же результата (плана запроса) можно добиться, отключая параметрами эти трансформации по отдельности: или _simple_view_merging=false, или _complex_view_merging=false

Возвращаясь к запросу, при использовании удалённой таблицы T2:

SQL> select--+ PUSH_PRED(analytic_view) opt_param('_optimizer_adaptive_plans' 'false')
  2         t.char_column, analytic_view.max_id, analytic_view.max_char_column
  3    from t,
  4         t@SCOTT_LOOPBACK -- remote
  5           t2,
  6         (select--+ NO_MERGE
  7                 id1,
  8                 max(id2) keep(dense_rank first order by id1 desc) max_id,
  9                 max(char_column) max_char_column
 10            from t
 11           group by id1) analytic_view
 12   where t.id1 = t2.id1
 13     and t.id2 = analytic_view.id1
 14     and t.id1 = 10
 15  /

1 row selected.

Elapsed: 00:00:04.42

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |   202 |       | 29566   (1)| 00:00:02 |        |      |
|   1 |  NESTED LOOPS                 |                |     1 |   202 |       | 29566   (1)| 00:00:02 |        |      |
|   2 |   NESTED LOOPS                |                |     1 |   124 |       |     4   (0)| 00:00:01 |        |      |
|   3 |    REMOTE                     | T              |     1 |    13 |       |     2   (0)| 00:00:01 | SCOTT~ | R->S |
|   4 |    TABLE ACCESS BY INDEX ROWID| T              |     1 |   111 |       |     2   (0)| 00:00:01 |        |      |
|*  5 |     INDEX UNIQUE SCAN         | T_UNIQUE_INDX1 |     1 |       |       |     1   (0)| 00:00:01 |        |      |
|*  6 |   VIEW                        |                |     1 |    78 |       | 29562   (1)| 00:00:02 |        |      | -- *
|   7 |    SORT GROUP BY              |                |  1000K|   105M|   120M| 29562   (1)| 00:00:02 |        |      |
|   8 |     TABLE ACCESS FULL         | T              |  1000K|   105M|       |  4423   (1)| 00:00:01 |        |      | -- **
------------------------------------------------------------------------------------------------------------------------

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

   5 - access("T"."ID1"=10)
   6 - filter("T"."ID2"="ANALYTIC_VIEW"."ID1")

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

   3 - SELECT /*+ */ "ID1" FROM "T" "T2" WHERE "ID1"=10 (accessing 'SCOTT_LOOPBACK' ) -- запрос на противоположную сторону
-- db link формируется в правильном виде, с условием и выполняется по индексу



Statistics
----------------------------------------------------------
...
      16910  consistent gets
      37404  physical reads
...
          1  rows processed

- план запрос заметно меняется в худшую сторону: view merging по-прежнему не выполняется (*) — что и требовалось, а вот нефункционирующий JPPD порождает TABLE ACCESS FULL (**):

SVM:     SVM bypassed: Query NO MERGE hint.
...
SPM: disallowed: statement references remote objects           -- заодно отключается использование SPM
...
JPPD:     JPPD bypassed: View not on right-side of outer-join.

- по достаточно странной причине, замеченной также, например, на версии 11.2.0.2 в комментарии к посту Jonathan Lewis.Pushing Predicates, наряду с более логичной/ожидаемой причиной JPPD bypassed: Remote table referenced, не проявившейся в моих тестах на версиях 11.2.0.3 и 12.1.0.1

Попытка форсировать JPPD хинтом PUSH_PRED, как видно, оптимизатором замечена, но не реализована:

Dumping Hints
=============
  atom_hint=(@=000007FF08776728 err=0 resol=0 used=1 token=1087 org=1 lvl=1 txt=OPT_PARAM ())
  atom_hint=(@=00000000160C0728 err=0 resol=1 used=0 token=895 org=1 lvl=3 txt=PUSH_PRED ("ANALYTIC_VIEW") ) -- resolved, but not used
  atom_hint=(@=000000002035FB28 err=0 resol=1 used=1 token=876 org=1 lvl=2 txt=NO_MERGE )

В качестве возможного решения возникающей проблемы можно было бы использовать появившуюся в 12c возможность использовать PL/SQL функцию в WITH clause:

SQL> CREATE or REPLACE TYPE t_rows AS OBJECT (
  2    id1           NUMBER,
  3    id2           NUMBER,
  4    char_column  VARCHAR2(100)
  5  )
  6  /

Type created.

SQL> CREATE or REPLACE TYPE t_tab IS TABLE OF t_rows
  2  /

Type created.

SQL> with FUNCTION analytic_view (v_id1 IN NUMBER) RETURN t_tab PIPELINED AS
  2  BEGIN
  3    FOR recs IN (
  4      select id1,
  5             id2 max_id,
  6             char_column max_char_column
  7            from t
  8            where id1 = v_id1)
  9    LOOP
 10      PIPE ROW(t_rows(recs.id1, recs.max_id, recs.max_char_column));
 11    END LOOP;
 12    RETURN;
 13  END;
 14  select t.char_column, av.id1, av.char_column
 15    from t,
 16         t@SCOTT_LOOPBACK t2,
 17         table(analytic_view(t.id2)) av
 18  where t.id1 = t2.id1
 19    and t.id2 = av.id1
 20    and t.id1 = 10
 21  /
with FUNCTION analytic_view (v_id1 IN NUMBER) RETURN t_tab PIPELINED AS
*
ERROR at line 1:
ORA-06553: PLS-653: aggregate/table functions are not allowed in PL/SQL scope

- если бы не функциональное ограничение новой фичи

Что не мешает, однако, успешно использовать в запросах такого типа табличные (pipelined) функции:

SQL> CREATE OR REPLACE FUNCTION analytic_view (v_id1 IN NUMBER) RETURN t_tab PIPELINED AS
  2  BEGIN
  3    FOR recs IN (
  4      select id1,
  5             max(id2) keep(dense_rank first order by id1 desc) max_id,
  6             max(char_column) max_char_column
  7            from t
  8            where id1 = v_id1
  9           group by id1)
 10    LOOP
 11      PIPE ROW(t_rows(recs.id1, recs.max_id, recs.max_char_column));
 12    END LOOP;
 13    RETURN;
 14  END;
 15  /

Function created.

SQL> select t.char_column, av.id1, av.char_column
  2    from t,
  3         t@SCOTT_LOOPBACK t2,
  4         table(analytic_view(t.id2)) av
  5  where t.id1 = t2.id1
  6    and t.id2 = av.id1
  7    and t.id1 = 10
  8  /

1 row selected.

Elapsed: 00:00:00.01

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |        |       |    33 (100)|          |        |      |
|   1 |  NESTED LOOPS                      |                |      1 |   126 |    33   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                     |                |      1 |   124 |     4   (0)| 00:00:01 |        |      |
|   3 |    REMOTE                          | T              |      1 |    13 |     2   (0)| 00:00:01 | SCOTT~ | R->S |
|   4 |    TABLE ACCESS BY INDEX ROWID     | T              |      1 |   111 |     2   (0)| 00:00:01 |        |      |
|*  5 |     INDEX UNIQUE SCAN              | T_UNIQUE_INDX1 |      1 |       |     1   (0)| 00:00:01 |        |      |
|*  6 |   COLLECTION ITERATOR PICKLER FETCH| ANALYTIC_VIEW  |      1 |     2 |    29   (0)| 00:00:01 |        |      |
----------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
...
      MERGE(@"SEL$2")                       --***
...
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("T"."ID1"=10)
   6 - filter("T"."ID2"=VALUE(KOKBF$))

 

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
...
          1  rows processed

- не менее эффективно эмулируя JPPD «функционально»

***) при этом в секции Outline Data можно видеть подсказку MERGE, а в трейсе 10053 — очевидный признак выполнения View Merging, согласно How to Check if a View is Merged in a Query (Doc ID 1389253.1):

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0) -- Select-Project-Join

, отключение которого не влияет на эффективность запроса:

SQL> select/*+ NO_MERGE(@"SEL$2")*/ t.char_column, av.id1, av.char_column
  2    from t,
  3         t@SCOTT_LOOPBACK t2,
  4         table(analytic_view(t.id2)) av
  5  where t.id1 = t2.id1
  6    and t.id2 = av.id1
  7    and t.id1 = 10
  8  /

Elapsed: 00:00:00.00

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   214 |    33   (0)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS                       |                |     1 |   214 |    33   (0)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS                      |                |     1 |   124 |     4   (0)| 00:00:01 |        |      |
|   3 |    REMOTE                           | T              |     1 |    13 |     2   (0)| 00:00:01 | SCOTT~ | R->S |
|   4 |    TABLE ACCESS BY INDEX ROWID      | T              |     1 |   111 |     2   (0)| 00:00:01 |        |      |
|*  5 |     INDEX UNIQUE SCAN               | T_UNIQUE_INDX1 |     1 |       |     1   (0)| 00:00:01 |        |      |
|*  6 |   VIEW                              |                |     1 |    90 |    29   (0)| 00:00:01 |        |      |--****
|   7 |    COLLECTION ITERATOR PICKLER FETCH| ANALYTIC_VIEW  |  8168 |       |    29   (0)| 00:00:01 |        |      |
----------------------------------------------------------------------------------------------------------------------

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

   5 - access("T"."ID1"=10)
   6 - filter("T"."ID2"="AV"."SYS_NC_ROWINFO$"."ID1") -- *****



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
...
          1  rows processed

- но демонстрирует:

  • что до преобразований табличная функции представлена в запросе в виде view (****)
  • плюс более читаемый, имхо, вид используемого условия (Filter Predicate) (*****)
About these ads

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

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

RSS-лента комментариев к этой записи. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Отслеживать

Get every new post delivered to your Inbox.

Join 129 other followers

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