Can a query on the standby update the primary ?

You would think that (with the exception of the V$ tables which are predominantly memory structures reflecting the state of various parts of the database instance) a query on a read-only standby database would have absolutely no interaction with the primary.  After all, the standby database needs to be able to run independently of the primary should that primary database be down, or destroyed.

But there’s an exception to the rule.  Consider the following example.  I create a sequence on my primary database.


SQL> create sequence seq;

Sequence created.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

1 row selected.

SQL> select last_number from user_sequences where sequence_name = 'SEQ';

LAST_NUMBER
-----------
         21

1 row selected.

You can see that that it’s just a plain old sequence with the default cache value of 20.  Now let’s pop over to my standby (which was in recovery mode, so it now contains the same definition) and flick it out into read only mode.


SQL> alter database open read only;

Database altered.

SQL> conn scott/tiger
Connected.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
        21

On the standby as well, I can query the same sequence.  Notice that because it is a different instance to the primary (obviously), it picked up it’s value from the next cache boundary, much like multiple RAC instances can do.  But that suggests a potential issue – what happens when the primary keeps on selecting sequence values.  Will it collide with the sequence values obtained via queries on the standby ?  Let’s look back on the primary database.


SQL> select last_number from user_sequences where sequence_name = 'SEQ';

LAST_NUMBER
-----------
         41

1 row selected.


SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

1 row selected.

The primary is still using it’s “cached” values from 2 to 20, but notice the LAST_NUMBER has been incremented to 41.  The query on the standby database has sent a message to the primary to get the LAST_NUMBER column on USER_SEQUENCES (or more accurately, the internal table SEQ$) updated to avoid sequence number collisions.  If we continue to consume sequence values on the primary, you will see it skip over those numbers that are now “allocated” to the standby database in read only mode.


SQL> select seq.nextval from dual
  2  connect by level <= 30;

   NEXTVAL
----------
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        41
        42
        43
        44

So there you go …. Something I didn’t know was possible.  A query on the standby may enact an physical update on the primary.

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.