Oracle mechanics

01.06.2014

CBQT при создании/обновлении матвью в 12.1.0.1

Filed under: heuristics,mview,Oracle,Oracle 12c — Игорь Усольцев @ 22:13
Tags: , ,

Тема предыдущего поста про BEGIN_OUTLINE_DATA возникла во время проверки действительно нового и, не побоюсь этого слова, революционного фикса:

SQL> @fix 9852856
 
  BUGNO VALUE SQL_FEATURE          DESCRIPTION                 OPTIMIZER_FEATURE_ENABLE
------- ----- -------------------- --------------------------- ------------------------
9852856     1 QKSFM_CBO_3904125    Enable CBQT for MV refresh  12.1.0.1

, указанного Леонидом Борчуком при обсуждении неоднозначных результатов оптимизации запросов создания/полного обновления материализованных представлений в версиях Oracle 11.2

Это полезное улучшение могло было бы решить множество проблем, но радость была преждевременной

В основном, вышеуказанный фикс упоминается в связи с матвью, содержащими NOT IN — см. Slow Create/Refresh of Materialized View Based on NOT IN Definition Query (Doc ID 1591851.1) и издревле, начиная с 11g для таких конструкций в обычных запросах, включая CTAS, используется преобразование Subquery Unnesting и операция HASH JOIN ANTI NA в плане выполнения:

12.1.0.1.ORCL1201@SCOTT SQL> explain plan for
  2  create table test_mv1
  3  as
  4  select
  5  count(*) as CNT
  6  from   emp
  7  where  mgr not in (select mgr from emp e)
  8  /

Explained.

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |          |     1 |     8 |     7   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | TEST_MV1 |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |     1 |     8 |            |          |
|   3 |    SORT AGGREGATE                |          |     1 |     8 |            |          |
|*  4 |     HASH JOIN ANTI NA            |          |     1 |     8 |     6   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL           | EMP      |    14 |    56 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL           | EMP      |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

При создании матвью в 12.1.0.1 пока по-прежнему всё не так:

SQL> create materialized view test_mv
  2  REFRESH complete ON demand
  3  as
  4  select
  5  count(*)
  6  from   emp
  7  where  mgr not in (select mgr from emp e)
  8  /

Materialized view created.

— даёт по-прежнему неполный (т.е. до описания сгенерированного плана и оценки статистики объетков бд дело не доходит) трейс оптимизатора, из которого тем не менее можно узнать:

******************************************
----- Current SQL Statement for this session (sql_id=693bmjf9rrn8y) -----
create materialized view test_mv
...
*******************************************
...
Considering Query Transformations on query block MISC$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block MISC$1 (#0): rule-based mode. -- про RULE в версии 12c
CBQT: Validity checks failed for 693bmjf9rrn8y.
...
********************
Subquery Unnest (SU)
********************
SU:   Checking validity of unnesting subquery SEL$1 (#0)
SU:     SU bypassed: Anti-join not feasible.                -- про то, что HASH JOIN ANTI не будет
SU:   Validity checks failed.                               -- как, впрочем, и SU

— при этом несмотря на указанный sql_id=693bmjf9rrn8y с текстом create materialized view …, матвью (точнее, подлежащая таблица) строится рекурсивным запросом с лаконичным текстом:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  049vfd72vgk1m, child number 0
-------------------------------------
CREATE TABLE "SCOTT"                 -- текст

Plan hash value: 454878385

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |      |       |       |     8 (100)|          | -- **
|   1 |  LOAD AS SELECT                  |      |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     4 |            |          |
|   3 |    SORT AGGREGATE                |      |     1 |     4 |            |          |
|*  4 |     FILTER                       |      |       |       |            |          | -- *
|   5 |      TABLE ACCESS FULL           | EMP  |    14 |    56 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL           | EMP  |     3 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

— кроме текста, план этого рекурсивного запроса:
*) по-прежнему использует нежелательно медленную операцию FILTER
**) имеет очевидно заниженную стоимость, ибо CTAS запрос с тем же планом логично имеет заметно бОльшую стоимость:

SQL> explain plan for
  2  create table test_mv1
  3  as
  4  select
  5  count(*) as CNT
  6  from   emp
  7  where  mgr not in (select/*+ NO_UNNEST*/ mgr from emp e)
  8  /

Explained.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 454878385 -- тот же план

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |          |     1 |     4 |    28   (0)| 00:00:01 | -- 28 против 8
|   1 |  LOAD AS SELECT                  | TEST_MV1 |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |     1 |     4 |            |          |
|   3 |    SORT AGGREGATE                |          |     1 |     4 |            |          |
|*  4 |     FILTER                       |          |       |       |            |          |
|   5 |      TABLE ACCESS FULL           | EMP      |    14 |    56 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL           | EMP      |     3 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Внимательно прочитав упомянутую ноту, можно заметить упоминание фикса 15950252:

The above mentioned limitation is lifted in 12c (bug 9852856). For 12.1.0.1 the fix for bug 15950252 is needed to eliminate the problem

— без которого обещанное улучшение и не должно работать — RTFM!

И поскольку фикс 15950252 нечасто упоминается в первоисточниках, задал вопрос и получил ответ от техподдержки «большого» Oracle:

Fix for this bug will be included in 12.1.0.2.0. So, right now there is no patch available for 12.1.0.1

— т.е. немного терпения, ведь согласно Release Schedule of Current Database Releases выпуск 12.1.0.2 ожидается не позднее второй половины 2014 календарного года!

Кроме описанной проблемы с операцией FILTER, как и в предыдущих версиях создание/обновление матвью в 12c пока плохо взаимодействует с удалёнными (remote) таблицами (over database link), что порождает странные (по сравнению с CTAS) планы выполнения:

12.1.0.1.ORCL1201@SCOTT SQL> create materialized view test_mv
  2  REFRESH complete ON demand
  3  as
  4  select
  5  count(*)
  6  from   emp
  7  where  mgr not in (select mgr from emp@SCOTT_LOOPBACK e)
  8  /

Materialized view created.

SQL> select sql_id, sql_text from v$sql where sql_text like 'CREATE TABLE "SCOTT"%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
cz985td71k3p5 CREATE TABLE "SCOTT"                    -- тот же забавный текст с другим SQL_ID

SQL> select * from table(dbms_xplan.display_cursor('cz985td71k3p5'));

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |      |       |       |     7 (100)|          |        |   |    -- дешевле чем с локальной таблицей
|   1 |  LOAD AS SELECT                  |      |       |       |            |          |        |   |
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     4 |            |          |        |   |
|   3 |    SORT AGGREGATE                |      |     1 |     4 |            |          |        |   |
|*  4 |     FILTER                       |      |       |       |            |          |        |   |
|   5 |      TABLE ACCESS FULL           | EMP  |    14 |    56 |     3   (0)| 00:00:01 |        |   |
|   6 |      REMOTE                      | EMP  |     1 |    13 |     2   (0)| 00:00:01 | SCOTT~ | R->S | -- *
---------------------------------------------------------------------------------------------------------

*) кол-во строк в удалённой таблице = 1

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

  1. Игорь,
    Спасибо, интересная и полезная статья!
    Указанный документ:
    Slow Create/Refresh of Materialized View Based on NOT IN Definition Query (Doc ID 1591851.1)
    По подобной проблеме разбирался с поддержкой Oracle.
    Oracle 11.2.0.3.7.
    У нас была периодическая проблема при обновлении mat view с commit scn mat view log:

    CREATE MATERIALIZED VIEW LOG ON "APP_USER"."T"
     PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "DATA"
      WITH PRIMARY KEY, COMMIT SCN EXCLUDING NEW VALUES
    /
    
    CREATE MATERIALIZED VIEW "APP_USER"."MV" ("UIN", "BANK_PAYER", "BANK_PAYEE", "WTIME", "STATUS")
      ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "MV_DATA"
      BUILD IMMEDIATE
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "MV_DATA"
      REFRESH FORCE ON DEMAND
      WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
      USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
      AS 
      select uin, bank_payer, bank_payee, wtime, status
        from app_user.t
       where wtime > to_date('20120101','yyyymmdd')
         and status in (2,5,6,9)
    /
    

    Проблемный запрос, для которого не выполняется subquery unnesting( видим наличие специфичной для commit scn mlog таблицы snap_xcmt$):

    select 1 
    from
     (select * from "APP_USER"."MLOG$_T" as of snapshot (:1)               
        where xid$$ not in (select xid from sys.snap_xcmt$))  where rownum = 1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.04       0.07          6         41          0           0
    Fetch        1     60.51      60.51          0    9262442          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3     60.55      60.58          6    9262483          0           0
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS   (recursive depth: 2)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0  COUNT STOPKEY (cr=9262442 pr=0 pw=0 time=60511344 us)
          0   FILTER  (cr=9262442 pr=0 pw=0 time=60511335 us)
      11087    TABLE ACCESS FULL MLOG$_T (cr=4795 pr=0 pw=0 time=82768 us cost=5 size=10 card=1)
       5808    TABLE ACCESS FULL SNAP_XCMT$ (cr=9257647 pr=0 pw=0 time=60359250 us cost=2737 size=11 card=1)
    

    Замена недокументированного as of snapshot на привычный as of scn, или запуск dbms_sqldiag.dump_trace для запроса с as of snapshot дает нужный, но недостижимый при обновлении план:

    sql=/* SQL Analyze(483,0) */ select 1 from (select * from "APP_USER"."MLOG$_T" as of snapshot (:1)                 where xid$$ not in (select xid from sys.snap_xcmt$))  where rownum = 1
    ----- Explain Plan Dump -----
    ----- Plan Table -----
     
    ============
    Plan Table
    ============
    -------------------------------------------------------+-----------------------------------+
    | Id  | Operation                  | Name              | Rows  | Bytes | Cost  | Time      |
    -------------------------------------------------------+-----------------------------------+
    | 0   | SELECT STATEMENT           |                   |       |       |  2742 |           |
    | 1   |  COUNT STOPKEY             |                   |       |       |       |           |
    | 2   |   HASH JOIN RIGHT ANTI SNA |                   |     1 |    33 |  2742 |  00:00:33 |
    | 3   |    TABLE ACCESS FULL       | SNAP_XCMT$        |     1 |    13 |  2737 |  00:00:33 |
    | 4   |    TABLE ACCESS FULL       | MLOG$_T           |     1 |    10 |     5 |  00:00:01 |
    -------------------------------------------------------+-----------------------------------+
    Predicate Information:
    ----------------------
    1 - filter(ROWNUM=1)
    2 - access("XID$$"="XID")
    

    Был открыт баг:
    Bug 18620497 : MATERIALIZED VIEW FAST REFRESH EXTREMELY SLOW
    Который был закрыт как duplicate:
    Bug 9852856 — COST-BASED TRANSFORMATION DISABLED FOR QUERIES ISSUED DURING MV REFRESH
    В ходе работ по SR-у поддержка даже просила этот NOT IN запрос, переписать с NOT EXISTS.
    Но натолкнулись на новый баг Bug 17941893 : ORA-00904: «LOG$».»SNAPTIME$$» WHEN CREATING COMMIT SCN BASED MATERIALIZED VIEW
    В результате, сейчас работаем на timestamp based mat view log-ах.

    комментарий от Mikhail Velikikh — 02.06.2014 @ 16:12 | Ответить

    • Спасибо, Михаил, за дополнительную подробную иллюстрацию проблемы
      Причём, если в описанных мной случаях ещё можно относительно просто влиять на запросы — переписывать, добавлять хинты и т.д.,
      то для ваших глубокорекурсивных запросов в процессе FAST-обновлений проблема, похоже, является блокирующей

      И, похоже, что разработчики трейс оптимизатора специально отключают для запросов, связанных с матвью)

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


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