Dear Tom,

Hope you are doing great !!

We have implemented Oracle-Active data guard setup between On-premise database(primary) and physical standby (i.e. on cloud AWS EC2 server). We configured standby with parameter (Max. available) mode. We are planning to off-load the read-only reports on to standby database, to which when we are testing QUERY execution plan between the databases we have seen differences in explain plan and why would be there different Consistent reads and time with standby db.

I did google search and I am not sure this information I found is accurate or not. they mentioned that SGA parameter details won't be same as primary and it would show some differences in performance.

I would like to understand that does SQL execution on an active dataguard standby is not same as primary database ? if yes, what are other possible ways which can be used to improve performance ?

Thanks in advance.

PRIMARY DATABASE:

SELECT TO_CHAR (a.date_created, 'MM/DD/YYYY') date_, t.table_id, COUNT (1)
    FROM aer a, system_audit_session s, system_audit_trail t
   WHERE     a.aer_id = s.aer_id
         AND s.session_id = t.session_id
         AND a.aer_no = '20K-118-3096686-00'
         AND a.version_no = 1
GROUP BY TO_CHAR (a.date_created, 'MM/DD/YYYY'), t.table_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         28          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0         28          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 155  
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  HASH GROUP BY (cr=14 pr=0 pw=0 time=319 us cost=7 size=2112 card=33)
        14         14         14   NESTED LOOPS  (cr=14 pr=0 pw=0 time=118 us)
        14         14         14    NESTED LOOPS  (cr=13 pr=0 pw=0 time=87 us cost=6 size=14976 card=234)
         1          1          1     NESTED LOOPS  (cr=9 pr=0 pw=0 time=54 us cost=3 size=477 card=9)
         1          1          1      TABLE ACCESS BY INDEX ROWID AER (cr=4 pr=0 pw=0 time=34 us cost=1 size=38 card=1)
         1          1          1       INDEX RANGE SCAN IDX_AER_1 (cr=3 pr=0 pw=0 time=25 us cost=1 size=0 card=1)(object id 957918)
         1          1          1      TABLE ACCESS BY INDEX ROWID SYSTEM_AUDIT_SESSION (cr=5 pr=0 pw=0 time=20 us cost=2 size=495 card=33)
         1          1          1       INDEX RANGE SCAN IDX_SYS_AUDIT_SES_AER_ID (cr=4 pr=0 pw=0 time=14 us cost=1 size=0 card=33)(object id 957724)
        14         14         14     INDEX RANGE SCAN PK_SYSTEM_AUDIT_TRAIL (cr=4 pr=0 pw=0 time=34 us cost=1 size=0 card=28)(object id 957971)
        14         14         14    TABLE ACCESS BY INDEX ROWID SYSTEM_AUDIT_TRAIL (cr=1 pr=0 pw=0 time=8 us cost=1 size=297 card=27)

********************************************************************************

Physical Standby Database:

SELECT TO_CHAR (a.date_created, 'MM/DD/YYYY') date_, t.table_id, COUNT (1)
    FROM aer a, system_audit_session s, system_audit_trail t
   WHERE     a.aer_id = s.aer_id
         AND s.session_id = t.session_id
         AND a.aer_no = '20K-118-3096686-00'
         AND a.version_no = 1
GROUP BY TO_CHAR (a.date_created, 'MM/DD/YYYY'), t.table_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0         74          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         74          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 155  
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  HASH GROUP BY (cr=25 pr=0 pw=0 time=528 us cost=7 size=2112 card=33)
        14         14         14   NESTED LOOPS  (cr=25 pr=0 pw=0 time=427 us)
        14         14         14    NESTED LOOPS  (cr=22 pr=0 pw=0 time=359 us cost=6 size=14976 card=234)
         1          1          1     NESTED LOOPS  (cr=13 pr=0 pw=0 time=209 us cost=3 size=477 card=9)
         1          1          1      TABLE ACCESS BY INDEX ROWID AER (cr=5 pr=0 pw=0 time=100 us cost=1 size=38 card=1)
         1          1          1       INDEX RANGE SCAN IDX_AER_1 (cr=4 pr=0 pw=0 time=91 us cost=1 size=0 card=1)(object id 957918)
         1          1          1      TABLE ACCESS BY INDEX ROWID SYSTEM_AUDIT_SESSION (cr=10 pr=0 pw=0 time=107 us cost=2 size=495 card=33)
         1          1          1       INDEX RANGE SCAN IDX_SYS_AUDIT_SES_AER_ID (cr=7 pr=0 pw=0 time=14 us cost=70 size=0 card=33)(object id 957724)
        14         14         14     INDEX RANGE SCAN PK_SYSTEM_AUDIT_TRAIL (cr=7 pr=0 pw=0 time=34 us cost=1 size=150 card=28)(object id 957971)
        14         14         14    TABLE ACCESS BY INDEX ROWID SYSTEM_AUDIT_TRAIL (cr=3 pr=0 pw=0 time=42 us cost=1 size=297 card=27)

Regards,
Sai