物化视图完全刷新与direct path load

今天在asktom上看到有人问10g物化视图完全刷新的问题，是与direct path load有关的。而tom的回答是ATOMIC_REFRESH值设为false,mv完全刷新时执行truncate + insert /*+ append */ 。记得以前也在asktom上看到相关问题，当时tom的回答没有说要设置ATOMIC_REFRESH为false，那时的版本应该是9i或8i。如果没记错的话，就说明10g和9i在这个地方是不一样的。查了一下文档，不论9i还是10g ATOMIC_REFRESH的缺省值是true.于是按tom的方法做个测试证实一下：

9i:

SQL> conn user1/abc
Connected.

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec dbms_mview.refresh( 'EMP_MV' );

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

然后tkprof bkup_ora_19341502.trc 1.out sys=no

看一下1.out：

....

truncate table "USER1"."EMP_MV" purge snapshot log

INSERT /*+ APPEND */ INTO "USER1"."EMP_MV"("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO","EMP"."ENAME",
  "EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL","EMP"."COMM",
  "EMP"."DEPTNO" FROM "EMP" "EMP"

....

再测试一下ATOMIC_REFRESH=false的情况：

SQL> conn user1/abc
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> exec dbms_mview.refresh( 'EMP_MV' , atomic_refresh => FALSE );

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

tkprof bkup_ora_19079346.trc 2.out sys=no

2.out中内容如下：

....

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE :=
  SYSDATE+1; BEGIN DBMS_SNAPSHOT.REFRESH_MV ('P0011655A0001', 1, 'USER1',
  'EMP_MV', 'C', '', '0',2); END;

...

看来是调用了DBMS_SNAPSHOT.REFRESH_MV 来刷新的.

下面再来看看DBMS_SNAPSHOT.REFRESH_MV 是direct方式的吗？

SQL> conn user1/abc
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> exec DBMS_SNAPSHOT.REFRESH_MV ('P0011655A0001', 1, 'USER1',  'EMP_MV', 'C', '', '0',2);

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

tkprof bkup_ora_2470112.trc 3.out sys=no

3.out中的内容：

....

truncate table "USER1"."EMP_MV" purge snapshot log

INSERT /*+ APPEND */ INTO "USER1"."EMP_MV"("EMPNO","ENAME","JOB","MGR",
  "HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO","EMP"."ENAME",
  "EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL","EMP"."COMM",
  "EMP"."DEPTNO" FROM "EMP" "EMP"

.....

从上述结果来看，9i中物化视图完全刷新是否执行truncate+insert /*+append*/与ATOMIC_REFRESH的值为true或false是无关的。

而10g的情况是，只有设置ATOMIC_REFRESH=false，物化视图完全刷新才执行trucate+insert /*+append*/

10g测试情况参见tom的测试过程，我自己测试的结果与tom一致。

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064

You Asked

Hi Tom,

We have an MV that is built with NOLOGGING and compress. But after refresh, there is no reduction in the total block numbers as compared to the table. When I see the recursive SQL, I see only insert /*+ bypass_recursive_check */ and there is no APPEND hint there. So I believe there is no direct path load happening. Is that correct? In such a case how can I achieve a direct path load and compression in an MV refresh.

Note that we have compared the size of MV and loaded the same data in a table. The size of the table is half that of MV due to compression.

Regards,
Pratap

and we said...

ATOMIC_REFRESH

if you set that to FALSE, it'll do a truncate + insert /*+ append */ on a FULL refresh.

Beware that of course the data will disappear during the refresh - as long as you are OK with that, it'll direct path.

eg, if you run something like:

connect /

drop table emp;
drop materialized view emp_mv;

create table emp as select * from scott.emp;
alter table emp add constraint emp_pk primary key(empno);

create materialized view emp_mv
refresh complete
as
select * from emp;

exec dbms_monitor.session_trace_enable
exec dbms_mview.refresh( 'EMP_MV' );

column trace new_val TRACE

select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  from v$process a, v$session b, v$parameter c, v$instance d
 where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'
/

disconnect
!tkprof &TRACE ./tk.prf sys=no aggregate=yes
connect /
edit tk.prf

exec dbms_monitor.session_trace_enable
exec dbms_mview.refresh( 'EMP_MV' , atomic_refresh => FALSE );
column trace new_val TRACE

select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  from v$process a, v$session b, v$parameter c, v$instance d
 where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'
/

disconnect
!tkprof &TRACE ./tk.prf sys=no aggregate=yes
connect /
edit tk.prf

tkprof will show the first time that you:

delete from "OPS$TKYTE"."EMP_MV"

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "OPS$TKYTE"."EMP_MV"("EMPNO",
  "ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO",
  "EMP"."ENAME","EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL",
  "EMP"."COMM","EMP"."DEPTNO" FROM "EMP" "EMP"

and the second time you did:

truncate table "OPS$TKYTE"."EMP_MV" purge snapshot log

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO
  "OPS$TKYTE"."EMP_MV"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM",
  "DEPTNO") SELECT "EMP"."EMPNO","EMP"."ENAME","EMP"."JOB","EMP"."MGR",
  "EMP"."HIREDATE","EMP"."SAL","EMP"."COMM","EMP"."DEPTNO" FROM "EMP" "EMP"

 

 

 

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

转载于:http://blog.itpub.net/228190/viewspace-133422/