My Oracle Support Banner

Large Redo Size Generated When Set Autotrace On for Query (Doc ID 2431697.1)

Last updated on DECEMBER 12, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

  

12c:

Statistics
----------------------------------------------------------
      15458  recursive calls
  2  db block gets
     486798  consistent gets
     792525  physical reads
   26519728  redo size                              *<<<<<<<<<<----------
     912915  bytes sent via SQL*Net to client
       1254  bytes received via SQL*Net from client
 68  SQL*Net roundtrips to/from client
 37  sorts (memory)
  1  sorts (disk)
       1000  rows processed

  
11.2:

Statistics
----------------------------------------------------------
       2719  recursive calls
          2  db block gets
     410794  consistent gets
     758747  physical reads
          0  redo size                              *<<<<<<<<<<----------
     899477  bytes sent via SQL*Net to client
       1226  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
       1000  rows processed

 For example:

SCN    TIMESTAMP    SEG_NAME    SEG_OWNER    TABLE_NAME    TABLE_SPACE    OPERATION    SQL_REDO
1.1115E+13    8/2/2018 10:05    PLAN_TABLE$    SYS    PLAN_TABLE$    SYSTEM    INSERT    /* No SQL_REDO for temporary tables */
1.1115E+13    8/2/2018 10:05    PLAN_TABLE$    SYS    PLAN_TABLE$    SYSTEM    DELETE    /* No SQL_REDO for temporary tables */
1.1115E+13 8/2/2018 10:05                                            INTERNAL
SQL> conn <USERNAME>/<Password> as sysdba
Connected.
SQL> SELECT name, value
FROM v$mystat, v$statname
WHERE v$mystat.statistic#=v$statname.statistic#
and v$statname.name like '%redo size%'
ORDER BY 1;
  2    3    4    5
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                                 0
redo size for direct writes                                               0
redo size for lost write detection                                        0

SQL> select count(*) from t1;

  COUNT(*)
----------
    316777

SQL> SELECT name, value
FROM v$mystat, v$statname
WHERE v$mystat.statistic#=v$statname.statistic#
and v$statname.name like '%redo size%'
ORDER BY 1;
  2    3    4    5
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            502756
redo size for direct writes                                               0
redo size for lost write detection                                   502756

  

Changes

 In 12c environment, the initialization parameter DB_LOST_WRITE_PROTECT is set to non-default value from default value of NONE.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.