Re: how do you verify that create table ... nologging ... as select

Jacques,

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');

Better yet, make your stat measurements of your session from another session.

Here's a bunch of redo stats from my session after a moderate update:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         0
redo synch time                                                           0
redo entries                                                          23227
redo size                                                           6762624
redo buffer allocation retries                                            1
redo wastage                                                              0
redo writer latching time                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo write time                                                           0
redo log space requests                                                   1
redo log space wait time                                                  4
redo log switch interrupts                                                0
redo ordering marks                                                       0

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 9 Jan 2002, Jacques Kilchoer wrote:



> 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

>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).