Home » SQL & PL/SQL » SQL & PL/SQL » When NOLOGGING will not be in effect (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
When NOLOGGING will not be in effect [message #630415] Fri, 26 December 2014 13:30 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

I am creating a table with

CREATE TABLE <TABLE_NAME>
NOLOGGING
AS
 SELECT * FROM <VERY_BIG_TABLE> WHERE <CONDITION>=<VALUE>;


I was just wondering, will there be any case when NOLOGGING option will not be in effect. Can you please also suggest what parameters I have to check to determine if table created with NOLOGGING, will be created with NOLOGGING or this option will be ignored.

Thanks,
Manu
Re: When NOLOGGING will not be in effect [message #630417 is a reply to message #630415] Fri, 26 December 2014 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unwilling or incapable to simply SEARCH for answer?

http://www.orafaq.com/wiki/Nologging_and_force_logging
Re: When NOLOGGING will not be in effect [message #630418 is a reply to message #630417] Fri, 26 December 2014 17:02 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

So my question is - if FORCE_LOGGING set to YES, then CTAS NOLOGGING option will be ignored or it will be in effect?

SQL> SELECT force_logging FROM v$database;
FOR
---
YES


Thanks,
Manu
Re: When NOLOGGING will not be in effect [message #630419 is a reply to message #630418] Fri, 26 December 2014 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So my question is - if FORCE_LOGGING set to YES, then CTAS NOLOGGING option will be ignored or it will be in effect?

Yes, no, Maybe, It depend.
Is the answer the same for every Oracle version on every OS?

Answer impacts what or how database is recovered should that be necessary.

There Ain't No Such Thing As A Free Lunch.

what do YOU see when YOU actually test it for yourself?
Re: When NOLOGGING will not be in effect [message #630425 is a reply to message #630415] Sat, 27 December 2014 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that CTAS is ALWAYS done in no logging mode and that the NOLOGGING option only set the LOGGING table property to NO for subsequent operations (which can be overwritten by a tablespace or database FORCE_LOGGING option, as BlackSwan pointed you to).

Re: When NOLOGGING will not be in effect [message #630431 is a reply to message #630425] Sat, 27 December 2014 08:39 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I was looking for that gist.

CTAS is ALWAYS done in no logging mode and that the NOLOGGING option only set the LOGGING table property to NO for subsequent operations.


I got it, so at the time of table creation NOLOGGING is not effective, it's only effective for subsequent DML operations.

Thanks Michel.
Re: When NOLOGGING will not be in effect [message #630434 is a reply to message #630425] Sat, 27 December 2014 09:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Sat, 27 December 2014 03:02

Note that CTAS is ALWAYS done in no logging mode and that the NOLOGGING option only set the LOGGING table property to NO for subsequent operations (which can be overwritten by a tablespace or database FORCE_LOGGING option, as BlackSwan pointed you to).




?????

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

     VALUE
----------
  48942740

SQL> create table tbl1
  2    as
  3      select  *
  4        from  dba_objects
  5  /

Table created.

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

     VALUE
----------
  65177460

SQL> create table tbl2
  2    NOLOGGING
  3    as
  4      select  *
  5        from  dba_objects
  6  /

Table created.

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

     VALUE
----------
  65601612

SQL> select  65177460 - 48942740 "tbl1 redo",
  2          65601612 - 65177460 "tbl2 redo"
  3    from  dual
  4  /

 tbl1 redo  tbl2 redo
---------- ----------
  16234720     424152

SQL>  


SY.
Re: When NOLOGGING will not be in effect [message #630435 is a reply to message #630434] Sat, 27 December 2014 09:55 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Ok, now this is again confusing me. Show me your value of FORCE_LOGGING parameter as well.

And is it session specific, or other sessions redo also counted?

What options affect redo log generation, show me all the parameters, so I will check against my database.

Manu
Re: When NOLOGGING will not be in effect [message #630436 is a reply to message #630435] Sat, 27 December 2014 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I will check against my database.

You are NOT done any testing so far, so why start now?
Re: When NOLOGGING will not be in effect [message #630437 is a reply to message #630436] Sat, 27 December 2014 10:06 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

SY,

For me, it's almost equal.

sqlplus username/password@proddb

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 27 10:01:58 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT VALUE
  FROM v$sesstat a, v$statname b
 WHERE     a.statistic# = b.statistic#
       AND b.name = 'redo size'
       AND a.sid = SYS_CONTEXT ('userenv', 'sid');

     VALUE
----------
         0

SQL> CREATE TABLE tbl1
AS
   SELECT * FROM dba_objects;

Table created.

SQL> SELECT VALUE
  FROM v$sesstat a, v$statname b
 WHERE     a.statistic# = b.statistic#
       AND b.name = 'redo size'
       AND a.sid = SYS_CONTEXT ('userenv', 'sid');

     VALUE
----------
  14866160

SQL> CREATE TABLE tbl2
NOLOGGING
AS
   SELECT * FROM dba_objects;

Table created.

SQL> SELECT VALUE
  FROM v$sesstat a, v$statname b
 WHERE     a.statistic# = b.statistic#
       AND b.name = 'redo size'
       AND a.sid = SYS_CONTEXT ('userenv', 'sid');

     VALUE
----------
  29728832

SQL> select  29728832 - 14866160 from dual;

29728832-14866160
-----------------
         14862672


What parameters to check that can affect redo generation?

EDIT : Thanks for the nice trick to quickly check REDO generated.

Thanks,
Manu

[Updated on: Sat, 27 December 2014 10:06]

Report message to a moderator

Re: When NOLOGGING will not be in effect [message #630438 is a reply to message #630435] Sat, 27 December 2014 10:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
manubatham20 wrote on Sat, 27 December 2014 10:55
Ok, now this is again confusing me. Show me your value of FORCE_LOGGING parameter as well.


My answer was to "CTAS is ALWAYS done in no logging mode". Since you already have FORCE_LOGGING = YES, run same test and post your findings.

SY.
Re: When NOLOGGING will not be in effect [message #630439 is a reply to message #630438] Sat, 27 December 2014 10:11 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I already posted it, there's not much difference in redo generation whether I am using NOLOGGING or not.
Re: When NOLOGGING will not be in effect [message #630440 is a reply to message #630437] Sat, 27 December 2014 10:20 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
manubatham20 wrote on Sat, 27 December 2014 11:06
SY,

For me, it's almost equal.

What parameters to check that can affect redo generation?


As usual RTFM:

Specifying FORCE LOGGING Mode

Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING setting can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect media recovery and standby databases.

Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for objects.


SY.
Previous Topic: ORA-01722: invalid number
Next Topic: How to display a record from an array(nested table)
Goto Forum:
  


Current Time: Fri Apr 26 00:37:17 CDT 2024