物化视图刷新递归SQL获取执行计划报错

最新推荐文章于 2023-07-21 15:52:22 发布

ckawt40802

最新推荐文章于 2023-07-21 15:52:22 发布

阅读量197

点赞数

文章标签：数据库

发现trace中记录的物化视图的快速刷新语句，拿到前台sqlplus中无法获取执行计划。

在解决一个物化视图刷新问题的时候发现了这个现象，简单搭建一个测试环境来说明这个问题：

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语法对于执行计划没有影响，去掉后就可以获取这个语句的执行计划了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-696195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-696195/