21 September, 2007

More on Bind Variable Peeking and Execution Plans

Here's how Tom Kyte used the example of a the mystery why a query with SQL_TRACE enabled seemed to perform better than the same query by itself --- because it was being parsed as two different queries when Bind Variable Peeking saw different values.
To the many responses about bind variable peeking, he further reviews some of the options.

10 September, 2007

ATOMIC_REFRESH=>FALSE causes TRUNCATE and INSERT behaviour in 10g ??

The 10g Datawarehousing documentation, when speaking of maintaining MViews inadvertently seems to imply that even FAST Refresh's can benefit with TRUNCATE.
Immediately after the text about the ATOMIC_REFRESH parameter (" If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views."), come the words :
For example, to perform a fast refresh on the materialized view cal_month_sales_mv, the DBMS_MVIEW package would be called as follows:
DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', 'F', '', TRUE, FALSE, 0,0,0, FALSE);
seeming to imply that even a FAST Refresh can use a TRUNCATE and INSERT -- which, certainly, cannot be the case.

Furthermore, when I tested (10.2.0.1 on Windows) refreshing 1 and 2 MVs together, I found that the TRUNCATE would occur only if one MV was being refreshed. If 2 MVs were being refreshed, even if ATOMIC_REFRESH was set to FALSE, I saw both suffering DELETE and INSERTs.

These were my test SQLs :

set echo on
set timing on
spool Test_MV_Complete_Refresh
drop materialized view my_dummy_mv;
drop materialized view my_mv_fast;
drop materialized view my_mv_complete;
drop materialized view my_mv2_complete;
drop table my_source_tab purge;
create table my_source_tab as select * from dba_objects where 1=2;
alter table my_source_tab nologging;
insert /*+ APPEND */ into my_source_tab select * from dba_objects;
commit;
delete my_source_tab where object_id is null ;
alter table my_source_tab modify (object_id not null);
create unique index my_source_tab_u1 on my_source_tab(object_id);
alter table my_source_tab add primary key (object_id);
create snapshot log on my_source_tab ;
create materialized view my_mv_fast
refresh fast on demand
as select * from my_source_tab;
create materialized view my_mv_complete
refresh complete on demand
as select * from my_source_tab;
create materialized view my_mv2_complete
refresh complete on demand
as select * from my_source_tab;
create materialized view my_dummy_mv
refresh complete
as select * from dual
where dummy='X' and rownum=1
/
exec dbms_mview.refresh('MY_MV_FAST','F');
exec dbms_mview.refresh('MY_MV_COMPLETE','C');
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_DUMMY_MV','C');
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_MV2_COMPLETE','C');
alter session set tracefile_identifier='T_M_C_R';
alter session set sql_trace=TRUE;
exec dbms_mview.refresh('MY_MV_FAST','F',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE','C',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_DUMMY_MV','C',atomic_refresh=>FALSE);
exec dbms_mview.refresh('MY_MV_COMPLETE,MY_MV2_COMPLETE','C',atomic_refresh=>FALSE);
spool off
exit