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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
