MVIEW REFRESH PERCEPTION WITH NON ATOMIC ORACLE

In this article, a big base table with 3 million rows will be used with a materialized view.

Materialized view refresh is one of the major cause for performance improvement in data warehouse system but at the same time, maintaining a huge materialized view is also challenge for downtime, performance and storage.

Ways to identify the refresh:

Check the status of MV refresh

kIsH@x3z6zx9<^>select * from v$mvrefresh;

       SID    SERIAL# CURRMVOWNER                     CURRMVNAME
---------- ---------- ------------------------------- -------------------------------
       141         59 KISH                            MVSAL

Another way is to check the lock type to identify if MVIEW refresh is in progress

kIsH@x3z6zx9<^>select SID,LMODE,REQUEST from v$lock where type='TX';

       SID      LMODE    REQUEST
---------- ---------- ----------
       141          6          0

Oracle also perform nonparallel index rebuild while refresh

kIsH@x3z6zx9<^>select sql_text from v$sql where sql_id='833ur41fuwzcm';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER INDEX "KISH"."XPK_XTBL1" REBUILD  NOPARALLEL

A non atomic refresh takes 12 minutes for 3 million rows to complete for a huge table

kIsH@x3z6zx9<^>exec DBMS_MVIEW.REFRESH('KISH.MVSAL',atomic_refresh => FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:12:48.11 <<<<<<<<<<===============

Lets peek through the MV refresh background activity

non-atomic refresh is said to be performed by truncate + direct path load which speeds up the refresh. Look at the trace and you can see the truncate statement. This will reset the highwater ark in a table and deallocate all the space for reuse.

=====================
PARSING IN CURSOR #47555417504464 len=49 dep=1 uid=91 oct=85 lid=91 tim=1648824217901886 hv=4081377486 ad='e2818e70' sqlid='961j36btn9s6f'
 truncate table "KISH"."MVSAL" purge snapshot log
END OF STMT
PARSE #47555417504464:c=12000,e=18613,p=2,cr=48,cu=1,mis=1,r=0,dep=1,og=1,plh=2001715463,tim=1648824217901879
=====================

SQL ID: 961j36btn9s6f Plan Hash: 2001715463

truncate table "KISH"."MVSAL" purge snapshot log    <<<<<<=== 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          1           0
Execute      1      0.05       0.10        133          2       1868           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.11        133          2       1869           0

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

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cell single block physical read                 6        0.00          0.00
  cell list of blocks physical read               1        0.03          0.03
  enq: RO - fast object reuse                     1        0.00          0.00
  local write wait                                3        0.00          0.00
********************************************************************************

Archive generation is quite high

ARCTIME     ARCSIZE_GB    ARCOUNT     ARCPCT
----------- ---------- ---------- ----------
01-04-22 20 4.94346952        135 .361579485

Check redo switches per hour on sun h7 and h8 which is during non-atomic mode and log switches during atomic mode which is higher during h11 and h12

kIsH@x3z6zx9<^>col Day form a3
col h0 format 99
col h1 format 99
col h2 format 99
col h3 format 99
col h4 format 99
kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
select to_char(first_time,'DY') as DAY,
sum(case to_char(FIRST_TIME,'hh24') when '00' then 1 else 0 end ) as h0,
sum(case to_char(FIRST_TIME,'hh24') when '01' then 1 else 0 end ) as h1,
sum(case to_char(FIRST_TIME,'hh24') when '02' then 1 else 0 end) as h2,
sum(case to_char(FIRST_TIME,'hh24') when '03' then 1 else 0 end) as h3,
kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>kIsH@x3z6zx9<^>  2    3    4    5    6  sum(case to_char(FIRST_TIME,'hh24') when '04' then 1 else 0 end) as h4,
sum(case to_char(FIRST_TIME,'hh24') when '05' then 1 else 0 end) as h5,
sum(case to_char(FIRST_TIME,'hh24') when '06' then 1 else 0 end) as h6,
sum(case to_char(FIRST_TIME,'hh24') when '07' then 1 else 0 end) as h7,
sum(case to_char(FIRST_TIME,'hh24') when '08' then 1 else 0 end) as h8,
sum(case to_char(FIRST_TIME,'hh24') when '09' then 1 else 0 end) as h9,
sum(case to_char(FIRST_TIME,'hh24') when '10' then 1 else 0 end) as h10,
sum(case to_char(FIRST_TIME,'hh24') when '11' then 1 else 0 end) as h11,
sum(case to_char(FIRST_TIME,'hh24') when '12' then 1 else 0 end) as h12,
sum(case to_char(FIRST_TIME,'hh24') when '13' then 1 else 0 end) as h13,
sum(case to_char(FIRST_TIME,'hh24') when '14' then 1 else 0 end) as h14,
sum(case to_char(FIRST_TIME,'hh24') when '15' then 1 else 0 end) as h15,
sum(case to_char(FIRST_TIME,'hh24') when '16' then 1 else 0 end) as h16,
sum(case to_char(FIRST_TIME,'hh24') when '17' then 1 else 0 end) as h17,
sum(case to_char(FIRST_TIME,'hh24') when '18' then 1 else 0 end) as h18,
sum(case to_char(FIRST_TIME,'hh24') when '19' then 1 else 0 end) as h19,
sum(case to_char(FIRST_TIME,'hh24') when '20' then 1 else 0 end) as h20,
sum(case to_char(FIRST_TIME,'hh24') when '21' then 1 else 0 end) as h21,
sum(case to_char(FIRST_TIME,'hh24') when '22' then 1 else 0 end) as h22,
sum(case to_char(FIRST_TIME,'hh24') when '23' then 1 else 0 end) as h23
from v$log_history
  7    8  group by to_char(first_time,'DY')
order by case to_char(first_time,'DY')
when 'SUN' then 0
when 'MON' then 1
when 'TUE' then 2
when 'WED' then 3
when 'THU' then 4
when 'FRI' then 5
when 'SAT' then 6
end ASC;  9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36

DAY  H0  H1  H2  H3  H4   H5   H6   H7   H8   H9  H10  H11  H12  H13  H14  H15  H16  H17  H18  H19  H20  H21  H22  H23
--- --- --- --- --- --- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
SUN   0   0   0   0   0    0    0    1  284  515    0    3    0    0  589  687    0    0   76   71    1    0    0    0
TUE   0   0   0   0   0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0
WED   0   0   0   0   0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    1    0
THU   0   0   0   0   0    0    0    0    0    0    0    0    0    0    1    3    0    0    0    0    0    0    0    0
FRI   0   0   0   0   0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0   24   32
SAT   0   0   0   0   0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    2    7   13    0    0

In an atomic refresh, the undo consumption is too high for million row table because of delete statement

kIsH@x3z6zx9<^>exec dbms_mview.refresh('KISH.MVSAL');
BEGIN dbms_mview.refresh('KISH.MVSAL'); END;

*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 4: '+DATAX/x3z6zx9/datafile/users.259.1098264613'
ORA-15081: failed to submit an I/O operation to a disk
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 4: '+DATAX/x3z6zx9/datafile/users.259.1098264613'
ORA-15081: failed to submit an I/O operation to a disk
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_1723003836$" too small
Process ID: 8527
Session ID: 135 Serial number: 11

Check how fast the undo has grown in an atomic refresh compared to non-atomic

kIsH@x3z6zx9<^>select * from (select segment_name,tablespace_name,bytes/1073741824 GB from dba_segments order by GB desc) where rownum < 6;

SEGMENT_NAME                                                                      TABLESPACE_NAME              GB
--------------------------------------------------------------------------------- -------------------- ----------
_SYSSMU7_2070203016$                                                              UNDOTBS1             7.71105957
XTBL                                                                              USERS                3.44116211
MVSAL                                                                             USERS                1.87792969
YTBL                                                                              USERS                1.81323242
ZTBL                                                                              USERS                1.80493164

Delete statement is used on a lob table

[oracle@x3dbzx36 ~]$ grep 'delete from' x3z6zx9_ora_31831_mv_atomic.trc
delete from system.def$_lob     where (enq_tid = :1)
delete from system.def$_calldest     where (enq_tid = :1)
delete from system.def$_aqcall     where (enq_tid = :1)
 delete from "KISH"."MVSAL"

SQL ID: 6a7chgvffupxz Plan Hash: 3428923576

delete from "KISH"."MVSAL"  <<<==============


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    256.29    3963.24     122190     214313   55397983           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    256.29    3963.24     122190     214313   55397983           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  MVSAL (cr=0 pr=0 pw=0 time=11 us)
  13479067   13479067   13479067   MAT_VIEW ACCESS STORAGE FULL MVSAL (cr=106873 pr=95046 pw=0 time=84527380 us cost=65375 size=210000000 card=30000000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cell single block physical read             27145        0.22        116.47
  log file switch completion                    159        0.28         16.54
  reliable message                             2191        0.02          0.74
  log file switch (checkpoint incomplete)       354        3.84        153.53
  latch: redo allocation                         17        0.02          0.02
  latch free                                      4        0.00          0.00
  cell multiblock physical read                 748        0.22         11.35
  latch: cache buffers lru chain                  1        0.00          0.00
  resmgr:cpu quantum                              1        0.00          0.00
  latch: messages                                 2        0.00          0.00
  latch: redo writing                             4        0.00          0.00
  log file switch (archiving needed)              1     3320.55       3320.55
********************************************************************************

Summary:

  • MV refresh in non atomic mode is faster compared to atomic mode
  • Atomic refresh consume double the undo, redo, archives and temporary area compared to non-atomic refresh
  • Atomic refresh uses conventional path load whereas non-atomic refresh use direct path load
  • Index rebuild happen in non-atomic mode due to direct path operation
  • non-atomic mode is faster than atomic mode for larger datasets but not always

Leave a Reply