DB version: 9.2
OS : AIX 5.9
I have a script with several INSERT INTO .... SELECT statements and these INSERTs are generating too much redo.
I gather that there is no NOLOGGING option for INSERT INTO .... SELECT statement.
And APPEND hint only seems to slightly reduce redo generation as shown in the demo below.
So, is there any other way to reduce redo generation for INSERT INTO .... SELECT
Testing the effect of APPEND hint on redo generation. As I am currently at home the below test was done in an 11.2.0.4 DB
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 3 12:52:34 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL> CREATE TABLE temp1
(
object_name VARCHAR2(40),
str1 VARCHAR2(40)
); 2 3 4 5
Table created.
SQL> SET AUTOTRACE ON
SQL> insert into temp1 select object_name,'AAAA' from all_objects ;
142536 rows created.
--- Below is the bottom section of auto trace output
Statistics
----------------------------------------------------------
169 recursive calls
5276 db block gets
36373 consistent gets
0 physical reads
5684228 redo size
825 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
801 sorts (memory)
0 sorts (disk)
142536 rows processed
SQL> commit;
----Now with APPEND hint (As you can see the redo is still generated , but less than the above scenario )
SQL> truncate table temp1;
Table truncated.
SQL> SET AUTOTRACE ON
SQL> insert /*+ append */ into temp1 select object_name,'AAAA' from all_objects ;
142536 rows created.
.
.
.
.
Statistics
----------------------------------------------------------
183 recursive calls
1005 db block gets
32937 consistent gets
0 physical reads
5298256 redo size
818 bytes sent via SQL*Net to client
835 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
801 sorts (memory)
0 sorts (disk)
142536 rows processed
SQL>