Materialized view – NOLOGGING

Compares refreshing materialized view with NOLOGGING

1) Refresh Materialized view with NOLOGGING

-- Capture redo size before refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 148331524

-- Check if the materialized view has nologging turned on
SQL> SELECT logging FROM user_tables WHERE table_name = ‘SALES_MV’;

LOG

NO

-- Passing atomic_refresh will refresh the table with nologging if the materialized view is set to nologging
SQL> exec dbms_mview.refresh(‘sales_mv’, atomic_refresh=>false);

PL/SQL procedure successfully completed.

-- Capture redo size after refreshing materialized view
SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 157719208

2) Refresh materalized view which has NOLOGGING turned on without using atomic_refresh option which defaults to true

-- Capture redo size after refreshing materialized view

SQL> SELECT vs.name, vm.value
FROM v$mystat vm, v$statname vs
WHERE vm.statistic# = vs.statistic#
AND vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 157719208

SQL> exec dbms_mview.refresh(‘sales_mv’);

PL/SQL procedure successfully completed.

SQL> SQL> SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like ‘%redo size%’;

NAME VALUE
—————————————————————- ———-
redo size 278564284

Compare Size using atomic refresh set to false (NOLOGGING is used) – 9,387,684
Compare Size using atomic refresh set to true – 12,0845,076
As you see the amount of redo generated is less when table is NOLOGGING and atomic_refresh is used

Advertisements

redo log generation during unrecoverable/nologging

# Capture amount of redo logs generated when table is created in UNRECOVERABLE
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 597736

SQL> create table test_all unrecoverable as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 648936

CREATE TABLE with NOLOGGING option
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1193320

SQL> create table test_all nologging as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1244436

Amount of redo logs generated for CREATE TABLE AS
– without UNRECOVERABLE/NOLOGGING option = 478904
– UNRECOVERABLE option = 51200
– NOLOGGING option = 51116
The amount of redo size is considerable less when nologging/unrecoverable is used.
Note: The database has to be in ARCHIVELOG mode to see the difference, if the database is in NOARCHIVELOG mode you won't see the difference.

How to find objects that are performing unrecoverable/nologgging option?

Using the steps below one can find the object that is unrecoverable from UNRECOVERABLE_CHANGE# in the v$datafile, one would use this to find the object that is unrecoverable since the last backup.

In this scenario datafile# 5 had unrecoverable/nologging as when we query v$datafile it shows and UNRECOVERABLE_TIME was greater than when the last backup was done.

SQL> column UNRECOVERABLE_CHANGE# format 99999999999999

SQL> select file#, UNRECOVERABLE_CHANGE# from v$datafile where file# = 5;

FILE# UNRECOVERABLE_CHANGE#
———- ———————
5 37640948153

— find the archive log that contains the sequence#
SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 < next_change#;

SEQUENCE# NAME
———- ——————————————————————————–
2108 /u01/TESTDB/arch/arch_1_2108.arc

— This will create file in user_dump
SQL> alter system dump logfile '/u01/TESTDB/arch/arch_1_2108.arc' layer 19;

In 8i the object id is not included but in the 10g trace file the object id is included in the trace file
/u01/admin/TESTDB/udump $ grep DBA ora_168168_testdb.trc


CHANGE #1 INVLD AFN:5 DBA:0x01401e0a BLKS:0x0020 SCN:0x0008.c39349b9 SEQ: 1 OP:19.2

# to find the file# and block
SET SERVEROUTPUT ON
DECLARE
l_dba NUMBER := TO_NUMBER (‘01401e0a’,’XXXXXXXX’);
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘File : ‘||l_file);
DBMS_OUTPUT.PUT_LINE (‘Block : ‘||l_block);
END;
/

Output:
File : 5
Block : 7690

SQL> SELECT owner, segment_name FROM dba_extents where file_id = 5 and block_id = 7690;

OWNER SEGMENT_NAME
—————————— —————-
SCOTT TEST_TABLE