Skip to Main Content
  • Questions
  • Active data guard - Why would be there difference in execution plan of the QUERY in primary and standby database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sai.

Asked: November 03, 2020 - 5:28 am UTC

Answered by: Connor McDonald - Last updated: November 18, 2020 - 11:57 pm UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

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

and we said...

I don't see any differences in the plan, only in the consistent gets.

Most likely the active dataguard node had to do more work due the initial parsing because there were less items in the dictionary cache (because its not as actively used as the primary)

and you rated our response

  (1 rating)

Reviews

November 18, 2020 - 3:56 pm UTC

Reviewer: Sai

Thanks a lot for your response. It helps me to understood more clearly.

Though query using same execution path/plan, due to resource utilization we are facing performance difference between Primary vs Standby. We set up SGA is 30GB in primary and 5GB in standby database. I am suspecting that this would be major factor to show impact on SQL execution time.

Is there any other possibilities where we can improve SQL execution performance ?

Thanks in advance.
Connor McDonald

Followup  

November 18, 2020 - 11:57 pm UTC

Look at tracing the *bigger* queries (ie, the ones that run longer) and see where the time is lost. Then we can come up with some recommendations

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database