Re: Avoiding redo log file entries using NOLOGGING table mode and /*+ APPEND */ hint

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 5 Feb 2009 07:25:26 -0800 (PST)
Message-ID: <a7575466-edf3-4b1b-9907-4f2b889890a9_at_g3g2000pre.googlegroups.com>



On Feb 4, 4:42 pm, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
>
> On Feb 4, 1:59 pm, dana_at_w..._at_yahoo.com wrote:
>
> > I'm working on a 9i database in ARCHIVELOG mode. I don't have DBA
> > privs on the database or the SELECT_CATALOG_ROLE (tough to inspect
> > things behind the scenes without this).
>
> > I'm trying to reduce the redo log file activity so the disk volume
> > won't fill up during large loads (before filled logs get copied off to
> > another disk volume).
>
> > One tip I've read about is using direct path inserts; to create and/or
> > alter a table to NOLOGGING mode, e.g. CREATE TABLE X (...) NOLOGGING;
> > and then to use the /*+ APPEND */ hint on INSERT INTO ... SELECT
> > statements--that this would reduce redo log entries considerably.
>
> NOLOGGING *can* reduce redo log entries, but it may not.  You hit upon
> a glitch with that plan later on in your text.  And the /*+ APPEND */
> hint doesn't affect redo, it only affects the possible speed of
> inserts as it bypasses the search for available slots in populated
> data blocks.
>
> > My question is: how can I verify, without having DBA privs or the
> > SELECT_CATALOG_ROLE, that redo entries aren't being generated? Would a
> > rollback fail to rollback the records inserted via direct path, for
> > instance?
>
> Rollback is rollback, and direct path or conventional path won't
> matter.
>
> > I've also read that FORCE LOGGING can be set at the database level in
> > Oracle 9iR2.
>
> And also in later releases and it's designed for use in Data Guard to
> ensure all changes to the primary are replicated to the standby.
>
> > And if that's the case, then my NOLOGGING requests won't
> > be honored.
>
> True.  Which is the glitch I mentioned earlier.
>
> > What are some other methods for reducing redo log entries outside of,
> > say, using SQL*LOADER?
>
> I don't understand how using SQL*Loader will reduce your redo log
> entries if logging is in force.  The same rules apply with that
> utility as they do with direct path and conventional path inserts.
>
> > Disabling PK and UNIQUE constraints (and so
> > preventing the underlying UNIQUE indexes from being popualted) seems
> > like one good method.
>
> Disabling the constraint may not prevent the index from being updated,
> as it may be based upon a non-unique index which won't be disabled
> with the constraint:
>
> SQL> create table pktest(
>   2          yark number,
>   3          ying varchar2(90)
>   4  );
>
> Table created.
>
> SQL>
> SQL> create index pktest_pk
>   2  on pktest(yark)
>   3  /
>
> Index created.
>
> SQL>
> SQL> alter table pktest
>   2  add constraint pktest_pk
>   3  primary key (yark)
>   4  using index;
>
> Table altered.
>
> SQL>
> SQL> begin
>   2          for i in 1..10000 loop
>   3                  insert into pktest values (i, 'Test'||i);
>   4          end loop;
>   5  end;
>   6  /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> set autotrace on
> SQL>
> SQL> select *
>   2  from pktest
>   3  where yark = 78;
>
>       YARK YING
> ----------
> ---------------------------------------------------------------------------­----
>         78 Test78
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3056812865
>
> ---------------------------------------------------------------------------­--------------
> | Id  | Operation                   | Name      | Rows  | Bytes | Cost
> (%CPU)| Time     |
> ---------------------------------------------------------------------------­--------------
> |   0 | SELECT STATEMENT            |           |     1 |    60 |
> 1   (0)| 00:00:01 |
> |   1 |  TABLE ACCESS BY INDEX ROWID| PKTEST    |     1 |    60 |
> 1   (0)| 00:00:01 |
> |*  2 |   INDEX RANGE SCAN          | PKTEST_PK |     1 |       |
> 1   (0)| 00:00:01 |
> ---------------------------------------------------------------------------­--------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    2 - access("YARK"=78)
>
> Statistics
> ----------------------------------------------------------
>          24  recursive calls
>           0  db block gets
>           9  consistent gets
>           0  physical reads
>         124  redo size
>         479  bytes sent via SQL*Net to client
>         416  bytes received via SQL*Net from client
>           2  SQL*Net roundtrips to/from client
>           0  sorts (memory)
>           0  sorts (disk)
>           1  rows processed
>
> SQL>
> SQL> set autotrace off
> SQL>
> SQL> alter table pktest
>   2  disable constraint pktest_pk;
>
> Table altered.
>
> SQL>
> SQL> begin
>   2          for i in 1..10000 loop
>   3                  insert into pktest values (i, 'Test'||i);
>   4          end loop;
>   5  end;
>   6  /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> set autotrace on
> SQL>
> SQL> select *
>   2  from pktest
>   3  where yark = 78;
>
>       YARK YING
> ----------
> ---------------------------------------------------------------------------­----
>         78 Test78
>         78 Test78
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3056812865
>
> ---------------------------------------------------------------------------­--------------
> | Id  | Operation                   | Name      | Rows  | Bytes | Cost
> (%CPU)| Time     |
> ---------------------------------------------------------------------------­--------------
> |   0 | SELECT STATEMENT            |           |     2 |   120 |
> 2   (0)| 00:00:01 |
> |   1 |  TABLE ACCESS BY INDEX ROWID| PKTEST    |     2 |   120 |
> 2   (0)| 00:00:01 |
> |*  2 |   INDEX RANGE SCAN          | PKTEST_PK |     2 |       |
> 1   (0)| 00:00:01 |
> ---------------------------------------------------------------------------­--------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    2 - access("YARK"=78)
>
> Note
> -----
>    - dynamic sampling used for this statement
>
> Statistics
> ----------------------------------------------------------
>           9  recursive calls
>           0  db block gets
>          62  consistent gets
>           0  physical reads
>           0  redo size
>         528  bytes sent via SQL*Net to client
>         416  bytes received via SQL*Net from client
>           2  SQL*Net roundtrips to/from client
>           0  sorts (memory)
>           0  sorts (disk)
>           2  rows processed
>
> SQL>
>
> > Thanks.
>
> > Dana
>
> Possibly you should consider adding space to the volume, rather than
> trying to alter the redo log generation of the database.  Those
> archived logs are created for a reason, and to interfere with that can
> drastically affect recoverability.
>
> You should really think this entire situation through thoroughly
> before you engage in any possible rash activity.
>
> David Fitzjarrell

"Rollback is rollback, and direct path or conventional path won't matter. "

Except that if a direct path load is performed and it fails the indexes can be left in an unusable state requireing the indexes to be rebuilt.

Use of nologging also makes restoration of the object via media recovery (restore and roll forward) impossible which is a consideration that might rule out the use of the feature.

If DataGuard is in use then nologging operations are a no no.

HTH -- Mark D Powell -- Received on Thu Feb 05 2009 - 09:25:26 CST

Original text of this message