 | |
Avoiding redo log file entries using NOLOGGING table mode and /*+APPEND */ hint
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Avoiding redo log file entries using NOLOGGING table mode and /*+APPEND */ hint -
02-04-2009
, 01:59 PM
#2
| |||
| |||
|
|
Comments embedded. On Feb 4, 1:59*pm, dana_at_w... (AT) yahoo (DOT) 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 |
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 --
#3
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.