Oracle mechanics

23.05.2011

Рекурсивные запросы для materialized view с использованием db link

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

English version

В операциях создания и обновления материализованных представлений (mview) с использованием удалённых таблиц (dblink) план выполнения запроса может существенно отличаться как от плана отдельно выполняемого запроса SELECT, входящего в определение mview, так и от плана отдельного запроса CREATE TABLE AS SELECT (CTAS)

SQL> create table t as select * from dba_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T');

PL/SQL procedure successfully completed.

SQL> create materialized view t_mv as select t1.* from t@db_link tr, t t1,t t2 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

в версии Oracle 11.2 наблюдаются два открытых активных курсора для этой сессии, один из которых имеет тип OPEN-RECURSIVE с забавным значением LAST_SQL_EXEC_TIME  (в версиях 10.2/11.1 поля CURSOR_TYPE в v$open_cursor ещё нет, но рекурсивный запрос также легко найти по тесту в V$SQL.SQL_TEXT)

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_ID        SQL_TEXT                                                    LAST_SQL_ACTIVE_TIME SQL_EXEC_ID CURSOR_TYPE
-------- ---------- ------------------------------ -------- ---------- ------------- ------------------------------------------------------------ -------------------- ----------- --------------
2F230E5C         67 SYSTEM                         233CD018 2317899508 3vcya0y52hprn CREATE TABLE "SYSTEM"."T_MV"  AS select t1.* from t@db_link  01-JAN-70              16777217 OPEN-RECURSIVE
2F230E5C         67 SYSTEM                         2639FCDC 1245822999 2dvw4u1543h0r create materialized view t_mv as select t1.* from t@db_link                         16777217 OPEN


Для рекурсивного (порождённого операцией CREATE MATERIALIZED VIEW) запроса CREATE TABLE «SYSTEM».»T» AS SELECT, оптимизатор определяет кол-во строк (ROWS, CARDINALITY) удалённых таблиц = 1, как следствие может значительно изменить план выполнения (по сравнению с явным выполнением CTAS, например, используя MERGE JOIN  CARTESIAN? как в этом примере или NESTED LOOP вместо HASH JOIN):

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |       |       |    60 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |     1 |   278 |    59   (2)| 00:00:01 |        |      |
|   3 |    MERGE JOIN CARTESIAN|      |     1 |    37 |    30   (0)| 00:00:01 |        |      |
|   4 |     REMOTE             | T    |     1 |    17 |     2   (0)| 00:00:01 | DB_LI~ | R->S |
|   5 |     BUFFER SORT        |      |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   6 |      TABLE ACCESS FULL | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   7 |    TABLE ACCESS FULL   | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

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

4 - SELECT "TABLE_NAME" FROM "T" "TR" (accessing 'DB_LINK' )

При выполнении явного CREATE TABLE AS SELECT, в плане выполнения кол-во строк удалённой таблицы оценивается правильно (Oracle это умеет по крайней мере с версии 10.2, строка REMOTE плана выполнения):

create table t_ctas as select t1.* from t@db_link tr, t t1,t t2 where
tr.table_name = t1.table_name and t2.table_name = t1.table_name

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |       |       |    96 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |  2809 |   762K|    76   (2)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL   | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|*  4 |    HASH JOIN           |      |  2801 |   705K|    48   (3)| 00:00:01 |        |      |
|   5 |     REMOTE             | T    |  2793 | 47481 |    19   (0)| 00:00:01 | DB_LI~ | R->S |
|   6 |     TABLE ACCESS FULL  | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

Такой же эффект наблюдается при полном неатомарном обновлении MV

SQL> exec dbms_mview.refresh('T_MV', method => 'c', atomic_refresh => false);

— рекурсивный INSERT /*+ APPEND*/ так же выбирает план выполнения исходя из неправильной оценки количества строк в удалённой таблице (ROWS=1):

----------------------------------------------------------------------------------------------------
SQL_ID  67bhrr62u207g, child number 0
-------------------------------------
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "SYSTEM"."T_MV"
select t1.* from t@db_link tr, t t1,t t2 where tr.table_name =
t1.table_name and t2.table_name = t1.table_name

Plan hash value: 3159662255

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |      |       |       |    59 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |     1 |   278 |    59   (2)| 00:00:01 |        |      |
|   3 |    MERGE JOIN CARTESIAN|      |     1 |    37 |    30   (0)| 00:00:01 |        |      |
|   4 |     REMOTE             | T    |     1 |    17 |     2   (0)| 00:00:01 | DB_LI~ | R->S |
|   5 |     BUFFER SORT        |      |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   6 |      TABLE ACCESS FULL | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   7 |    TABLE ACCESS FULL   | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

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

2 - access("TR"."TABLE_NAME"="T1"."TABLE_NAME" AND
"T2"."TABLE_NAME"="T1"."TABLE_NAME")

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

4 - SELECT /*+ */ "TABLE_NAME" FROM "T" "TR" (accessing 'DB_LINK' )

В качестве workaround’а при создании mview можно легко обойти проблему, выполнив операции CREATE TABLE AS SELECT и CREATE MVIEW … ON PREBUILD TABLE последовательно, либо использовать подсказки, которые транслируются оптимизатором в рекурсивные запросы (при обновлении mview это единственная мне известная возможность исправить описанную ошибку оптимизатора)

SQL>create materialized view t_mv as select--+ cardinality(tr 10000)
2  t1.* from t@db_link tr, t t1,t t2, t t3, t t4 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

Рекурсивный CTAS при этом будет выполняться с учётом подсказки:

CREATE TABLE "SYSTEM"."T_MV"  AS select--+ cardinality(tr 10000) t1.*
from t@db_link tr, t t1,t t2, t t3, t t4 where tr.table_name =
t1.table_name and t2.table_name = t1.table_name

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |      |       |       |   544M(100)|          |        |      |
|   1 |  LOAD AS SELECT          |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN              |      |    78G|    19T|   849K (92)| 02:50:00 |        |      |
|   3 |    TABLE ACCESS FULL     | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|*  4 |    HASH JOIN             |      |    78G|    18T|   461K (84)| 01:32:19 |        |      |
|   5 |     TABLE ACCESS FULL    | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
|   6 |     MERGE JOIN CARTESIAN |      |    78G|  1235G|    81M(100)|270:03:09 |        |      |
|   7 |      MERGE JOIN CARTESIAN|      |  7800K|       | 74315   (1)| 00:14:52 |        |      |
|   8 |       TABLE ACCESS FULL  | T    |  2793 |       |    28   (0)| 00:00:01 |        |      |
|   9 |       BUFFER SORT        |      |  2793 |       | 74287   (1)| 00:14:52 |        |      |
|  10 |        TABLE ACCESS FULL | T    |  2793 |       |    27   (0)| 00:00:01 |        |      |
|  11 |      BUFFER SORT         |      | 10000 |   166K|    81M(100)|270:03:09 |        |      |
|  12 |       REMOTE             | T    | 10000 |   166K|     0   (0)|          | DB_LI~ | R->S |
-------------------------------------------------------------------------------------------------

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

12 - SELECT /*+ OPT_ESTIMATE (TABLE "TR" ROWS=10000.000000 ) */ "TABLE_NAME" FROM "T"
"TR" (accessing 'DB_LINK' )

В последнем примере множественное картезианское соединение используется только для увеличения времени выполнения запроса — для удобства наблюдения :)

В секции Remote SQL Information показан хинт оптимизатора /*+ OPT_ESTIMATE (TABLE «TR» ROWS=10000.000000 ) */ эквивалентный (или полученный из) подсказке cardinality(tr 10000)

Тестировалось на версиях Oracle 10.2.0.4, 11.2.0.1

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

  1. Другая проблема, связанная с отличием планов выполнения рекурсивных CTAS (create table as select), порождаемых CREATE/REFRESH MATERIALIZED VIEW, с одной стороны и «прямых» CTAS описана в Create Materialized View or Complete Refresh Taking Longer Than CTAS or Insert-Select [ID 763718.1], где упоминается неопубликованный баг с красноречивым названием:
    «Bug.6840494 CBQT DISALLOWED FOR CTAS UNDER CREATE MATERIALIZED VIEW / IAS UNDER REFRESH:
    which was closed as not feasible to fix and, as only workaround, to force optimizer to pick same
    plan for non-refresh operations
    .

    This is caused by CBQT being disabled for mview operations»
    Т.е. ещё и некоторые операции CBO (здесь — Cost Based Query Transformation = CBQT) отключены для MVIEW операций

    комментарий от Igor Usoltsev — 05.07.2011 @ 16:30 | Ответить

    • Игорь, огромное спасибо за такие умные и глубокие статьи по оптимизации запросов! Я почерпнул из них много полезного для себя.
      Хочу поделиться одним способом укрощения оптимизатора, когда он неадекватно себя ведет при обновлении MVIEW. Этот прием можно использовать, когда в запросе все таблицы удаленные (доступны через db_link) и находятся в одной удаленной БД.
      У меня были похожие проблемы при обновлении MVIEW через dbms_mview.refresh(‘…’, method => ‘c’, atomic_refresh => false) — команда выполнялась очень долго, хотя CREATE MATERIALIZED VIEW … (с тем же самым запросом) отрабатывал быстро. Оказалось, что при обновлении оптимизатор выбирал план, который вытягивал удаленные таблицы с CARDINALITY = 1 (вместо нескольких миллионов) и потом локально соединял их через NESTED LOOPS! В тот момент я подумал, что это очередной баг и решил попробовать добиться выполнения запроса на удаленном сервере — ведь все таблицы лежали в удаленной БД и я подумал, что удаленный оптимизатор лучше знает о своих данных. В результате перепробовав кучу хинтов я смог добиться желаемого с помощью подсказки /*+ NO_QUERY_TRANSFORMATION */, которую я добавил в базовый запрос и пересоздал MVIEW. Запрос стал полностью отрабатывать в режиме remote, что значительно ускорило обновление MVIEW через dbms_mview.refresh, т.к. планы в удаленной БД строились более адекватные. Странно, что использование в запросе для MVIEW хинта /*+ DRIVING_SITE(…) */ совершенно никак не повлияло на оптимизатор — при обновлении он продолжал выбирать тот же самый ущербный план.

      комментарий от Марс Ф — 25.09.2014 @ 14:40 | Ответить


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