how do you verify that create table ... nologging ... as select r

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 09 Jan 2002 15:55:00 -0800
Message-ID: <F001.003EBADB.20020109153532@fatcity.com>

My apologies. Allow me to add the following information to my previous e-mail: Oracle 8.1.7.2.6
Windows 2000 server

I wanted to verify that my "create table ... nologging ... as select ..." really minimizes writing to the redo log. To verify that, I issue a select * from v$sysstat before and after two create statements, one with and one without nologging. I see the same number of redo writes in both cases! This is in a test database where I am the only session. What do I fail to understand? (N.B. The tablespace default is logging, and has locally managed extents.)

create table without nologging:
redo writes = 3534 - 3410 = 124
redo blocks = 93851 - 91058 = 2793

create table with nologging:
redo writes = 3725 - 3600 = 125
redo blocks = 99032 - 96238 = 2794

Results below. (p.s. yes, I know that the select part of the "create table as select" does a cartesian join, that's only my cheap-and-easy way of getting many rows in the created table.)

Any help is appreciated.

LQS> column name format a20
LQS> select
  2    name, value
  3  from
  4    v$sysstat
  5  where name in ('redo blocks written', 'redo writes') ;

NAME                      VALUE
-------------------- ----------

redo writes                3410
redo blocks written       91058

LQS> create table my_table
  2  tablespace data1
  3  as select a.* from dba_tables_copy a, dba_tables_copy b ;

Table créée.

LQS> select
  2    name, value
  3  from
  4    v$sysstat
  5  where name in ('redo blocks written', 'redo writes') ;

NAME                      VALUE
-------------------- ----------

redo writes                3534
redo blocks written       93851

LQS> drop table my_table ;

Table supprimée.

LQS> select
  2    name, value
  3  from
  4    v$sysstat
  5  where name in ('redo blocks written', 'redo writes') ;

NAME                      VALUE
-------------------- ----------

redo writes                3600
redo blocks written       96238

LQS> create table my_table nologging
  2  tablespace data1
  3  as select a.* from dba_tables_copy a, dba_tables_copy b ;

Table créée.

LQS> select
  2    name, value
  3  from
  4    v$sysstat
  5  where name in ('redo blocks written', 'redo writes') ;

NAME                      VALUE
-------------------- ----------

redo writes                3725
redo blocks written       99032

LQS> select * from dba_tablespaces where tablespace_name = 'DATA1' ;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

------------------------------ -------------- ----------- -----------

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   EXTENT_MAN ----------- ------------ ---------- --------- --------- --------- ---------- ALLOCATIO PLU
--------- ---

DATA1                                   14336       14336           1

 2147483645            0      14336 ONLINE    PERMANENT LOGGING   LOCAL UNIFORM   NO Received on Wed Jan 09 2002 - 17:55:00 CST