Skip to Main Content
  • Questions
  • Nologging does not work (yes it does...)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yong.

Asked: August 02, 2000 - 6:45 pm UTC

Last updated: February 13, 2012 - 12:50 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

This is reported by Surya Rao <surya.rao@reuters.com> in a mailing list and I reproduce it here:

SQL> create table test (a number) nologging;

Table created.

SQL> select table_name,logging
2 FROM USER_TABLES
3 where table_name = 'TEST';

TABLE_NAME LOG
------------------------------ ---
TEST NO

SQL> select sid from v$mystat where rownum = 1;

SID
---------
22

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name = 'redo size' and sid = 22;

NAME VALUE
--------------------------------------------- ---------
redo size 25852

SQL> insert into test values (111);

1 row created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name = 'redo size' and sid = 22;

NAME VALUE
--------------------------------------------- ---------
redo size 26520

SQL> commit;

Commit complete.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name = 'redo size' and sid = 22;

NAME VALUE
--------------------------------------------- ---------
redo size 26600

SQL> truncate table test;

Table truncated.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name = 'redo size' and sid = 22;

NAME VALUE
---------------------------------------------- ---------
redo size 29900

Why does redo size keep increasing? Shouldn't it be fixed for this nologging table? Thank you.

Yong Huang

and we said...

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:477221446020
and
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:366018048216 <code>
Nologging only affects very very specific operations. For exmaple, from the alter index syntax statement in the SQL reference:

<quote>
LOGGING|NOLOGGING

LOGGING|NOLOGGING specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned index, a range or hash index partition, or all partitions or subpartitions of a composite-partitioned index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.

In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose this index, you must take a backup after the operation in NOLOGGING mode.

If the database is run in ARCHIVELOG mode, media recovery from a backup taken before an operation in LOGGING mode will re-create the index. However, media recovery from a backup taken before an operation in NOLOGGING mode will not re-create the index.
An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.
</quote>

That also explains why the truncate above generated redo -- The statement "minimal logging (to mark new extents invalid and to record dictionary changes)." explains where that redo comes from. The blocks that were truncated were not logged HOWEVER the changes to the data dictionary itself were.

NOLOGGING works --



Rating

  (37 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What is the actual for CTAS using NOLOGGING

Nag, November 02, 2001 - 12:53 pm UTC

wHEN THERE IS NO WHERE CLAUSE  the following works with NOLOGGING  given at the end
SQL> create table myemp 
  2  as select * from  emp
  3  nologging ;

Table created.

SQL> drop table myemp;

Table dropped.


Where you give a where clause , it does not work if nologging is given at the end.
SQL> create table myemp
  2  as select empno,dname
  3  from emp,dept
  4  where emp.deptno=dept.deptno
  5  nologging   ;
nologging
*
ERROR at line 5:
ORA-00933: SQL command not properly ended

It works when nologging is given after the table name(when a where clause is given in CTAS)
SQL> create table myemp nologging
  2  as select empno,dname
  3  from emp,dept
  4  where emp.deptno=dept.deptno
  5  
SQL> /

Where is this difference of syntax documented in oracle documentation.

Why this change in syntax. It is confusing.

Table created.

(Tom, Yes, I do ask a lot of question , but believe me most of my questions arise out of genuine practical situations, which every developer with startup to intermediate experience every day, AND
I dont have words for the service you are rendering to us, I have already mentioned to you that I see a lot of difference in the way I approach to bugs  and other technicaly difficult situations , the whole credit goes to to you) 

Tom Kyte
November 02, 2001 - 8:37 pm UTC

This is the documented syntax.

The high level diagram for create table is something like:

create table <schema>.table
< ( relational properties ) >
< physical properties >
< table_properties >


Now, if you look at the wire diagram in the sql ref manual for physical properties, you'll find it includes:

o segment attributes clause
o organization
o cluster
o lob storage
and so on.

part of segment attributes clause is NOLOGGIN.

The table properties wire diagram shows things like:

o range partition
o hash
o composite
o cache/nocache
o monitor/no monitor
o parallel
o AS SUBQUERY

so the as subquery is DEAD LAST in a create table statement (if present at all)

So, this is documented in the SQL ref manual under CREATE TABLE.

It is not a change in syntax -- it is just the AS SUBQUERY is DEAD LAST in the CTAS

(btw: my comment about your "asking lots of questions" wasn't to get your dander up, it was to make clear that i fully expect you to have my book given the number of Q's you do ask (which is statistically higher then the rest of the population out there, regardless of your motive). I was really surprise when you asked me about bind variables since my book is literally rife with examples)

Reader

Reader, November 03, 2001 - 2:10 am UTC

Tom,
"
In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes).
"
Once backup is taken, does Oracle re-visit the extents
and validate them. Could you tell us what process takes place.

Also, does Oracle determine that backup is being taken by
looking at the "Hot Backup end marker scn: " in control
file and "Backup taken at scn: " from the datafile.

Also, since the methodolgy changed by the advent of RMAN, does "Hot Backup end marker scn: " still relevent

Thanks

Tom Kyte
November 03, 2001 - 8:34 am UTC

No, the extents are not revisited. In fact the extent invalidation is in the redo not the table.

You see, if you do a non logged operation on a table that has data on "disk1" and then a failure occurrs on disk 1 the following will happen:

o you'll restore disk1 from a backup
o you'll apply the redo logs to this.
o you'll need to apply the redo log with the invalidation entries (since it happened AFTER the backup)
o it'll invalidate them, marking them "corrupt"

On the other hand if you do a non-logged operation on that table, BACKUP that datafile right after and then a failure occurs the following will happen:

o you'll restore disk1 from that backup
o you'll apply redo logs, this time the redo log containing the invalidation records is NOT applied -- it happened BEFORE the backup
o you are a-ok

I don't understand the "Also, does Oracle determine that backup is being taken..." part. If you are taking a backup the old fashioned way, you have issued an alter tablespace begin backup (so it knows). If you are using RMAN, RMAN knows and the database doesn't really care (no "backup" mode for tablespaces with RMAN, not necessary. RMAN does the book keeping its needs to know what redo it need apply to datafiles upon recovery.

Reader

Reader, November 03, 2001 - 1:54 pm UTC

Tom,

You have answered my question comprehensively.

The "Also, does Oracle determine that backup is being
taken..." part is:

I was referring to the
"Hot Backup end marker scn: 0x0000.00000000"
in symbolic control file dump
and
"Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0"
in symbolic file_hdr dump

I was not sure these are still relevent with the advent
of RMAN

Thanks





wire diagram

A reader, November 05, 2001 - 8:44 pm UTC

< ( relational properties ) >
< physical properties >
< table_properties >

Tom, can you describe the above 3 kinds of properties.

Is the above order the same for all the oracle objects. Or do some of the objects have other kind of properties.

What do you mean by wire diagram.

By the way this is good info( especially for attacking OCP questions).



Tom Kyte
November 05, 2001 - 8:54 pm UTC

By wire diagram I meant what you see at:

</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76989/ch4g9.htm#9623 <code>

and that has the description of the three things you ask of.

How to avoid redo generation for index tables

SK, January 14, 2003 - 11:07 am UTC

SQL> create table t nologging as select * from all_objects where 1=0;

Table created.

SQL> set autotrace on statistics;
SQL> 
SQL> insert /*+ append */ into t select * from all_objects;

7719 rows created.


Statistics
----------------------------------------------------------
        315  recursive calls
        251  db block gets
      28048  consistent gets
          0  physical reads
      18700  redo size
        775  bytes sent via SQL*Net to client
        701  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       7719  rows processed

SQL> truncate table t;

Table truncated.

SQL> create index object_name_idx on t(object_name);

Index created.

SQL> insert /*+ append */ into t select * from all_objects;

7720 rows created.


Statistics
----------------------------------------------------------
        291  recursive calls
        720  db block gets
      28085  consistent gets
          2  physical reads
     892808  redo size
        776  bytes sent via SQL*Net to client
        701  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       7720  rows processed

SQL> truncate table t;

Table truncated.

SQL> alter index object_name_idx nologging;

Index altered.

SQL> insert /*+ append */ into t select * from all_objects;

7720 rows created.


Statistics
----------------------------------------------------------
        506  recursive calls
        815  db block gets
      28141  consistent gets
          5  physical reads
     926960  redo size
        777  bytes sent via SQL*Net to client
        701  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       7720  rows processed

SQL> truncate table t;

Table truncated.
SQL> alter index object_name_idx disable;
alter index object_name_idx disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option


SQL> alter index object_name_idx unusable;

Index altered.

SQL> truncate table t;

Table truncated.

SQL> insert /*+ append */ into t select * from all_objects;

7720 rows created.


Statistics
----------------------------------------------------------
        435  recursive calls
        932  db block gets
      28196  consistent gets
          5  physical reads
    1097652  redo size
        779  bytes sent via SQL*Net to client
        701  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       7720  rows processed

Q1: Why did alter index ... disable failed? Oracle 9.2 document still gives this a valid command.

Q2: Why did the redo generated when index was in unusable state?

How to avoid redo regeration for a large table direct load insert. DB in ARCHIVELOG mode and table and indexes are altered to nologging mode before large inserts.

Thanks in advance. 

Tom Kyte
January 14, 2003 - 12:39 pm UTC

q1) 

ENABLE Clause ENABLE <b>applies only to a function-based index</b> that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true: n The function is currently valid n The signature of the current function matches the signature of the function when the index was created n The function is currently marked as DETERMINISTIC Restriction on the ENABLE clause: You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE. DISABLE Clause 

DISABLE <b>applies only to a function-based index.</b> This clause enables you to disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

q2) trick question - it is because the index was nolonger in the unusable state!  the truncate "fixed it"


ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> create index t_idx on t(x);

Index created.

ops$tkyte@ORA920> alter index t_idx unusable;

Index altered.

ops$tkyte@ORA920> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX                          UNUSABLE

ops$tkyte@ORA920> truncate table t;

Table truncated.

ops$tkyte@ORA920> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX                          VALID



alter the index AFTER the truncate and then alter session set skip_unusable_indexes=true;



Indexes must be logged during inserts.  the nologging controls their behaviour during THEIR bulk operations, like a rebuild or build.

so, truncate, unusable, load, rebuild  is what you are looking for 

Can the samething be done with PK

SK, January 14, 2003 - 4:17 pm UTC

Thanks for the useful information.

Can the redo generation be avoided for Primary keys while doing mass inserts.

i.e.

Disable PK, alter index unusable, insert /*+ append */, rebuild index nologging, enable PK.

In the above I am presuming that a PK has been created using an existing index.

Tom Kyte
January 14, 2003 - 8:26 pm UTC

disabling the primary key that has a unique index backing it (doesn't have to be unique) will drop the index - so, you might not have to do the unusable part -- but yes, it'll avoid the redo.

Corruption due to NOLOGGING or UNRECOVERABLE

Igor, July 18, 2003 - 2:31 pm UTC

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an
object and the datafile containing that object is subsequently
recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed.

Why when I perform a NOLOGGING operation to build and index, I get a corruption in the table datafile not in the index datafile itself?

Tom Kyte
July 19, 2003 - 11:21 am UTC

you won't -- perhaps you did the table nologging as well.

I would need a step by step example, like I provide you guys to see what you mean.

Redo while DELETING

Prasad, March 09, 2004 - 5:50 am UTC

Hi Tom,

We are doing an archive operation where we are selecting and then deleting a few million records.we are commiting after every 0.1 million rows (I know you will dislike this :o)
Now this delete process generates a huge redo. We are in archive log mode on Oracle 8.1.7.4 (HP 11).
Last friday according to Production DBAs, this archiving process spawaned 1000s of archive logs and the DB was crashed.
so i need your suggestions on this please.

An early responce (as usual) will be highly appreciated.

Thanks,
Prasad

Tom Kyte
March 09, 2004 - 12:15 pm UTC

it would not have crashed the database.

perhaps your dba's failed to provide sufficient space for the archives (and the database, in a selfless act of self preservation -- stopped doing work -- until the dba's freed sufficient space).

perhaps your dba's did not get the archives off of the system fast enough

but I doubt the system crashed due to high archive generation

you might consider partitioning (and just truncating a partion to purge data)
or </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:890462882545 <code>use a CTAS to copy the rows to keep and just drop the old table

those are all "no redo generated if you like" options.


Prasad, March 09, 2004 - 12:33 pm UTC

hi Tom,

As usual you are very right.The DB didn't crash, it sort of hanged.
I'm doubtfull about the CTAS option as monthly i delete about 30m rows & that table has around 500m rows.so with CTAS i'll have to create a table with 470m data. so again that will be time consuming, won't it?

But we've decided to partition these tables in near future.
So shudn't be a problem after that.

But plese give your valuable opinion about CTAS in my case.

Many thanks again.
Prasad

Tom Kyte
March 09, 2004 - 3:35 pm UTC

depends -- it could be less time to create 470m rows without logging/undo/indexes then to delete 30million indexed rows.

benchmark it on a couple million rows, see what you see.

Nologging - dbv

AR, November 13, 2004 - 9:53 pm UTC

Tom,
Say I perform a bunch of NOLOGGING dml operations on a database that is being backed up at the same time (RMAN hot backup). Then say I perform a RESTORE using this backup to another system. NOTE : I only RESTORED it, I have NOT performed RECOVERY on it yet.

Should I expect to see corruption if I issue a dbv on the files on which NOLOGGING operations had been performed? Obviously, I will see dbv corruption if I performed "recovery" on nologging tables..but I'm more concerned about dbv errors after a mere "restore".

Thankyou for your time.

Tom Kyte
November 14, 2004 - 10:07 am UTC

a mere restore of "inconsistent files" you mean. what is 'mere restore'?

The blocks would probably look clean immediately after the restore (before any application of redo) but would be marked corrupt by the recovery process (as there is no redo to apply to them)

I'm not concerned at all about dbv here, I'm concerned that you seem to have scheduled the backup to correspond with non-logged operations which would not be a good idea. you want the backup immediately after the non-logged operations, not before, not during.



Nologging - dbv

AR, November 14, 2004 - 11:37 am UTC

Thank you for your response.

> a mere restore of "inconsistent files" you mean.

Correct. Thats what I meant.

> The blocks would probably look clean immediately after > the restore (before any application of redo) but would be > marked corrupt by the recovery process (as there is no > redo to apply to them)

Hmm..that was my understanding as well. I thought/guessed as much. But I was talking to a DBA that had to restore her warehouse database after a failure. She says she sees dbv corruption errors immediately after the restore even (of inconsistent files). I find that odd. She has opened a TAR and support says, that is expected behavior! I don't have the TAR number.

Tom Kyte
November 14, 2004 - 11:44 am UTC

I said probably -- the nologging stuff is all direct path operations -- anything goes. I would not rely on dbv saying anything accurate about the blocks in the midst of this non-logged operation.

It is a concern that you would be backing up files that are in the midst of direct path operations -- not only are you making the direct path operations "go slow" due to fighting with backups trying to read the same stuff -- but you are just plain "wasting time" as you need to backup again right after you finish.

nologging clause on indexes

A reader, November 17, 2004 - 7:48 am UTC

hi tom,

you said that the nologging clause on indexes does only work for operations on the index itself: "the nologging controls their behaviour during THEIR bulk operations, like a rebuild or build".

but the oracle 9.2 documentation says:
"Use the logging_clause to specify whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against a nonpartitioned index, a range or hash index partition, or all partitions or subpartitions of a composite-partitioned index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file."

???


Tom Kyte
November 17, 2004 - 11:00 am UTC

the documentation is misleading -- the indexes are logged during the "merge" phase.  this can easily be observed (we'll do the nonpartitioned index)

ops$tkyte@ORA9IR2> create table t as select * from all_objects where 1=0;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t nologging;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on statistics;
ops$tkyte@ORA9IR2> insert /*+ append */ into t select * from all_objects;
 
30912 rows created.
 
 
Statistics
----------------------------------------------------------
        591  recursive calls
        235  db block gets
     173113  consistent gets
          0  physical reads<b>
      26056  redo size</b>
        767  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      30912  rows processed
 
ops$tkyte@ORA9IR2> truncate table t;
 
Table truncated.
 
ops$tkyte@ORA9IR2> create index t_idx on t(object_name,owner,object_type,object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> alter index t_idx nologging;
 
Index altered.
 
ops$tkyte@ORA9IR2> insert /*+ append */ into t select * from all_objects;
 
30913 rows created.
 
 
Statistics
----------------------------------------------------------
        967  recursive calls
       4666  db block gets
     173682  consistent gets
          0  physical reads<b>
    7146824  redo size</b>
        768  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      30913  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off
 

A reader, April 13, 2006 - 4:56 pm UTC


Nologging at table level when database is in NO ARCHIVE MODE

Reader, April 18, 2006 - 5:20 am UTC

hi tom, thanks for u r help!

1)i just want to check with you, does nologging at table or index level help when the database is in noarchive mode.

2)alter databse no force logging! can u plz explain what does this command do for a database in Noarchive mode!

Thanks

Tom Kyte
April 18, 2006 - 9:42 am UTC

"u r" ?? what is that?

this "u" dude isn't around, so "u" cannot discuss german postal codes (I assume that is what you mean by PLZ since I have never been able to find any other definition for it as a word?)



Nologging at table level when database is in NO ARCHIVE MODE

Reader, April 18, 2006 - 10:33 am UTC

Hi Tom, my apologies for that " u r" language!

Can you please help me on that query!

Thanks
Reader

Tom Kyte
April 18, 2006 - 2:15 pm UTC

1) depends on the operation.

ops$tkyte@ORA10GR2> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

ops$tkyte@ORA10GR2> @mystat "redo size"
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE
-------------------- ----------
redo size              24564516

ops$tkyte@ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA10GR2> @mystat2
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              24662764           98,248

ops$tkyte@ORA10GR2> create index t_idx on t(object_name,object_type,owner);

Index created.

ops$tkyte@ORA10GR2> @mystat2
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              27625000        2,962,236

ops$tkyte@ORA10GR2> alter index t_idx nologging;

Index altered.

ops$tkyte@ORA10GR2> @mystat2
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              27627400            2,400

ops$tkyte@ORA10GR2> alter index t_idx rebuild;

Index altered.

ops$tkyte@ORA10GR2> @mystat2
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              27689284           61,884

ops$tkyte@ORA10GR2> alter index t_idx rebuild logging;

Index altered.

ops$tkyte@ORA10GR2> @mystat2
ops$tkyte@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              30634600        2,945,316

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>

create table as select - skips redo, but the index operations did not unless and until we used nologging.

2) alter database force logging - ignore NOLOGGING option, generate redo (if we did the above example with force logging you would see:

sys@ORA10GR2> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

sys@ORA10GR2> @mystat "redo size"
sys@ORA10GR2> set echo off

NAME                      VALUE
-------------------- ----------
redo size                 24888

sys@ORA10GR2> create table t as select * from all_objects;

Table created.

sys@ORA10GR2> @mystat2
sys@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               5805448        5,780,560

sys@ORA10GR2> create index t_idx on t(object_name,object_type,owner);

Index created.

sys@ORA10GR2> @mystat2
sys@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               8730540        2,925,092

sys@ORA10GR2> alter index t_idx nologging;

Index altered.

sys@ORA10GR2> @mystat2
sys@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               8732916            2,376

sys@ORA10GR2> alter index t_idx rebuild;

Index altered.

sys@ORA10GR2> @mystat2
sys@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              11664808        2,931,892

sys@ORA10GR2> alter index t_idx rebuild logging;

Index altered.

sys@ORA10GR2> @mystat2
sys@ORA10GR2> set echo off

NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size              14595952        2,931,144

everything logs)

alter database no force logging and re-run test in noarchivelog mode and you are back to the original set of numbers.... 

Meaning, it has the same effect - it will either force OR NOT force logging of any operation that could skip logging. 

Nologging at table level when database is in NO ARCHIVE MODE

Reader, April 19, 2006 - 8:20 am UTC

Hi Tom, 
Thank you so much for the clarification, can you please explain why nologging doesn't work with alter table .....add constraint primary key....!



SQL> create table test23 (sno number , bno number);

Table created.

SQL> select logging from user_tables where table_name='TEST23';

LOG
---
YES

SQL> create index ind_test23 on test23(sno) parallel 5 nologging;

Index created.

SQL> select logging from user_indexes where index_name='IND_TEST23';

LOG
---
NO

SQL> DROP INDEX IND_TEST23;

Index dropped.

SQL> ALTER TABLE TEST23 ADD CONSTRAINT PK_TEST23 PRIMARY KEY(SNO) PARALLEL NOLOGGING;

Table altered.

SQL> select logging from user_indexes where index_name='PK_TEST23';

LOG
---
YES

Thank you so much 

Tom Kyte
April 19, 2006 - 9:14 am UTC

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
 
NAME                      VALUE
-------------------- ----------
redo size               7819108
 
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(object_id,object_name,object_type,owner);
 
Table altered.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               9656716        1,837,608
 
ops$tkyte@ORA9IR2> alter table t drop constraint t_pk;
 
Table altered.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               9677000           20,284
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(object_id,object_name,object_type,owner)
  2  using index (create index t_pk on t(object_id,object_name,object_type,owner) NOLOGGING );
 
Table altered.
 
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
 
NAME                      VALUE DIFF
-------------------- ---------- ------------------
redo size               9733928           56,928
 

Reader, April 19, 2006 - 10:42 am UTC

This is wonderful Tom, but the problem with explicitly named indexes is
IT REQUIRES INDEX TO BE DROPED EXPLICITLY WHEN PRIMARY KEY IS DROP!

 
 1  alter table t add constraint tt_pk primary key(object_id,object_name,object_type)
  2  using index (create index Tt_pk on
  3 t(object_id,object_name,object_type ) NOLOGGING )
SQL> /

Table altered.

SQL> select table_name,index_name,logging from user_indexes where table_name='T';

TABLE_NAME                     INDEX_NAME                     LOG
------------------------------ ------------------------------ ---
T                              TT_PK                          NO


SQL>  ALTER TABLE T DROP PRIMARY KEY;

Table altered.

SQL>  select table_name,index_name,logging from user_indexes where table_name='T';

TABLE_NAME                     INDEX_NAME                     LOG
------------------------------ ------------------------------ ---
T                              TT_PK                          NO

Index for primary key still exists in user_indexes;

All these  below do keep indexes in user_indexes even primary key is droped.

ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT TT_PK;

Any work around for using explicity named indexes and also
making oracle drop primary key index in user_indexes if primary key constraint is dropped.

Thanks you so much

 

Tom Kyte
April 19, 2006 - 11:06 am UTC

"so" he says out loud?

workaround: drop index if you don't want index?

Alberto Dell'Era, April 19, 2006 - 11:25 am UTC

Another workaround - create a UNIQUE index:

SQL> alter table t add constraint t_pk primary key(object_id,object_name,object_type,owner)
  2   using index (create UNIQUE index t_pk on t(object_id,object_name,object_type,owner) NOLOGGING );

Table altered.

SQL> ALTER TABLE T DROP PRIMARY KEY;

Table altered.

SQL> select table_name,index_name,logging from user_indexes where table_name='T';

no rows selected

I believe that this "workaround" is to be found somewhere on asktom - at least the knowledge that pk-supporting indexes are dropped when the PK is dropped, if they're UNIQUE.
So I don't claim to be the Inventor :) 

Tom Kyte
April 19, 2006 - 12:59 pm UTC

indeed, yes... the unique indexes go (unless of course you use keep index ;)

Reader, April 20, 2006 - 7:32 am UTC

Thanks you so much

Overhead of using view instead of table

Anto, April 27, 2006 - 5:39 pm UTC

Hi Tom

SQL > set autotrace on

 1  insert into load_link (trade_id, host_trade_date, host_sys_code,
  2    host_trade_id)
  3  values
  4*  (100, to_date('23-Mar-06','dd-Mon-YY'), 'TE', 11)
  5  /

1 row created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=49)




Statistics
----------------------------------------------------------
         17  recursive calls
         18  db block gets
         21  consistent gets
          0  physical reads
      12548  redo size     <<<<< large redo size 
        357  bytes sent via SQL*Net to client
        418  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Instead of using the view  load_link - if we directly insert into the table load_link_base


  1  insert into cmis_trade.load_link_base (trade_id, host_trade_date, host_sys_code,
  2    host_trade_id)
  3  values
  4*  (100, to_date('23-Mar-06','dd-Mon-YY'), 'TE', 11)
SQL> /

1 row created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=49)




Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
        396  redo size    <<< very small value
        371  bytes sent via SQL*Net to client
        434  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Also smaller values for recursive calls,db block gets,consistent gets, redo size etc if we directly use the table instead of the view - I tried that multiple times(to nullify the effect of caching etc) but always the same considerable difference in autotrace stats between table and view

What could be the reason for such differnce in numbers - when using the view instead of the base table ? Is there any parameter or workaround available for reducing these stats when continuing to use the view instead of the base table ? There are thousands of similar statements which get executed every min - hence it would make a considerable differnce if we can get these autotrace stats numbers down for the view

Load_link is a simple view  based on load_link_base table and there is no index on this   load_link_base  table

Oracle version 9204

thanks
Anto
 

Tom Kyte
April 28, 2006 - 1:52 am UTC

12k and "large" don't seem to go together do they?


Look at your recursive calls, looks like insert #1 had to load the library cache, whereas insert #2 just got to make use of the work performed by one.

I do not see what you see:

ops$tkyte@ORA9IR2> create table t ( x int, y date, z varchar2(30) );

Table created.

ops$tkyte@ORA9IR2> create or replace view vw as select * from t;

View created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, sysdate, user );

1 row created.

ops$tkyte@ORA9IR2> insert into vw values ( 1, sysdate, user );

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
ops$tkyte@ORA9IR2> insert into t values ( 1, sysdate, user );

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
        256  redo size
        788  bytes sent via SQL*Net to client
        814  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA9IR2> insert into vw values ( 1, sysdate, user );

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
        256  redo size
        788  bytes sent via SQL*Net to client
        815  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA9IR2> set autotrace off
 

A reader, April 28, 2006 - 12:21 pm UTC

It should have been "larger redo size" not "large redo size"

Thanks for the pointer

I will trace while using the view and see whether I can find something

Anto

Overhead of view

A reader, April 28, 2006 - 12:30 pm UTC

Hi,

You are right. There are triggers defined on the view but not on the table which were causing the numbers to go up for the view

thanks
Anto

@mystat2

Reader, May 04, 2006 - 11:25 am UTC

Hi Tom,

Can you please list down the @mystat2 script!

Thanks in advance

Tom Kyte
May 04, 2006 - 4:51 pm UTC

---------- mystat.sql ---------------
set echo off
set verify off
column value new_val V
define S="&1"
column name format a20

set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on


----------- mystat2.sql ------------------

set echo off
set verify off
column diff format a18
select a.name, b.value Value, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on


logging is generating more redo than nologging

Thierry Yen Suin, May 16, 2006 - 3:28 am UTC

Hi Tom,
I am testing nologging and found that I am having more redo that without than logging.
I traced them with sql_trace and found that after 'alter table nologging' I am getting more recursive sql in the trace.

Can you please explain?
Thank you,
Thierry Yen Suin.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> insert /*+ APPEND */ into big_table2 select * from all_objects;

28429 rows created.


Statistics
----------------------------------------------------------
         70  recursive calls
        107  db block gets
     150274  consistent gets
          0  physical reads
       7964  redo size                <------------ ?
        606  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      28429  rows processed

SQL> commit;

Commit complete.

SQL> alter table big_table2 nologging;

Table altered.

SQL> insert /*+ APPEND */ into big_table2 select * from all_objects;

28429 rows created.


Statistics
----------------------------------------------------------
        276  recursive calls
        110  db block gets
     150319  consistent gets
          0  physical reads
       8080  redo size              <--------------- ?
        606  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      28429  rows processed


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0         12          3           3
Fetch        7      0.00       0.00          0         13          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0         25          3           7

Misses in library cache during parse: 0

    4  user  SQL statements in session.
    7  internal SQL statements in session.
   11  SQL statements in session.
********************************************************************************
Trace file: append.trc
Trace file compatibility: 9.02.00
Sort options: default

       1  session in tracefile.
       4  user  SQL statements in trace file.
       7  internal SQL statements in trace file.
      11  SQL statements in trace file.
       7  unique SQL statements in trace file.
     127  lines in trace file.
      18  elapsed seconds in trace file.

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       16      0.00       0.00          0          0          0           0
Execute     20      0.01       0.00          0         12          3           3
Fetch       43      0.00       0.00          0         56          0          28
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       79      0.01       0.00          0         68          3          31

Misses in library cache during parse: 0

    4  user  SQL statements in session.
   16  internal SQL statements in session.
   20  SQL statements in session.
********************************************************************************
Trace file: nologging.trc
Trace file compatibility: 9.02.00
Sort options: default

       1  session in tracefile.
       4  user  SQL statements in trace file.
      16  internal SQL statements in trace file.
      20  SQL statements in trace file.
      16  unique SQL statements in trace file.
     230  lines in trace file.
      14  elapsed seconds in trace file.


 

Tom Kyte
May 16, 2006 - 7:05 am UTC

come on. look at the bytes here, this isn't worth any time on our part. Be 'reasonable'

nologging has no use here in a noarchivelog mode database with append. It doesn't do anything.



Confusing Test case of CTAS in NOLOGGING mode

Asif Momen, September 25, 2006 - 7:50 am UTC

Hi Tom, 

Sure NOLOGGING can be used for a CTAS and its suppose to generate redo information for the Create Table and not for the data. But what I have found is very different, below is the test case.

/********************* redo.sql **********************/
select b.name, a.value
  from v$sesstat a,
       v$sysstat b
 Where a.statistic# = b.statistic#
   and a.sid = 9
   and b.name = 'redo size';
/********************* redo.sql **********************/




SQL> select count(*) from big_table;&#8206;

&#8206;  COUNT(*)&#8206;
&#8206;----------&#8206;
&#8206;   1865664&#8206;

SQL> @redo

NAME                                             VALUE
&#8206;--------------------------------------------- ----------&#8206;
redo size                                       3479344&#8206;


create a table with NOLOGGING mode &#8206;

SQL> create table test_nologging NOLOGGING as select * from big_table;&#8206;

Table created.&#8206;

SQL> @redo

NAME                                                VALUE
&#8206;----------------------------------------------- ----------&#8206;
redo size                                         3590256&#8206;

Now, create a table with LOGGING mode

SQL> create table test_logging LOGGING as select * from big_table;&#8206;

Table created.&#8206;

SQL> @redo

NAME                                               VALUE
&#8206;------------------------------------------------ ----------&#8206;
redo size                                          3696924&#8206;

SQL> select 3696924 - 3590256, 3590256 - 3479344 from dual;&#8206;

&#8206;3696924-3590256 3590256-3479344&#8206;
&#8206;--------------- ---------------&#8206;
&#8206;         106668          110912&#8206;

It's nearly the same amount of redo generated for both cases.&#8206;

Lets see approximately how much redo is generated for the Create Table command

SQL> create table test as select * from big_table where 1=0;&#8206;

Table created.&#8206;

SQL> @redo

NAME                                                 VALUE
&#8206;--------------------------------------------------- ----------&#8206;
redo size                                          3712600&#8206;

SQL> select 3712600-3696924 from dual;&#8206;

&#8206;3712600-3696924&#8206;
&#8206;---------------&#8206;
&#8206;          15676&#8206;

Now, this redo is very very less, that means I am getting a high redo for the data as &#8206;well.  Could you please put some light on this?&#8206;

Is it because of the tablespace is in LOGGING mode? &#8206;

Thanks for the wonderful service.&#8206;
 

Tom Kyte
September 25, 2006 - 2:59 pm UTC

you are likely in NOARCHIVELOG mode.

there, create table as select generates no redo for the table data regardless.

nologging only really counts in archivelog mode databases.

a create table as select writes directly to disk using direct IO, it doesn't write to the buffer cache, so in noarchivelog mode databases - there is no reason to generate redo. you don't need to support media recovery, so it skips it.

Confusing Test case of CTAS in NOLOGGING mode

Asif Momen, September 27, 2006 - 5:48 pm UTC

As usual you are RIGHT, I am in NOARCHIVELOG mode. Thanks for your reply.

Hats off to you !!!!

What is the rule for logging in archivelog mode database?

Pauline, November 09, 2006 - 4:18 pm UTC

Tom,
We create the new database by 10g dbca (noarchivelog as default)and use exp/imp method to migrade database from 8.1.5 to 10.2. Then we change the database to archivelog mode. We found some sys and system tables are nologging such as SYS.LOGMNRT_* and SYSTEM.OL* tables. Please see below:

ORACLE@anetprd > select count(*) ,logging from dba_tables where owner='SYS'
2 group by logging
3 /

COUNT(*) LOG
---------- ---
609 YES
56
40 NO

Is any reason ORAcLE having them as nologging?

In archive log mode database, should we put every table as logging including sys/system/DBSNMP's tables? If so, we need to alter these tables to logging.

Please tell us your thoughts.

Thanks.

Tom Kyte
November 09, 2006 - 6:29 pm UTC

it is ok, it is normal. we don't do bulk/direct path operations on them day to day, so it is not affecting a thing.

nologging only affects bulk direct path operations like alter table T move, insert /*+ append */, sqlldr direct=y and so on

it does not affect normal transactions

and that is all that happens on these - normal stuff.

Clear and good answer

A reader, November 10, 2006 - 11:48 am UTC

Thanks for your response. It helps me to understand further.

nologging logs DDL ?

A reader, February 09, 2012 - 2:57 pm UTC

hi tom,

can i check with you , in the documentation it writes.

-----
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses005.htm#CJAHABGF

Specify LOGGING if you want the creation of a database object, as well as subsequent inserts into the object, to be logged in the redo log file.

Specify NOLOGGING if you do not want these operations to be logged.

For a nonpartitioned object, the value specified for this clause is the actual physical attribute of the segment associated with the object.
----------

q1) does nologging logs the creation of object ?
i tried

- do a backup
- create a table with nologging
- restore and recover the backup
- alter database open

well, the table with nologging is still there.

---------------
If the database is run in archivelog mode, then media recovery from a backup made before the LOGGING operation re-creates the object. However, media recovery from a backup made before the NOLOGGING operation does not re-create the object.
--------------

i done a media recovery from a backup before the nologging operation, but the nologging table is there after the recovery.

i do not have forcelogging on.

Regards,
Noob
Tom Kyte
February 09, 2012 - 7:45 pm UTC

q1) nologging applies ONLY to direct path operations.

the DDL of the create table is always logged.

An insert /*+ append */ into the table (direct path load) could skip logging.
An insert /* without append */ into the table will always be logged.
A create table as select can skip logging of the table data - BUT NOT the ddl
An alter table t move can skip logging.
An update, insert (noappend), delete will always log.

A reader, February 10, 2012 - 12:57 am UTC

thanks tom for the clarification, now i am clear.

but it seems that the document is somehow describing the behaviour differently or have i misunderstood the content ?

regards,
Noob
Tom Kyte
February 10, 2012 - 5:05 pm UTC

when they said "creation", I believe they were referring to the create table as select - the 'selected' data would not be logged.

the underlying ddl operations are always logged, each and every change to the system catalog is always logged.

otherwise, an unlogged operation would render your data dictionary corrupt in the event of a media recovery.


Thank goodness

A reader, February 10, 2012 - 1:23 pm UTC

A create table as select can skip logging of the table data - BUT NOT the ddl.

I can't imagine the mess possible if Oracle skipped logging on changes to the data dictionary.

nologging but still logged (for direct path insert)

A reader, February 10, 2012 - 3:34 pm UTC

hi tom,

as you have mentioned
nologging affect direct-path insert 

- but i can still see my inserted row after restoring a backup before the nologging operation
- neither my tablespace nor my db is in forcelogging

-------------

SQL> create table test (a number) nologging;
Table created.
SQL> 

SQL> INSERT /*+APPEND*/ INTO TEST VALUES (888);
1 row created.
SQL> commit;
Commit complete.


C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Sat Feb 11 05:28:33 2012

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

connected to target database: ORA10 (DBID=738611814)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     524288000 bytes

Fixed Size                     1304220 bytes
Variable Size                155191652 bytes
Database Buffers             360710144 bytes
Redo Buffers                   7081984 bytes

RMAN> restore database;
.......
......
RMAN> recover database;
.......
........
media recovery complete, elapsed time: 00:01:38
Finished recover at 11-FEB-12
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.

C:\Documents and Settings\Administrator>sqlplus szejie/abc123

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Feb 11 05:33:03 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * From test;

         A
----------
       888

SQL>

------------

Any idea why tom ?

Hope to hear your advice.
Regards,
Noob





---------------------


Tom Kyte
February 10, 2012 - 5:34 pm UTC

SQL> INSERT /*+APPEND*/ INTO TEST VALUES (888);


that only works with insert AS SELECT.

to see if a direct path write was used, just query the table after and see if it can be read or not. If it can be read, it was NOT a direct path insert! You have to commit after a direct path insert before you can query it:


ops$tkyte%ORA11GR2> create table t ( x number ) nologging;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert /*+ APPEND */ into t values ( 42 );

1 row created.

ops$tkyte%ORA11GR2> select * from t;

         X
----------
        42

ops$tkyte%ORA11GR2> insert /*+ APPEND */ into t select 42 from dual;

1 row created.

ops$tkyte%ORA11GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte%ORA11GR2> 



first one, regular insert, second one, direct path.

A reader, February 11, 2012 - 10:10 am UTC

kudos to you tom. thank you very much . really.

nologging but still logged (for direct path insert)

A reader, February 11, 2012 - 12:09 pm UTC

hi tom,

just curious,

why is it that a direct path insert will result in

ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Regards,
Noob
Tom Kyte
February 13, 2012 - 7:46 am UTC

because no undo is generated for a direct path insert (which is a normal side effect of a parallel insert) and hence read consistency cannot be supported for the session that did the insert - so we do not allow you to read it until you commit (at which point, read consistency can be supported)

nologging but still logged (for direct path insert)

A reader, February 13, 2012 - 10:50 am UTC

hi tom,

thanks for the explanation.
As you mentioned "because no undo is generated for a direct path insert (which is a normal side effect of a parallel insert)"

Reading further on, a direct-path insert can be in serial or parallel mode as well.

q1)how do you know that i am doing a parallel insert ?
understand for parallel insert, direct-path is the default mode.
what is the default mode for direct-path insert ? (serial or parallel )?


q2) if i am using a standard edition db, i can only do a serial direct path insert right ?

q3) which is it that is causing undo not being generated
- direct path insert or parallel insert ?

q4)if i am inserting in serial mode, will i be able to select from the table after a direct-path insert without committing ?

q5) how do i know if my session is in parallel or direct mode ?

Hope to hear your advice.
Thanks
Regards,
Noob
Tom Kyte
February 13, 2012 - 12:11 pm UTC

q1) i didn't ? when did I say I did?


insert /*+ append */ by default is serial.


q2) no parallel operations, correct.

q3) direct path operations do not generate undo. create table as select, alter table t move, insert /*+ APPEND */, sqlldr direct=y.....

q4) no.

q5) you would have told it to be in parallel mode.

nologging but still logged (for direct path insert)

A reader, February 13, 2012 - 12:40 pm UTC

hi tom,

thanks for the prompt reply.

You mentioned in the earlier thread ->

"because no undo is generated for a direct path insert (which is a normal side effect of a parallel insert)"

so i misunderstood that, direct path insert is a normal side effect of parallel insert , which also indirectly implies i am doing a parallel insert.

sorry about that.
-------------------------------
last question - is there anyway i can check from the v$ table my current session is in parallel or serial mode ?

Regards,
Noob

Tom Kyte
February 13, 2012 - 12:50 pm UTC

I didn't mean to imply you were doing parallel. It was just more information. If you do a parallel insert - it'll always be direct path.


$ plus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 13 13:49:23 2012

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


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

ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> select name, value, isdefault
  2    from v$ses_optimizer_env
  3   where sid = (select sid from v$mystat where rownum=1)
  4     and name like 'parallel%'
  5  /

NAME                           VALUE                     ISD
------------------------------ ------------------------- ---
parallel_execution_enabled     true                      YES
parallel_threads_per_cpu       2                         YES
parallel_query_mode            enabled                   YES<b>
parallel_dml_mode              disabled                  YES</b>
parallel_ddl_mode              enabled                   YES
parallel_degree_policy         manual                    YES
parallel_degree                0                         YES
parallel_min_time_threshold    10                        YES
parallel_query_default_dop     0                         YES
parallel_degree_limit          65535                     YES
parallel_force_local           false                     YES
parallel_max_degree            8                         YES
parallel_autodop               0                         YES
parallel_ddldml                0                         YES

14 rows selected.

ops$tkyte%ORA11GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> select name, value, isdefault
  2    from v$ses_optimizer_env
  3   where sid = (select sid from v$mystat where rownum=1)
  4     and name like 'parallel%'
  5  /

NAME                           VALUE                     ISD
------------------------------ ------------------------- ---
parallel_execution_enabled     true                      YES
parallel_threads_per_cpu       2                         YES
parallel_query_mode            enabled                   YES<b>
parallel_dml_mode              enabled                   NO</b>
parallel_ddl_mode              enabled                   YES
parallel_degree_policy         manual                    YES
parallel_degree                0                         YES
parallel_min_time_threshold    10                        YES
parallel_query_default_dop     0                         YES
parallel_degree_limit          65535                     YES
parallel_force_local           false                     YES
parallel_max_degree            8                         YES
parallel_autodop               0                         YES
parallel_ddldml                0                         YES

14 rows selected.


A reader, February 14, 2012 - 1:53 pm UTC

thanks tom!