Posted by We had an interesting question on AskTom this week. The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably to ensure easier consistency in a physical standby node.
So … here’s a simple test case to demonstrate that this assertion is wrong.
First we build up table, and then sort ~2million rows both with the database in FORCE LOGGING and not in FORCE LOGGING modes. We’ll see SQL monitoring to compare.
FORCE LOGGING
SQL> drop table t purge;
Table dropped.
SQL> alter database force logging;
Database altered.
SQL>
SQL> create table t as select * from dba_objects;
Table created.
SQL>
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 nologging as select * from t where 1=0;
Table created.
SQL>
SQL> insert /*+ append monitor gather_plan_statistics */ into t1
2 select t.* from t,
3 ( select 1 from dual connect by level <= 20 )
4 order by 1,2,3,4,5;
1932580 rows created.
SQL> commit;
Commit complete.
SQL> set lines 300
SQL> set long 50000
SQL> set longchunksize 50000
SQL>
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
2 sql_id=>'3hc70p0d7036g',
3 type=>'TEXT',
4 report_level=>'ALL')
5 from dual
6 /
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'3HC70P0D7036G',TYPE=>'TEXT',REPORT_LEVEL=>'ALL')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
insert /*+ append monitor gather_plan_statistics */ into t1 select t.* from t, ( select 1 from dual connect by level <= 20 ) order by 1,2,3,4,5
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : MCDONAC (22:26495)
SQL ID : 3hc70p0d7036g
SQL Execution ID : 16777219
Execution Started : 06/17/2016 11:03:05
First Refresh Time : 06/17/2016 11:03:05
Last Refresh Time : 06/17/2016 11:03:33
Duration : 28s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Global Stats
=============================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=============================================================================================
| 28 | 5.35 | 22 | 0.00 | 0.97 | 38614 | 4942 | 271MB | 1554 | 512MB |
=============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2834581133)
========================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
========================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 22 | +7 | 1 | 2 | | | | | | | | |
| 1 | LOAD AS SELECT | | | | 22 | +7 | 1 | 2 | | | 263 | 253MB | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 96629 | 2958 | 22 | +7 | 1 | 2M | | | | | | | | |
| 3 | SORT ORDER BY | | 96629 | 2958 | 27 | +2 | 1 | 2M | 4913 | 259MB | 1291 | 259MB | 105M | 272M | | |
| 4 | MERGE JOIN CARTESIAN | | 96629 | 454 | 5 | +3 | 1 | 2M | | | | | | | | |
| 5 | VIEW | | 1 | 2 | 5 | +3 | 1 | 20 | | | | | | | | |
| 6 | CONNECT BY WITHOUT FILTERING | | | | 5 | +3 | 1 | 20 | | | | | 2048 | | | |
| 7 | FAST DUAL | | 1 | 2 | 1 | +3 | 1 | 1 | | | | | | | | |
| 8 | BUFFER SORT | | 96629 | 2958 | 5 | +3 | 20 | 2M | | | | | 16M | | | |
| 9 | TABLE ACCESS FULL | T | 96629 | 452 | 3 | +1 | 1 | 96629 | 28 | 13MB | | | | | | |
========================================================================================================================================================================================
1 row selected.
SQL>
SQL>
NO FORCE LOGGING
SQL> drop table t purge;
Table dropped.
SQL> alter database no force logging;
Database altered.
SQL>
SQL> create table t as select * from dba_objects;
Table created.
SQL>
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 nologging as select * from t where 1=0;
Table created.
SQL>
SQL> insert /*+ append monitor gather_plan_statistics */ into t1
2 select t.* from t,
3 ( select 1 from dual connect by level <= 20 )
4 order by 1,2,3,4,5;
1932640 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> set lines 300
SQL> set long 50000
SQL> set longchunksize 50000
SQL>
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
2 sql_id=>'3hc70p0d7036g',
3 type=>'TEXT',
4 report_level=>'ALL')
5 from dual
6 /
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'3HC70P0D7036G',TYPE=>'TEXT',REPORT_LEVEL=>'ALL')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
insert /*+ append monitor gather_plan_statistics */ into t1 select t.* from t, ( select 1 from dual connect by level <= 20 ) order by 1,2,3,4,5
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : MCDONAC (22:26495)
SQL ID : 3hc70p0d7036g
SQL Execution ID : 16777221
Execution Started : 06/17/2016 11:16:03
First Refresh Time : 06/17/2016 11:16:03
Last Refresh Time : 06/17/2016 11:16:15
Duration : 12s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Global Stats
=============================================================================================
| Elapsed | Cpu | IO | PL/SQL | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=============================================================================================
| 12 | 5.23 | 6.87 | 0.00 | 0.38 | 38453 | 4941 | 271MB | 1349 | 512MB |
=============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2834581133)
===================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
===================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 7 | +6 | 1 | 2 | | | | | | | | |
| 1 | LOAD AS SELECT | | | | 7 | +6 | 1 | 2 | | | 264 | 253MB | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 96632 | 2958 | 7 | +6 | 1 | 2M | | | | | | | | |
| 3 | SORT ORDER BY | | 96632 | 2958 | 12 | +1 | 1 | 2M | 4913 | 259MB | 1085 | 259MB | 105M | 272M | 100.00 | |
| 4 | MERGE JOIN CARTESIAN | | 96632 | 454 | 5 | +2 | 1 | 2M | | | | | | | | |
| 5 | VIEW | | 1 | 2 | 5 | +2 | 1 | 20 | | | | | | | | |
| 6 | CONNECT BY WITHOUT FILTERING | | | | 5 | +2 | 1 | 20 | | | | | 2048 | | | |
| 7 | FAST DUAL | | 1 | 2 | 1 | +2 | 1 | 1 | | | | | | | | |
| 8 | BUFFER SORT | | 96632 | 2958 | 5 | +2 | 20 | 2M | | | | | 16M | | | |
| 9 | TABLE ACCESS FULL | T | 96632 | 452 | 1 | +2 | 1 | 96632 | 28 | 13MB | | | | | | |
===================================================================================================================================================================================================
1 row selected.
SQL>
SQL>
As you can see, in both cases, the temporary segment consumption was comparable. The NOLOGGING case was much faster, which is to be expected since we are doing less redo operations. In a genuine physical standby scenario, this would perhaps be then offset by the need to then potentially re-transfer and recover the affected datafile(s) over to the standy node.
Learning is not a spectator sport 
A good Blog.
It is not clear in which Oracle version you have made the test.
My tests on Oracle 12.1.0.2.0 and 18.0.0.0.0 are allied with what the DBA said.
The problem seems that the first run makes a few of TEMP resizing like:
Resize operation completed for file# 2049, old size 3894264K, new size 3996664K
That caused the first run looks slower.