Chanel [K]

面朝大海，春暖花开

Archive for the ‘mv’ tag

可更新分区物化视图Issue

with 4 comments

这个限制其实按照道理不应该存在，但是确实有这个问题，一个可更新的分区物化视图无法刷新会引起分区间记录转移的改动。这句话比较拗口，那么用例子说明一下。

创建一个分区表，注意要enable row movement，这样后面才能去更新分区键的字段值。

  1. CREATE TABLE PARTITION_BY_RANGE
  2. ( FIRST_NAME    VARCHAR2(10),
  3.   MIDDLE_INIT    VARCHAR2(1),
  4.   LAST_NAME    VARCHAR2(10),
  5.   BIRTH_MM    INT NOT NULL,
  6.   BIRTH_DD    INT NOT NULL,
  7.   BIRTH_YYYY    INT NOT NULL)
  8. PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
  9. (PARTITION DOBS_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
  10.  PARTITION DOBS_IN_1972           VALUES LESS THAN (1973, 01 ,01),
  11.  PARTITION DOBS_IN_1973           VALUES LESS THAN (1974, 01 ,01),
  12.  PARTITION DOBS_IN_1974           VALUES LESS THAN (1975, 01 ,01),
  13.  PARTITION DOBS_IN_1975_OR_LATER  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
  14. ENABLE ROW MOVEMENT;

插入分区数据。

  1. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1969', 'A', 'SMITH_1969', 09, 20, 1969);
  2. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1970', 'A', 'SMITH_1970', 09, 20, 1970);
  3. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1971', 'A', 'SMITH_1971', 09, 20, 1971);
  4. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1972', 'A', 'SMITH_1972', 09, 20, 1972);
  5. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1973', 'A', 'SMITH_1973', 09, 20, 1973);
  6. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1974', 'A', 'SMITH_1974', 09, 20, 1974);
  7. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1975', 'A', 'SMITH_1975', 09, 20, 1975);
  8. INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1976', 'A', 'SMITH_1976', 09, 20, 1976);
  9. COMMIT;

创建物化视图日志。

  1. create MATERIALIZED VIEW LOG ON PARTITION_BY_RANGE WITH PRIMARY KEY;

创建分区可更新物化视图。不是分区视图不会有问题，不是可更新视图也不会问题。

  1. create MATERIALIZED VIEW mv_pr 
  2. PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
  3. (PARTITION MV_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
  4.  PARTITION MV_IN_1972           VALUES LESS THAN (1973, 01 ,01),
  5.  PARTITION MV_IN_1973           VALUES LESS THAN (1974, 01 ,01),
  6.  PARTITION MV_IN_1974           VALUES LESS THAN (1975, 01 ,01),
  7.  PARTITION MV_IN_1975_OR_LATER  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
  8. refresh fast with primary key for update 
  9. as 
  10. select * from PARTITION_BY_RANGE;

随便修改基表的几条数据，目的是引起分区记录的转移，比如将DOBS_IN_1972分区内的记录全部更新到DOBS_IN_1973中。

然后，尝试做物化视图的手动刷新，将会报错。

  1. SQL> exec dbms_mview.refresh(list => 'MV_PR');
  2.  
  3. begin dbms_mview.refresh(list => 'MV_PR'); end;
  4.  
  5. ORA-12008: error in materialized view refresh path
  6. ORA-14402: updating partition key column would cause a partition change
  7. ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
  8. ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
  9. ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
  10. ORA-06512: at line 2

目前没有什么可以解决的方法，只能是修改为非分区的或者只读的物化视图。

Written by kamus

November 15th, 2007 at 10:43 pm

Posted in Oracle RDBMS

Tagged with ,

探讨实体化视图的刷新机制

with 8 comments

今天给客户搭建历史查询服务器，用oracle8i的snapshot实现，也就是9i的实体化视图。

顺手做了一下trace，看了一下刷新时候oracle后台是怎么工作的。

前期准备，使用DBMS_SUPPORT包，这个包默认是没有安装的，需要运行下面的命令来手动安装。

  1. SQL>conn / as sysdba
  2. SQL>@?\rdbms\admin\dbmssupp.sql
  3. SQL>GRANT execute ON dbms_support TO kamus;
  4. SQL>CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

先看快速刷新，测试表是T1，创建了快照日志，用于刷新的视图是MV_T1，用户是KAMUS

执行trace：

  1. SQL>conn kamus
  2. SQL>exec dbms_support.start_trace(waits=>TRUE,binds=>TRUE);
  3. SQL>exec dbms_mview.refresh(list => 'MV_T1');
  4. SQL>exec dbms_support.stop_trace;

然后tkprof生成trace结果的报表，下面只是节选了其中一部分。

执行一次dbms_mview.refresh，Oracle后台会执行13个 user SQL和92个 internal SQL，实在是一个繁杂的工作。

1。开始刷新

  1. BEGIN dbms_mview.refresh(list => 'MV_T1'); END;

2。检查SNAP$表，确认当前用户是否有需要刷新的视图

3。在DBMS_LOCK_ALLOCATED数据字典中更新记录，设置过期时间

  1. UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400)
  2. WHERE
  3.  ROWID = :B2

4。检查可能会用到的dblink和一些高级队列的数据字典

5。检查表的相关约束

6。检查几个初始化参数的值，包括_enable_refresh_schedule，_delay_index_maintain，compatible

7。将MLOG中所有没有标志为定时刷新的记录更新为立刻刷新

  1. update "KAMUS"."MLOG$_T1" set snaptime$$ = :1 
  2. where
  3.  snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

8。重新编译MV_T1实体化视图

  1. ALTER SUMMARY "KAMUS"."MV_T1" COMPILE

这一步比较可疑，SQL中是没有alter summary找个命令的，如果是编译的话，那么就可能锁定对象，就有可能产生library cache lock

9。检查要执行的SQL文，这一步比较有趣

  1. SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum
  2. FROM
  3.  sys.snap_refop$   WHERE ((operation# >= 0 AND operation# <= 6) OR operation#
  4.   IN (10, 12, 13))   AND sowner = :1 AND vname = :2 AND instsite = :3   ORDER
  5.   BY tabnum, setnum, operation#

对于一个MV刷新将会使用到SQL全部存在这张表中。
如果是fast刷新，那么对于查询mlog表，查询基表的数据，insert、update、delete实体化视图都分别有一句SQL。
其中operation#字段值的常见含义如下：

0：查询mlog表

1：对于实体化视图的delete操作

2：查询基表的最新数据

3：对于实体化视图的update操作

4：对于实体化视图的insert操作

如果是complete刷新，那么只有一条记录，是基于基表的全表insert操作，operation#是7。
此处的执行计划显示是对于snap_refop$的全表扫描，如果系统中存在大量需要refresh的实体化视图，无疑是影响性能的。

10。取得需要更新的记录主键

  1. SELECT DISTINCT LOG$."IDATE"
  2. FROM
  3.  (SELECT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" > :1
  4.   AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."IDATE") NOT IN (SELECT
  5. MAS_TAB$."IDATE" FROM "T1" "MAS_TAB$" WHERE LOG$."IDATE" = MAS_TAB$."IDATE")

注意到这里使用了distinct，也就是我们可以猜测，如果在一次刷新之前对于同一条记录作了多次的修改，那么刷新操作只需要作一次，就是获得基表中该条记录的最新值就可以了。

IDATE字段是我的测试表中的主键。
“DMLTYPE$$” != ‘I’表示不是insert的操作。
此处的执行计划显示对于mlog表进行了一次全表扫描，如果有大量的更新操作，无疑又是影响性能的一步。

11。取得基表中当前需要刷新的记录所有字段的最新值

  1. SELECT CURRENT$."IDATE",CURRENT$."C"
  2. FROM
  3.  (SELECT "T1"."IDATE" "IDATE","T1"."C" "C" FROM "T1" "T1") CURRENT$, (SELECT
  4.   DISTINCT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" >
  5. :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."IDATE" = LOG$."IDATE"

这一步操作表示，mlog中只存储修改操作涉及到的记录主键，其它的字段值仍然会到基表中去作查询。
此处的执行计划显示对于mlog表再一次作了全表扫描。

12。用取得的最新值更新实体化视图

  1. UPDATE "KAMUS"."MV_T1" SET "IDATE" = :1,"C" = :2
  2. WHERE
  3.  "IDATE" = :1

这一步仍然比较奇怪，因为我的测试中只作了insert，并没有update的操作，莫非oracle在刷新时，并不管是否存在update的操作，都会例行作一次视图数据的更新？不过此处更新会使用实体化视图中的主键，速度应该时很快的。

13。将取得的最新值插入到实体化视图中

  1. INSERT INTO "KAMUS"."MV_T1"  ("IDATE","C")
  2. VALUES
  3.  (:1,:2)

这步才到了真正要实现的目的上，呵呵。

14。更新一批数据字典，表明刷新已经完成

15。删除mlog表中已经刷新过的记录

  1. delete from "KAMUS"."MLOG$_T1"
  2. where
  3.  snaptime$$ <= :1

这一步操作是比较耗费资源的，使用delete，产生redo和undo，无法降低mlog表的HWM标志，同时又是一次全表扫描，如果经常有大量更新发生，最好能定时作mlog表的truncate动作，否则这一步操作可能会越来越慢。

至此，一次实体化视图的快速刷新算是完全结束了。

我们继续看一下完全刷新的后台机制。

1-8步跟快速刷新基本相同。

9。检查要执行的SQL文

  1. SELECT operation#, cols, sql_txt
  2. FROM
  3.  sys.snap_refop$   WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND
  4. instsite = :3

可以看到直接去找operation# = 7的SQL了，这就是完全刷新需要使用的SQL。

10。检查完全刷新涉及到的约束，索引，触发器

11。删除实体化视图中的原有数据

  1. delete from "KAMUS"."MV_T"

这一步让我很诧异，记得文档中说应该是truncate操作，但是此处显示的是delete？这样的话，完全刷新的代价实在是很大了。

12。插入基表中所有数据

  1. INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "KAMUS"."MV_T"("X") SELECT "T"."X"
  2. FROM "T" "T"

这里使用到的提示/*+ BYPASS_RECURSIVE_CHECK */，是不是在实际应用中可以提高INSERT的效率呢？

13。更新一批数据字典，表明刷新已经完成

14。如果在基表上创建了刷新日志mlog表，那么Oracle不管这次刷新是不是完全刷新，都会去作一次删除mlog表中数据的操作。如果没有创建过mlog，那么这一步将被省略。所以如果决定使用完全刷新，那么就不要在基表上创建刷新日志了，省得无谓的资源消耗。

Written by kamus

July 7th, 2006 at 5:13 pm

Posted in Oracle RDBMS

Tagged with ,

最简单的创建复制环境中只读实体化视图站点的方法

without comments

目的：创建一个实体化视图站点，定时刷新，获取主站点中指定表的变化，这个实体化站点可以作为查询服务器使用

步骤：

1。主站点上创建实体化视图日志表

  1. CREATE MATERIALIZED VIEW LOG ON kamus.account2004;

2。实体化视图站点上创建公用数据库链接

  1. conn system/password
  2.  
  3. CREATE PUBLIC DATABASE LINK orcl using 'ORCL';

3。实体化视图站点上创建刷新组，本例中3分钟刷新一次

  1. conn system/password
  2.  
  3. BEGIN
  4.    DBMS_REFRESH.MAKE (
  5.       name => 'kamus.test_repg',
  6.       list => '',
  7.       next_date => SYSDATE,
  8.       interval => 'SYSDATE + 3/(24*60)',
  9.       implicit_destroy => FALSE,
  10.       rollback_seg => '',
  11.       push_deferred_rpc => TRUE,
  12.       refresh_after_errors => FALSE);
  13. END;
  14. /
  15. COMMIT;

注意：此处必须commit，否则创建的刷新组并没有真正生效。

4。实体化视图站点上创建用户私有数据库链接

  1. conn kamus/password
  2.  
  3. CREATE DATABASE LINK orcl CONNECT TO kamus IDENTIFIED BY password;

5。实体化视图站点上创建实体化视图

  1. conn kamus/password
  2.  
  3. CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY  AS SELECT * FROM   KAMUS.ACCOUNT2004@orcl;

6。实体化视图站点上将创建的视图加入刷新组

  1. conn kamus/password
  2.  
  3. exec DBMS_REFRESH.ADD(name => 'kamus.TEST_REPG', list => 'kamus.ACCOUNT2004', lax => TRUE);

7。测试，在主站点中更新ACCOUNT2004表，过3分钟检查实体化试图站点中的视图，发现更新已经复制成功。

以上为建立只读实体化视图站点的最简单方法：

不需要创建其它的任何用户，比如复制环境中需要的传播者，刷新者，接收者等等

不需要创建任何复制组

不需要生成任何复制对象，不需要生成任何对象的复制支持

Written by kamus

July 7th, 2006 at 5:06 pm

Posted in Oracle RDBMS

Tagged with , ,