Tag Archives: Hint

ORACLE – redo-log by using append-hint, nologging and archive-log-mode

Here a very good overview about the combinations when Redo-Log is generated or not for inserts with and without logging/append-hint/archive-log-mode. Found by asktom.

Check DB for archive-log-mode turned on or off:
select log_mode from v$database;

Check for logging-mode for table:
select logging from all_tables
where owner = '<USER>'
and table_name = '<TABLE_NAME>';

Example for Direct-Path-Insert with append-hint:
insert into '<TABLE_NAME>' /*+ append */
select * from all_objects;


TableMode  Insert Mode ArchiveLogMode  result
---------- ----------- --------------- ----------
LOGGING    APPEND      ARCHIVE LOG     redogenerated
NOLOGGING  APPEND      ARCHIVE LOG     no redo
LOGGING    no append   ""              redogenerated
NOLOGGING  no append   ""              redogenerated
LOGGING    APPEND      noarchive       no redo
NOLOGGING  APPEND      noarchive       no redo
LOGGING    no append   noarchive       redogenerated
NOLOGGING  no append   noarchive       redogenerated

If “FORCE LOGGING” is used, than of course redo is generated!

Optimizer-Modes ALL_ROWS vs FIRST_ROWS

FIRST_ROWS and ALL_ROWS are cost based optimizer features.

Overview:

  • Default: ALL_ROWS
  • Change: alter [system|session] OPTIMIZER_MODE = [ALL_ROWS|FIRST_ROWS|FIRST_ROWS_1|FIRST_ROWS_10|FIRST_ROWS_100|FIRST_ROWS_1000]
  • Check: SHOW PARAMETER OPTIMIZER_MODE (with dba-role)
  • SQL – Hint for retrieving a few rows: /*+ FIRST_ROWS*/

 

http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams167.htm

first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).


http://oracle-online-help.blogspot.de/2007/03/optimizermode-allrows-or-firstrows.html