Re: Avoiding redo log file entries using NOLOGGING table mode and /*+ APPEND */ hint
Date: Wed, 4 Feb 2009 13:42:01 -0800 (PST)
Message-ID: <58c0c404-1423-4e0f-ae14-ded06ca614f4_at_t39g2000prh.googlegroups.com>
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 Received on Wed Feb 04 2009 - 15:42:01 CST