Oracle Database - Why you have still a lot of redo and archive ? The Index side effect

-

Oracle Database - Why you have still a lot of redo and archive ? The Index side effect

1 - About
You use the direct path insert and set the logging mode as NOLOGGING but you have still a lot of redo log generated.

 Why ?

 The answer is that you have an index on the table, and the index cannot just be appended to, they just be merged into. Since you need to merge data into them, you need redo in order to recover from instance failure. If your system crashed in the middle of the index operations, you would end up with a corrupt index structure. Hence, redo is generated for the index operations.

 2 - Articles Related
Oracle Database - How can we load a large number of rows into an indexed existing table ?
Oracle Database - Redo Log (Log Files)
Oracle Database - Redo Size statistics
3 - Indexes are expensive complex data structure
Oracle Database - Indexes are complex data structure, so maintaining them can be expensive.

 gerardnico@orcl> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG
The database is in Oracle Database - ARCHIVELOG mode.

 3.1 - Without Index

 gerardnico@orcl>SET autotrace ON statistics;
gerardnico@orcl>INSERT INTO big_table SELECT * FROM all_objects;

66651 rows created.


Statistics
----------------------------------------------------------
       6062  recursive calls
       9053  db block gets
     189602  consistent gets
          1  physical reads
    7533724  redo size
        901  bytes sent via SQL*Net TO client
        952  bytes received via SQL*Net FROM client
          4  SQL*Net roundtrips TO/FROM client
       1542  sorts (memory)
          0  sorts (disk)
      66651  rows processed
The data of the table generate 7,5MB of redo log.

 3.2 - With the index

 gerardnico@orcl>SET autotrace ON statistics;
gerardnico@orcl>CREATE INDEX big_table_idx ON big_table(owner,object_type,object_name);

INDEX created.

gerardnico@orcl>INSERT INTO big_table SELECT * FROM all_objects;

66652 rows created.


Statistics
----------------------------------------------------------
       6163  recursive calls
     174495  db block gets
     192120  consistent gets
        462  physical reads
   33037788  redo size
        906  bytes sent via SQL*Net TO client
        952  bytes received via SQL*Net FROM client
          4  SQL*Net roundtrips TO/FROM client
       1542  sorts (memory)
          0  sorts (disk)
      66652  rows processed
With the index, 33MB of redo are generated. 23MB only for the index !

Comments

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more