LOGGING and temporary space

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s