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

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 10 Jan 2002 00:35:19 -0800
Message-ID: <F001.003EBE0A.20020109235019@fatcity.com>

Jacques,

What about using the following script to measure actual amount of redo in the log file:

---

---
--

• Script: log_file_usage.sql
• Purpose: to see how much of the current log file has been used
• For: 8.0 and higher
  --
  
• Copyright: (c) Ixora Pty Ltd
• Author: Steve Adams
  --
  
• 5-Apr-2001 B Reardon - added x$ cf x_$, commented out sqlplus_settings
• from a oracle-l mail by Glen Travis merged in code to show MB used of current file.

  ---

---

column used justify right

select
  le.leseq log_sequence#,
  substr(to_char(100 * cp.cpodr_bno / le.lesiz, '999.00'), 2) || '%' used ,   le.lesiz * le.lebsz /1024 /1024 logmbtotal,   cp.cpodr_bno * le.lebsz /1024 /1024 logmbinuse from
  sys.x$kcccp cp,
  sys.x$kccle le
where

  le.inst_id = userenv('Instance') and
  cp.inst_id = userenv('Instance') and
  le.leseq = cp.cpodr_seq

/

---

Then you could see if more or less redo is going into the redo log.

Maybe you could also try it in a tablespace set to nologging and compare the results?

Hope this helps,
Bruce Reardon

-----Original Message-----

Sent: Thursday, 10 January 2002 14:30

> -----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
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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). Received on Thu Jan 10 2002 - 02:35:19 CST