Large Redo Size Generated When Set Autotrace On for Query
(Doc ID 2431697.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation 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.
- After migrated from 11.2 to 12c, large redo size is generated when set autotrace on for query
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
----------------------------------------------------------
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
----------------------------------------------------------
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
- The large redo size is still generated even though unified auditing was disabled
- The log miner results show some INTERNAL operations besides the INSERT and DELETE of PLAN_TABLE$
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
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
- This problem can be reproduced by simple query as following, and the statistics of v$mystat shows that redo size for lost write detection is generated:
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
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 |