Oracle mechanics

23.02.2018

Наблюдаемые особенности компиляции Matview курсоров на примере 12c Out-of-Place (OOP) Refresh

Filed under: Oracle — Игорь Усольцев @ 23:45
Tags: ,

В процессе тестирования с Максимом Филатовым Patch 20933264: OUT-OF-PLACE COMPLETE REFRESH NOT USING DIRECT LOAD IN POPULATING OUTSIDE TABLE наблюдали следующее воспроизводимое поведение:

12.1.0.2@ SQL> CREATE MATERIALIZED VIEW scott.EMP_SNAPSHOT2 tablespace users REFRESH complete ON DEMAND as SELECT * FROM "SCOTT"."EMP" join "SCOTT"."DEPT" using(deptno)
  2  /

Materialized view created.

SQL> alter session set events '10979 trace name context forever';

Session altered.

SQL> alter session set events '10046 trace name context forever , level 12';

Session altered.

SQL> exec dbms_mview.refresh('"SCOTT"."EMP_SNAPSHOT2"', method => '?', atomic_refresh => false, out_of_place => TRUE)

PL/SQL procedure successfully completed.

После чего SYS.SNAP_REFOP$ содержит три операции:

SQL> select operation#, sql_txt from sys.snap_refop$ where sowner='SCOTT';

OPERATION# SQL_TXT
---------- --------------------------------------------------------------------------------
         7 INSERT INTO "SCOTT"."EMP_SNAPSHOT2"("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDA
           ...

        21 CREATE TABLE %s ("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
           NAME","LOC") tablespace users  AS SELECT  * FROM (SELECT ...

        22 INSERT /*+ APPEND */ INTO "SCOTT"."%s" ("DEPTNO","EMPNO","ENAME","JOB","MGR","HI
           ...

3 rows selected.

— первая из которых (INSERT INTO «SCOTT».»EMP_SNAPSHOT2″) кажется не очень подходящей для исправленной патчем опции out_of_place => TRUE

Трейс 10046 показывает, как текст этого «неподходящего» Conventional Insert не только генерируется, но и успешно извлекается из SNAP_REFOP$:

=====================
PARSING IN CURSOR #140627453745856 len=125 dep=2 uid=0 oct=3 lid=0 tim=2500827299976 hv=3193394297 ad='155d880058' sqlid='fj1r98uz5fp3t'
SELECT operation#, cols, sql_txt FROM sys.snap_refop$   WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND instsite = :3
END OF STMT
...
EXEC #140627453745856:c=0,e=719,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=898211532,tim=2500827300759
FETCH #140627453745856:c=0,e=18,p=0,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=898211532,tim=2500827300800
STAT #140627453745856 id=1 cnt=1 pid=0 pos=1 obj=163 op='TABLE ACCESS BY INDEX ROWID BATCHED SNAP_REFOP$ (cr=3 pr=0 pw=0 time=13 us cost=3 size=35 card=1)'
STAT #140627453745856 id=2 cnt=1 pid=1 pos=1 obj=226232 op='INDEX RANGE SCAN I_SNAP_REFOP1 (cr=2 pr=0 pw=0 time=9 us cost=2 size=0 card=1)'
CLOSE #140627453745856:c=0,e=40,dep=2,type=0,tim=2500827300858
=====================
PARSE ERROR #140627453778344:len=750 dep=1 uid=214 oct=3 lid=214 tim=2500827300887 err=10980 -- **
SELECT "from$_subquery$_003"."DEPTNO_0" "DEPTNO","from$_subquery$_003"."EMPNO_1" "EMPNO","from$_subquery$_003"."ENAME_2" "ENAME","from$_subquery$_003"."JOB_3" "JOB","from$_subquery$_003"."MGR_4" "MGR","from$_subquery$_003"."HIREDATE_5" "HIREDATE","from$_subquery$_003"."SAL_6" "SAL","from$_subquery$_003"."COMM_7" "COMM","from$_subquery$_003"."DNAME_8" "DNAME","from$_subquery$_003"."LOC_9" "LOC" FROM  (SELECT "DEPT"."DEPTNO" "DEPTNO_0","EMP"."EMPNO" "EMPNO_1","EMP"."ENAME" "ENAME_2","EMP"."JOB" "JOB_3","EMP"."MGR" "MGR_4","EMP"."HIREDATE" "HIREDATE_5","EMP"."SAL" "SAL_6","EMP"."COMM" "COMM_7","DEPT"."DNAME" "DNAME_8","DEPT"."LOC" "LOC_9" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO") "from$_subquery$_003"
CLOSE #140627453778344:c=1001,e=9,dep=1,type=0,tim=2500827301008
=====================
...
=====================
PARSING IN CURSOR #140627454740456 len=820 dep=1 uid=214 oct=2 lid=214 tim=2500827507787 hv=1938729673 ad='14ad325e30' sqlid='4j7187jtsxaq9'
/* MV_REFRESH (ITB) */INSERT /*+ APPEND */ INTO "SCOTT"."RV$F58474" ("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DNAME","LOC") SELECT "from$_subquery$_003"."DEPTNO_0","from$_subquery$_003"."EMPNO_1","from$_subquery$_003"."ENAME_2","from$_subquery$_003"."JOB_3","from$_subquery$_003"."MGR_4","from$_subquery$_003"."HIREDATE_5","from$_subquery$_003"."SAL_6","from$_subquery$_003"."COMM_7","from$_subquery$_003"."DNAME_8","from$_subquery$_003"."LOC_9" FROM  (SELECT "DEPT"."DEPTNO" "DEPTNO_0","EMP"."EMPNO" "EMPNO_1","EMP"."ENAME" "ENAME_2","EMP"."JOB" "JOB_3","EMP"."MGR" "MGR_4","EMP"."HIREDATE" "HIREDATE_5","EMP"."SAL" "SAL_6","EMP"."COMM" "COMM_7","DEPT"."DNAME" "DNAME_8","DEPT"."LOC" "LOC_9" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO") "from$_subquery$_003"
END OF STMT

после чего возникает на первый взгляд необоснованный PARSE ERROR…10980 (**), а вот ожидаемый для пропатченного OOP Matview Refresh INSERT /*+ APPEND */ выполняется позднее

10979 event trace частично проясняет запутанную логику:

 NON ATOMIC REFRESH
 ON DEMAND REFRESH
[MV REFRESH METHOD]:Complete: -- ***
Truncate:
/* MV_REFRESH (DEL) */ truncate table "SCOTT"."EMP_SNAPSHOT2" purge snapshot log
Insert:
/* MV_REFRESH (INS) */INSERT INTO "SCOTT"."EMP_SNAPSHOT2"("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DNAME","LOC") SELECT "from$_subquery$_003"."DEPTNO_0","from$_subquery$_003"."EMPNO_1","from$_subquery$_003"."ENAME_2","from$_subquery$_003"."JOB_3","from$_subquery$_003"."MGR_4","from$_subquery$_003"."HIREDATE_5","from$_subquery$_003"."SAL_6","from$_subquery$_003"."COMM_7","from$_subquery$_003"."DNAME_8","from$_subquery$_003"."LOC_9" FROM  (SELECT "DEPT"."DEPTNO" "DEPTNO_0","EMP"."EMPNO" "EMPNO_1","EMP"."ENAME" "ENAME_2","EMP"."JOB" "JOB_3","EMP"."MGR" "MGR_4","EMP"."HIREDATE" "HIREDATE_5","EMP"."SAL" "SAL_6","EMP"."COMM" "COMM_7","DEPT"."DNAME" "DNAME_8","DEPT"."LOC" "LOC_9" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO") "from$_subquery$_003"
 Refresh method picked Complete
Out-place complete refresh SCOTT.EMP_SNAPSHOT2 -- ****
[MV REFRESH Executing]:Complete:Out-Place:Drop Table
[MV REFRESH Executing]:Complete:Out-Place:Create Table
[MV REFRESH Executed]:
/* MV_REFRESH (CTB) */CREATE TABLE RV$F58475 ("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DNAME","LOC") tablespace users  AS SELECT  * FROM (SELECT "from$_subquery$_003"."DEPTNO_0" "DEPTNO","from$_subquery$_003"."EMPNO_1" "EMPNO","from$_subquery$_003"."ENAME_2" "ENAME","from$_subquery$_003"."JOB_3" "JOB","from$_subquery$_003"."MGR_4" "MGR","from$_subquery$_003"."HIREDATE_5" "HIREDATE","from$_subquery$_003"."SAL_6" "SAL","from$_subquery$_003"."COMM_7" "COMM","from$_subquery$_003"."DNAME_8" "DNAME","from$_subquery$_003"."LOC_9" "LOC" FROM  (SELECT "DEPT"."DEPTNO" "DEPTNO_0","EMP"."EMPNO" "EMPNO_1","EMP"."ENAME" "ENAME_2","EMP"."JOB" "JOB_3","EMP"."MGR" "MGR_4","EMP"."HIREDATE" "HIREDATE_5","EMP"."SAL" "SAL_6","EMP"."COMM" "COMM_7","DEPT"."DNAME" "DNAME_8","DEPT"."LOC" "LOC_9" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO") "from$_subquery$_003") WHERE 1 = NULL
[MV REFRESH Executing]:Complete:Out-Place:Insert
[MV REFRESH Executed]:
/* MV_REFRESH (ITB) */INSERT /*+ APPEND */ INTO "SCOTT"."RV$F58475" ("DEPTNO","EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DNAME","LOC") SELECT "from$_subquery$_003"."DEPTNO_0","from$_subquery$_003"."EMPNO_1","from$_subquery$_003"."ENAME_2","from$_subquery$_003"."JOB_3","from$_subquery$_003"."MGR_4","from$_subquery$_003"."HIREDATE_5","from$_subquery$_003"."SAL_6","from$_subquery$_003"."COMM_7","from$_subquery$_003"."DNAME_8","from$_subquery$_003"."LOC_9" FROM  (SELECT "DEPT"."DEPTNO" "DEPTNO_0","EMP"."EMPNO" "EMPNO_1","EMP"."ENAME" "ENAME_2","EMP"."JOB" "JOB_3","EMP"."MGR" "MGR_4","EMP"."HIREDATE" "HIREDATE_5","EMP"."SAL" "SAL_6","EMP"."COMM" "COMM_7","DEPT"."DNAME" "DNAME_8","DEPT"."LOC" "LOC_9" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO") "from$_subquery$_003"

*** 2018-02-08 14:40:13.577
[MV REFRESH Executing]:Complete:Out-Place:Drop Table

— на первом этапе предполагается выполнить обычный NON ATOMIC Complete REFRESH (***), для чего выбираются/генерируются соответствующие TRUNCATE + INSERT INTO, после чего оказывается, что д.б. выполнен Out-place complete refresh (**** Семён Семёныч!) — что, соответственно, требует другого комплекта запросов — CREATE TABLE + INSERT /*+ APPEND */ — теперь всё логично и понятно!)

Для переключения первоначального плана действий (обычный Complete REFRESH) на План Б (OOP MV Refresh), используется очень специальная ошибка:

$ oerr ora 10980
10980, 00000, "prevent sharing of parsed query during Materialized View query generation"

, к счастью, слегка описанная поддержкой в 11.5.10 Errors In Refresh Snapshot Trace Files ORA-10980 (Doc ID 294513.1):

ORA-10980 error is only seen in the trace file, when SQL trace is set.

The user should never see an ORA-10980. This error is raised internally and is cleared interally. It will be raised when Snapshot refresh queries are regenerated and parsed. The materialized view definition query needs to be hard pased always to ensure execution of parse callback functions.

When MV contains aggregates, joins, Set operations, nested MVs, ORA-10980 may be raised as the refresh query could be regenerated based on some Optimizer options.

Whenever some Optimizer features can be used for faster execution or when some indexes can be made use of in the table scan, query regeneration happens. However, these are not the only cases when query regeneration happens and a parse is needed, whose cursor should not be shared

— из чего можно сделать далекоидущие выводы, в частности, можно предположить, что Optimizer features and options могут быть реализованы в процессе компиляции запросов Matview Refresh совсем иначе, чем в обычных курсорах при используемом уровне OFE — т.е. наблюдаемые особенности / отличия в поведении MV Optimizer вполне ожидаемы by design

Реклама

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

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

RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Google+ photo

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

Фотография Twitter

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

Фотография Facebook

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

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

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