物化檢視重新整理遞迴SQL獲取執行計劃報錯

在解決一個物化檢視重新整理問題的時候發現了這個現象，簡單搭建一個測試環境來說明這個問題：

SQL> CREATE TABLE T (OWNER VARCHAR2(30), NAME VARCHAR2(30), TYPE VARCHAR2(18));

表已建立。

SQL> CREATE MATERIALIZED VIEW LOG ON T WITH ROWID, SEQUENCE (OWNER, TYPE) INCLUDING NEW VALUES;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_T
2 REFRESH FAST AS
3 SELECT OWNER, TYPE, COUNT(*) CN
4 FROM T
5 GROUP BY OWNER, TYPE;

實體化檢視已建立。

SQL> INSERT INTO T
2 SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
3 FROM ALL_OBJECTS;

已建立40764行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

會話已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

會話已更改。

SQL> SELECT SPID
2 FROM V$PROCESS P, V$SESSION S
3 WHERE S.PADDR = P.ADDR
4 AND SYS_CONTEXT('USERENV', 'SID') = S.SID;

SPID
------------
4844

下面從得到的trace中找到物化檢視的快速重新整理語句：

.
.
.
=====================
PARSING IN CURSOR #2 len=40 dep=0 uid=57 ct=47 lid=57 tim=30719668925 hv=227083342 ad='284d57a0'
BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;
END OF STMT
PARSE #2:c=124801,e=625749,p=15,cr=517,cu=0,mis=1,r=0,dep=0,og=1,tim=30719668922
BINDS #2:
=====================
.
.
.
=====================
PARSING IN CURSOR #28 len=723 dep=1 uid=57 ct=189 lid=57 tim=30721489107 hv=3661396181 ad='23facb30'
/* MV_REFRESH (MRG) */ MERGE INTO "TEST"."MV_T" "SNA$" USING (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */ "DLT$0"."OWNER" "GB0", "DLT$0"."TYPE" "GB1", SUM(1) "D0" FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."TYPE", "MAS$"."OWNER" FROM "TEST"."MLOG$_T" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) "DLT$0" GROUP BY "DLT$0"."OWNER","DLT$0"."TYPE")"AV$" ON (SYS_OP_MAP_NONNULL("SNA$"."OWNER")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND SYS_OP_MAP_NONNULL("SNA$"."TYPE")=SYS_OP_MAP_NONNULL("AV$"."GB1")) WHEN MATCHED THEN UPDATE SET "SNA$"."CN"="SNA$"."CN"+"AV$"."D0" WHEN NOT MATCHED THEN INSERT ("SNA$"."OWNER", "SNA$"."TYPE", "SNA$"."CN") VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0")
END OF STMT
PARSE #28:c=0,e=22051,p=1,cr=5,cu=4,mis=1,r=0,dep=1,og=1,tim=30721489105
=====================
.
.
.
=====================
PARSING IN CURSOR #23 len=210 dep=2 uid=0 ct=3 lid=0 tim=30721513236 hv=864012087 ad='2861192c'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
EXEC #23:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=30721513235
FETCH #23:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=30721513296
BINDS #28:
kkscoacd
Bind#0
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=10 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=05cd50c0 bln=07 avl=07 flg=09
value="5/24/2011 15:37:21"
Bind#1
acdty=23 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
acflg=10 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=05cd5054 bln=32 avl=24 flg=09
value=
Dump of memory from 0x05CD5054 to 0x05CD506C
5CD5050 D3AD1100 00000000 00000000 [............]
5CD5060 00000000 00000000 00000000 [............]
EXEC #28:c=93600,e=123307,p=0,cr=43900,cu=35,mis=1,r=85,dep=1,og=1,tim=30721612532
STAT #28 id=1 cnt=2 pid=0 pos=1 bj=0 p='MERGE MV_T (cr=43740 pr=0 pw=0 time=98180 us)'
STAT #28 id=2 cnt=85 pid=1 pos=1 bj=0 p='VIEW (cr=43738 pr=0 pw=0 time=82388 us)'
STAT #28 id=3 cnt=85 pid=2 pos=1 bj=0 p='HASH JOIN RIGHT OUTER (cr=43738 pr=0 pw=0 time=82387 us)'
STAT #28 id=4 cnt=0 pid=3 pos=1 bj=52640 p='MAT_VIEW ACCESS FULL MV_T (cr=3 pr=0 pw=0 time=25 us)'
STAT #28 id=5 cnt=85 pid=3 pos=2 bj=0 p='VIEW (cr=43735 pr=0 pw=0 time=82073 us)'
STAT #28 id=6 cnt=85 pid=5 pos=1 bj=0 p='SORT GROUP BY (cr=43735 pr=0 pw=0 time=82073 us)'
STAT #28 id=7 cnt=40764 pid=6 pos=1 bj=52639 p='TABLE ACCESS FULL MLOG$_T (cr=43735 pr=0 pw=0 time=40893 us)'
STAT #21 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE SNAP$ (cr=1 pr=0 pw=0 time=66 us)'
STAT #21 id=2 cnt=1 pid=1 pos=1 bj=217 p='INDEX UNIQUE SCAN I_SNAP1 (cr=1 pr=0 pw=0 time=7 us)'
=====================

雖然從trace中也可以獲取到執行計劃，但是格式看著很不舒服，於是打算直接將MERGE語句從EXPLAIN PLAN FOR進行分析，從而得到執行計劃：

SQL> EXPLAIN PLAN FOR
2 /* MV_REFRESH (MRG) */ MERGE INTO "TEST"."MV_T" "SNA$"
3 USING
4 (
5 SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */
6 "DLT$0"."OWNER" "GB0", "DLT$0"."TYPE" "GB1", SUM(1) "D0"
7 FROM
8 (
9 SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."TYPE", "MAS$"."OWNER"
10 FROM "TEST"."MLOG$_T" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1
11 ) AS OF SNAPSHOT (:2) "DLT$0"
12 GROUP BY "DLT$0"."OWNER","DLT$0"."TYPE"
13 )"AV$"
14 ON
15 (
16 SYS_OP_MAP_NONNULL("SNA$"."OWNER")=SYS_OP_MAP_NONNULL("AV$"."GB0")
17 AND SYS_OP_MAP_NONNULL("SNA$"."TYPE")=SYS_OP_MAP_NONNULL("AV$"."GB1")
18 )
19 WHEN MATCHED THEN UPDATE
20 SET "SNA$"."CN"="SNA$"."CN"+"AV$"."D0"
21 WHEN NOT MATCHED THEN
22 INSERT ("SNA$"."OWNER", "SNA$"."TYPE", "SNA$"."CN")
23 VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0")
24 ;
) AS OF SNAPSHOT (:2) "DLT$0"
*
第 11 行出現錯誤:
ORA-08187: 此處不允許快照表示式

但是Oracle這裡出現了錯誤。

從錯誤資訊看，似乎這裡不允許指定SNAPSHOT表示式，而實際上Oracle的這個語句是內部語句，可以從SNAPSHOT語句的繫結變數也可以看到，這並不是一個常規的資料型別，Oracle在這裡並沒有解析這個值，而是直接顯示了記憶體DUMP的結果。

SQL> EXPLAIN PLAN FOR
  2  /* MV_REFRESH (MRG) */ MERGE INTO "TEST"."MV_T" "SNA$"
  3  USING
  4  (
  5   SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=1000)   */
  6    "DLT$0"."OWNER" "GB0", "DLT$0"."TYPE" "GB1", SUM(1) "D0"
  7   FROM
  8   (
  9    SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."TYPE", "MAS$"."OWNER" 
 10    FROM "TEST"."MLOG$_T" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :1
 11   ) "DLT$0" 
 12   GROUP BY "DLT$0"."OWNER","DLT$0"."TYPE"
 13  )"AV$"
 14  ON
 15  (
 16   SYS_OP_MAP_NONNULL("SNA$"."OWNER")=SYS_OP_MAP_NONNULL("AV$"."GB0")
 17   AND SYS_OP_MAP_NONNULL("SNA$"."TYPE")=SYS_OP_MAP_NONNULL("AV$"."GB1")
 18  )
 19  WHEN MATCHED THEN UPDATE 
 20   SET "SNA$"."CN"="SNA$"."CN"+"AV$"."D0"
 21  WHEN NOT MATCHED THEN
 22   INSERT ("SNA$"."OWNER", "SNA$"."TYPE", "SNA$"."CN")
 23   VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0")
 24  ;

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3001273056

-------------------------------------------------------------------------------------------
|Id| Operation                         |Name        |Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
| 0| MERGE STATEMENT                   |            |   85|  8075 |   167   (1)| 00:00:03 |
| 1|  MERGE                            |MV_T        |     |       |            |          |
| 2|   VIEW                            |            |     |       |            |          |
| 3|    NESTED LOOPS OUTER             |            |   85|  7990 |   167   (1)| 00:00:03 |
| 4|     VIEW                          |            |    1|    41 |   166   (1)| 00:00:02 |
| 5|      SORT GROUP BY                |            |    1|    37 |   166   (1)| 00:00:02 |
|*6|       TABLE ACCESS FULL           |MLOG$_T     |    1|    37 |   165   (0)| 00:00:02 |
| 7|     MAT_VIEW ACCESS BY INDEX ROWID|MV_T        |   85|  4505 |     1   (0)| 00:00:01 |
|*8|      INDEX UNIQUE SCAN            |I_SNAP$_MV_T|    1|       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   6 - filter("MAS$"."SNAPTIME$$">:1)
   8 - access(SYS_OP_MAP_NONNULL("OWNER"(+))=SYS_OP_MAP_NONNULL("AV$"."GB0") AND
              SYS_OP_MAP_NONNULL("TYPE"(+))=SYS_OP_MAP_NONNULL("AV$"."GB1"))

Note
-----
   - dynamic sampling used for this statement

已選擇26行。

好在AS OF SNAPSHOT語法對於執行計劃沒有影響，去掉後就可以獲取這個語句的執行計劃了。