RE: Nologging clause
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <riyaj.shamsudeen@xxxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxx>
- Date: Wed, 1 Apr 2009 08:10:11 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Riyaj Shamsudeen
Sent: Tuesday, March 31, 2009 11:24 PM
To: Mark.Bobak@xxxxxxxxxxxx
Cc: greg@xxxxxxxxxxxxxxxxxx; William Wagman; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx>
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Greg Rahn [greg@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, March 31, 2009 8:26 PM
To: William Wagman
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
