RE: Nologging clause

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 1 Apr 2009 08:10:11 -0400
Message-ID: <825B809D44C34110959BD31E4822429B_at_rsiz.com>

Riyaj:  

A most excellent list and reference including especially the reminder to readjust your worldview with securefiles in 11g!  

I'll add in my ongoing mission to remind everyone that "index organized" is an adjective and table is the noun in IOT. (Some folks forget the IOT is the primary record of the data when overly focused that its insert and update path requires non-append mode treatment. A table is still a table, whether it is "heap" (the plain old regular kind), IO, or any variety of cluster.)  

Speaking of clusters, I haven't tested, but I don't see how a table in a cluster could use direct path, since there is either a hash or an index involved.  

Oracle has been remarkably astute supplying the ability to directly format complete blocks in memory and slap a bunch of or them down with one write whenever it is logically possible to do so without having to come up for air to make row by row adjustments to something else.  

William: If it should come to pass that nologging doesn't work out for you and you go with noarchive, I would urge you to budget as much space as you can for online redo logs to maximize the window within which instance recovery is secure. Make them a size where you still switch however often you have already decided is good. Just make more of them.  

Regards,  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj Shamsudeen
Sent: Tuesday, March 31, 2009 11:24 PM
To: Mark.Bobak_at_proquest.com
Cc: greg_at_structureddata.org; William Wagman; oracle-l_at_freelists.org Subject: Re: Nologging clause  

There are few more oddities:

1. If there are any indices on these tables, direct path insert generates redo for those indices. Of course, IOT gets almost no benefit of direct path inserts.
2. If there are any row level triggers on the table, direct path insert resorts to conventional mode redo generation.
3. Any foreign key constraint on the table also can resort to conventional mode redo generation
4. There are some oddities with lob values when stored out-of-line too. With introduction of securefile in 11g, much seems to have changed. There are three sorts of logging now: logging|nologging|filesystem_like_logging.
5. Merge statement also can do direct path inserts..

 I presented some of it in RMOUG training days presentation (slides 30-36): http://orainternals.files.wordpress.com/2008/04/riyaj_debunking_myths_about_ redo_ppt.pdf

  Of course, that is for 9i/10g. I haven't tested it for 11g though..  

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com

On Tue, Mar 31, 2009 at 7:39 PM, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:

To add to Greg's answer a bit, there are a few other operations that are "direct path", in addition to INSERT /*+ APPEND */.

Off the top of my head:
index creation/rebuild
create table as select ...

And there may be a few others. Any non-append insert, any delete or update, will *always* log.

Hope that helps,

-Mark

PS As far as insert /*+ append */ goes, it only applies to a multi-row insert, like insert /*+ append */ into tab select * from tab2; Something like insert /*+ append */ into tab values(...); WILL log.

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn [greg_at_structureddata.org] Sent: Tuesday, March 31, 2009 8:26 PM

To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: Re: Nologging clause

This is correct. The [NO]LOGGING only changes direct path operations.  That is, inserts with the /*+ APPEND */ hint.

So if you want NOLOGGING you need either: - NO ARCHIVE LOG mode
- ARCHIVE LOG mode, NOLOGGING, and APPEND hint

On Tue, Mar 31, 2009 at 5:04 PM, William Wagman <wjwagman_at_ucdavis.edu> wrote:
> It appears that he is saying that if a table is set to nologging and my
insert mode is append then even if archive logging is on no redo will be generated. I'm still not sure I am understanding this correctly but I take that to mean
>
> SQL> insert /*+ APPEND */ into <table>;
>
> Then no redo will be generated. Is that a correct understanding?

--
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l



 




--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 01 2009 - 07:10:11 CDT