When NOLOGGING will not be in effect [message #630415] |
Fri, 26 December 2014 13:30 |
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 #630434 is a reply to message #630425] |
Sat, 27 December 2014 09:48 |
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 #630437 is a reply to message #630436] |
Sat, 27 December 2014 10:06 |
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 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
manubatham20 wrote on Sat, 27 December 2014 10:55Ok, 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 #630440 is a reply to message #630437] |
Sat, 27 December 2014 10:20 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
manubatham20 wrote on Sat, 27 December 2014 11:06SY,
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.
|
|
|