You are right, ALTER TABLESPACE .. NOLOGGING has zero effect. I just
confirmed this when importing a 2G database in Oracle9i 9.0.1.2, into
a nologging tablespace. So don't worry about the overhead of logging.
But .. only if this refers to the logging/nologging option on the
tablespace. Writing the redo logs is overhead for sure and the idea to
turn it off makes much sense to me too. Only Oracle will generally not
let you do that. The nologging option on the tablespace or table only
aplies to very particular inserts (from SQL*LOADER and CREATE TABLE ..
AS SELECT FROM ..). I confirmed that the 2G import in a nologging
tablespace wrote about 2G of redo log files as usual.
Y <a_at_b.c> wrote in message news:<3C731509.BC06AB07_at_b.c>...
> damorgan wrote:
>
> > Likely a complete waste of time. 95% of the time when people starting
> > worrying about the overhead of logging ... they either have no real
> > performance problem ... or they have not run EXPLAIN PLAN, TKPROF, and
> > DBMS_PROFILER against their application.
> >
> > In most cases, there is more to be gained from one good index than there is
> > from nologging.
>
> Good point!
> I just want to say, for OLTP applications. We got no more benefit from turn off
> logging in most cases. Just like Daniel said, you'd better check the access
> plan, collect current statistics, ...
> But for those applications which involve heaven update sql requests, I hope I
> can turn off logging.
> Regards,
>
> >
> >
> > Daniel Morgan
> >
> > Stan wrote:
> >
> > > am planning to have nologging turned on for perfstat tablespace as my
> > > statpack's snap might run every hour, whereby avoiding the overhead that
> > > it might create like writing rollbacks, filling up redologs..etc
> > >
> > > Is this a good idea ? any dis-advantages....
> > >
> > > -Stan
Received on Wed Feb 20 2002 - 03:10:05 CST
