RE: how do you verify that create table ... nologging ... as sele

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 09 Jan 2002 20:02:06 -0800
Message-ID: <F001.003EBCBD.20020109193021@fatcity.com>

> -----Original Message-----
> From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]
> ...
>The 'redo writes' and 'redo blocks' statistics are racked up by LGWR ,
>so you shouldn't see your session doing much of them.
>
>A better measurement of your session's redo generation would be the
>'redo size' statistic.
>
>select name, value from v$sesstat ss, v$statname sn where
>ss.statistic# = sn.statistic# and sn.name = 'redo size' and ss.sid =
>userenv('sessionid');

Thank you Mr. Wilton. On your advice I looked at the value of the 'redo size' statistic from v$sesstat for my session. I still am puzzled by the result though:

table created WITH logging:
  redo size = 3590580 - 2591128 = 999452
table created WITHOUT logging:
  redo size = 4885844 - 3886432 = 999412

The numbers are essentially the same. Shouldn't they be substantially different? When I analyze the table (compute statistics) I see that the table created has num_rows = 67600 and avg_row_len = 169.

(results copied below - still Oracle 8.1.7.2.6 and Windows 2000 server)

SQL> select
  2     b.name, a.value
  3   from
  4      v$sesstat a, v$statname b, v$session c
  5   where
  6      a.statistic# = b.statistic#
  7      and b.name = 'redo size'
  8      and a.sid = c.sid
  9      and c.audsid = sys_context ('userenv', 'sessionid') ;

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

redo size               2591128

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

Table créée.

SQL> select
  2     b.name, a.value
  3   from
  4      v$sesstat a, v$statname b, v$session c
  5   where
  6      a.statistic# = b.statistic#
  7      and b.name = 'redo size'
  8      and a.sid = c.sid
  9      and c.audsid = sys_context ('userenv', 'sessionid') ;

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

redo size               3590580

SQL> drop table my_table ;

Table supprimée.

SQL> select
  2     b.name, a.value
  3   from
  4      v$sesstat a, v$statname b, v$session c
  5   where
  6      a.statistic# = b.statistic#
  7      and b.name = 'redo size'
  8      and a.sid = c.sid
  9      and c.audsid = sys_context ('userenv', 'sessionid') ;

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

redo size               3886432

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

Table créée.

SQL> select
  2     b.name, a.value
  3   from
  4      v$sesstat a, v$statname b, v$session c
  5   where
  6      a.statistic# = b.statistic#
  7      and b.name = 'redo size'
  8      and a.sid = c.sid
  9      and c.audsid = sys_context ('userenv', 'sessionid') ;

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

redo size               4885844

SQL> 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 - 22:02:06 CST