ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 包含LOB的物化视图快速刷新

包含LOB的物化视图快速刷新

原创 Linux操作系统 作者:yangtingkun 时间:2009-10-26 21:12:58 0 删除 编辑

以前写过一篇文章描述物化视图是否进行字段级的刷新。当时得出的结论是包含LOB字段的物化视图会进行字段级的刷新,但是这个结论是在9.2上得出的,最近测试发现,10g上的结果有所不同。

物化视图是否根据字段进行刷新(一):http://yangtingkun.itpub.net/post/468/201236

物化视图是否根据字段进行刷新(二):http://yangtingkun.itpub.net/post/468/201607

物化视图是否根据字段进行刷新(三):http://yangtingkun.itpub.net/post/468/203414

 

 

首先在9.2上进行测试:

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 -
Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T
  2  (
  3     ID NUMBER,
  4     NAME VARCHAR2(30),
  5     CONTENTS CLOB,
  6     CONSTRAINT PK_T PRIMARY KEY (ID)
  7  );

表已创建。

SQL> INSERT INTO T
  2  VALUES (1, 'TEST', 'ABCDEFGHIJKL');

已创建 1 行。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT *
  3  FROM T;

实体化视图已创建。

SQL> UPDATE T
  2  SET NAME = 'ABC'
  3  WHERE ID = 1;

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> SELECT A.VALUE || '/' || B.VALUE || '_ora_' || SPID || '.trc'
  2  FROM V$PARAMETER A, V$PARAMETER B, V$SESSION S, V$PROCESS P
  3  WHERE S.PADDR = P.ADDR
  4  AND A.NAME = 'user_dump_dest'
  5  AND B.NAME = 'instance_name'
  6  AND S.SID IN
  7     (
  8             SELECT SID
  9             FROM V$MYSTAT
 10             WHERE ROWNUM = 1
 11     );

A.VALUE||'/'||B.VALUE||'_ORA_'||SPID||'.TRC'
---------------------------------------------------------------------------------
/opt/ora9/admin/testdata/udump/testdata_ora_27043.trc

下面查看对应的trace文件信息,下面只列出和物化视图刷新有关的部分内容:

APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=53 ct=42 lid=53 tim=1227092903531914 hv=4177740527 ad='8db74f30'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #1:c=1000,e=12225,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1227092903519475
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=53 ct=47 lid=53 tim=1227092907098922 hv=1010564567 ad='8db743c4'
BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;
END OF STMT
PARSE #1:c=3000,e=2266,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1227092907098917
XCTEND rlbk=0, rd_only=1
=====================
.
.
.
=====================
PARSING IN CURSOR #21 len=62 dep=2 uid=53 ct=6 lid=53 tim=1227092907516579 hv=2639849752 ad='8db69674'
UPDATE "TEST"."MV_T" SET "ID" = :1,"NAME" = :2 WHERE "ID" = :1
END OF STMT
PARSE #21:c=2999,e=7255,p=0,cr=12,cu=0,mis=1,r=0,dep=2,og=0,tim=1227092907516576
EXEC #21:c=1000,e=178,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,tim=1227092907516804
FETCH #19:c=3999,e=7598,p=0,cr=18,cu=2,mis=0,r=1,dep=1,og=4,tim=1227092907516839
STAT #19 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT ORDER BY (cr=5 r=0 w=0 time=77 us)'
STAT #19 id=2 cnt=1 pid=1 pos=1 bj=0 p='NESTED LOOPS  (cr=5 r=0 w=0 time=56 us)'
STAT #19 id=3 cnt=1 pid=2 pos=1 bj=0 p='VIEW  (cr=3 r=0 w=0 time=35 us)'
STAT #19 id=4 cnt=1 pid=3 pos=1 bj=0 p='SORT UNIQUE (cr=3 r=0 w=0 time=31 us)'
STAT #19 id=5 cnt=1 pid=4 pos=1 bj=43625 p='TABLE ACCESS FULL MLOG$_T (cr=3 r=0 w=0 time=17 us)'
STAT #19 id=6 cnt=1 pid=2 pos=2 bj=43621 p='TABLE ACCESS BY INDEX ROWID T (cr=2 r=0 w=0 time=13 us)'
STAT #19 id=7 cnt=1 pid=6 pos=1 bj=43624 p='INDEX UNIQUE SCAN PK_T (cr=1 r=0 w=0 time=6 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=53 ct=42 lid=53 tim=1227092911037576 hv=855351039 ad='8da37ff0'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #1:c=1000,e=205,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1227092911037567
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1227092911037705

显然物化视图刷新的UPDATE语句只是更新了NAME列,而没有更新LOB列。

下面看看10g的情况:

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T
  2  (
  3     ID NUMBER,
  4     NAME VARCHAR2(30),
  5     CONTENTS CLOB,
  6     CONSTRAINT PK_T PRIMARY KEY (ID)
  7  );

Table created.

SQL> INSERT INTO T
  2  VALUES (1, 'TEST', 'ABCDEFGHIJKL');

1 row created.

SQL> CREATE MATERIALIZED VIEW LOG ON T;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT *
  3  FROM T;

Materialized view created.

SQL> UPDATE T
  2  SET NAME = 'ABC'
  3  WHERE ID = 1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

SQL> SELECT A.VALUE || '/' || B.VALUE || '_ora_' || SPID || '.trc'
  2  FROM V$PARAMETER A, V$PARAMETER B, V$SESSION S, V$PROCESS P
  3  WHERE S.PADDR = P.ADDR
  4  AND A.NAME = 'user_dump_dest'
  5  AND B.NAME = 'instance_name'
  6  AND S.SID IN
  7     (
  8             SELECT SID
  9             FROM V$MYSTAT
 10             WHERE ROWNUM = 1
 11     );

A.VALUE||'/'||B.VALUE||'_ORA_'||SPID||'.TRC'
--------------------------------------------------------------------------
/opt/ora10g/admin/test08/udump/test08_ora_28926.trc

下面检查10g对应的TRACE文件:

*** ACTION NAME:() 2009-10-26 16:22:56.230
*** MODULE NAME:(SQL*Plus) 2009-10-26 16:22:56.230
*** SERVICE NAME:(SYS$USERS) 2009-10-26 16:22:56.230
*** SESSION ID:(137.851) 2009-10-26 16:22:56.230
=====================
PARSING IN CURSOR #1 len=198 dep=1 uid=0 ct=3 lid=0 tim=1227095093975054 hv=4125641360 ad='49f3640'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #1:c=1000,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1227095093975048
=====================
.
.
.
=====================
PARSING IN CURSOR #31 len=78 dep=2 uid=74 ct=6 lid=74 tim=1227095094370773 hv=3148607377 ad='fb528898'
UPDATE "TEST"."MV_T" SET "ID" = :1,"NAME" = :2,"CONTENTS" = :3 WHERE "ID" = :1
END OF STMT
PARSE #31:c=0,e=147,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1227095094370771
=====================
.
.
.
=====================
PARSING IN CURSOR #29 len=11 dep=1 uid=0 ct=44 lid=0 tim=1227095094392143 hv=1180858989 ad='0'
COMMIT WORK
END OF STMT
PARSE #29:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1227095094392141
XCTEND rlbk=0, rd_only=1
EXEC #29:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1227095094392193
EXEC #5:c=245963,e=335838,p=10,cr=1766,cu=87,mis=0,r=1,dep=0,og=1,tim=1227095094392287
=====================
PARSING IN CURSOR #30 len=35 dep=0 uid=74 ct=42 lid=74 tim=1227095097954261 hv=4067503723 ad='0'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #30:c=0,e=175,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1227095097954258
EXEC #30:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1227095097954316

最后看看最新的11.2上是如何处理这个问题的:

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T
  2  (
  3     ID NUMBER,
  4     NAME VARCHAR2(30),
  5     CONTENTS CLOB,
  6     CONSTRAINT PK_T PRIMARY KEY (ID)
  7  );

表已创建。

SQL> INSERT INTO T
  2  VALUES (1, 'TEST', 'ABCDEFGHIJKL');

已创建 1 行。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT *
  3  FROM T;

实体化视图已创建。

SQL> UPDATE T
  2  SET NAME = 'ABC'
  3  WHERE ID = 1;

已更新 1 行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> SELECT A.VALUE || '/' || B.VALUE || '_ora_' || SPID || '.trc'
  2  FROM V$PARAMETER A, V$PARAMETER B, V$SESSION S, V$PROCESS P
  3  WHERE S.PADDR = P.ADDR
  4  AND A.NAME = 'user_dump_dest'
  5  AND B.NAME = 'instance_name'
  6  AND S.SID IN
  7     (
  8             SELECT SID
  9             FROM V$MYSTAT
 10             WHERE ROWNUM = 1
 11     );

A.VALUE||'/'||B.VALUE||'_ORA_'||SPID||'.TRC'
------------------------------------------------------------------------------------
/data/oracle/diag/rdbms/test112/test112/trace/test112_ora_19732.trc

最后看看11.2是如何处理物化视图刷新的:

*** SESSION ID:(130.4161) 2009-10-27 01:02:24.539
*** CLIENT ID:() 2009-10-27 01:02:24.539
*** SERVICE NAME:(SYS$USERS) 2009-10-27 01:02:24.539
*** MODULE NAME:(SQL*Plus) 2009-10-27 01:02:24.539
*** ACTION NAME:() 2009-10-27 01:02:24.539

=====================
PARSING IN CURSOR #7 len=34 dep=0 uid=85 ct=42 lid=85 tim=1256576544538993 hv=3913151867 ad='2aaaac82fe40' sqlid='14ys3d7nmvxbv'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #7:c=0,e=594,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1256576544538977

*** 2009-10-27 01:02:32.433
CLOSE #7:c=0,e=11,dep=0,type=0,tim=1256576552433030
=====================
.
.
.
=====================
PARSING IN CURSOR #5 len=78 dep=2 uid=85 ct=6 lid=85 tim=1256576553255679 hv=3148607377 ad='1e75cd970' sqlid='4w75xwuxurvwj'
UPDATE "TEST"."MV_T" SET "ID" = :1,"NAME" = :2,"CONTENTS" = :3 WHERE "ID" = :1
END OF STMT
PARSE #5:c=0,e=349,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1256576553255674
=====================
.
.
.
=====================
PARSING IN CURSOR #12 len=11 dep=1 uid=0 ct=44 lid=0 tim=1256576553306719 hv=1180858989 ad='0' sqlid='87rnsy1364ymd'
COMMIT WORK
END OF STMT
PARSE #12:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1256576553306714
XCTEND rlbk=0, rd_only=1, tim=1256576553306848
EXEC #12:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1256576553306881
CLOSE #12:c=0,e=6,dep=1,type=3,tim=1256576553306927
EXEC #2:c=540033,e=821985,p=57,cr=3174,cu=100,mis=0,r=1,dep=0,og=1,plh=0,tim=1256576553307067

*** 2009-10-27 01:02:38.059
CLOSE #2:c=0,e=66,dep=0,type=0,tim=1256576558059916
=====================
PARSING IN CURSOR #14 len=35 dep=0 uid=85 ct=42 lid=85 tim=1256576558060755 hv=4067503723 ad='2aaaac82fe40' sqlid='0pgs023t72bmb'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #14:c=0,e=683,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1256576558060750
EXEC #14:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1256576558061023

显然11.2延续了10.2中的情况,没有对物化视图执行字段级的刷新。

 

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405776