To determine the Materialized view use, Look at the detail process behaviour behind Materialized view "complete refresh"

Shinjuku Gyoen National Garden,Tokyo,Japan.
-------------------------------------------------


Look at the detail process behaiviour behind Materialized view "complete refresh".
and determine that Oracle 10g guarantees data consistency during "complete refresh"
with atomic_refresh is set to "TRUE".

*Instraction*
[1]create complete refresh trace log.
[2]format trace log using "tkprof" tool
[3]Compare those output
[4]Default Transaction concept on Oracle.


[1]create complete refresh trace log.

>sqlplus /nolog

connect/

--(1)Create complete refresh trace file log, atomic_refresh is set to "TRUE"
alter session set sql_trace = true;

exec DBMS_MVIEW.REFRESH('MVIEW_A','c',atomic_refresh=>TRUE);

alter session set sql_trace = false;

disconnect

--(2)Create complete refresh trace file log, atomic_refresh is set to "FALSE"
alter session set sql_trace = true;

exec DBMS_MVIEW.REFRESH('MVIEW_A','c',atomic_refresh=>FALSE);

alter session set sql_trace = false;

disconnect

----
atomic_refresh If this parameter is set to true, then the list of materialized views is refreshed in a single transaction.
All of the refreshed materialized views are updated to a single point in time.
If the refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction.

References:
61 DBMS_MVIEW-REFRESH Procedures
(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#i997194)



[2]format trace log using "tkprof" tool

> ${oracle_home}/bin/tkprof {your trace file absolute path} {formatted file absolute path} sys=no aggregate=yes;

----
AGGREGATE -- If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple users of the same SQL text.
SYS -- Enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements,
into the output file.
The default value of YES causes TKPROF to list these statements.
The value of NO causes TKPROF to omit them.
This parameter does not affect the optional SQL script.
The SQL script always inserts statistics for all traced SQL statements, including recursive SQL statements.

References:
Using the SQL Trace Facility and TKPROF
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#PFGRF01020)

To see where your trace log is created.
$select name,value from V$parameter where name = 'user_dump_dest';


[3]Compare those output

The following is the "(1)Create complete refresh trace file log, atomic_refresh is set to "TRUE"" result.

delete from "{your_schema}"."MVIEW_A"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 2.14 2.10 0 494 104081 98008
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.15 2.10 0 495 104081 98008

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE MVIEW_A (cr=536 pr=0 pw=0 time=2114914 us)
98008 MAT_VIEW ACCESS FULL MVIEW_A (cr=480 pr=0 pw=0 time=490142 us)

********************************************************************************

The following is the "(2)Create complete refresh trace file log, atomic_refresh is set to "FALSE"" result.

truncate table "{your_schema}"."MVIEW_A" purge snapshot log

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.02 0.16 5 1 208 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.16 5 1 209 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69 (recursive depth: 1)
********************************************************************************


[4]Default Transaction concept on Oracle.

Read committed -- This is the default transaction isolation level.
Each query executed by a transaction sees only data that was committed before the query (not the transaction) began.
An Oracle query never reads dirty (uncommitted) data.
Because Oracle does not prevent other transactions from modifying the data read by a query,
that data can be changed by other transactions between two executions of the query.
Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.

References:
13 Data Concurrency and Consistency-Oracle Isolation Levels
(http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i17856)