Oracle Active data guard-为什么在主数据库和备用数据库中查询的执行计划会有所不同

亲爱的汤姆，

希望你做得很好!!

我们已经在本地数据库 (主数据库) 和物理备用 (即在云AWS EC2服务器上) 之间实现了Oracle-Active data guard设置。我们配置了备用参数 (最大可用) 模式。我们计划将只读报告卸载到备用数据库，当我们测试数据库之间的查询执行计划时，我们看到了explain计划中的差异，以及为什么备用数据库会有不同的一致读取和时间。

我进行了google搜索，但不确定找到的信息是否准确。他们提到SGA参数细节将与主要参数不同，并且会显示出一些性能差异。

我想了解一下，在活动dataguard备用数据库上执行SQL是否与主数据库不同？如果是，还有哪些其他可能的方法可以用来提高性能？

提前谢谢。

主数据库:

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)

*

物理备用数据库:

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)

问候，
Sai

我看不到计划有任何差异，只有在一致的情况下。

由于初始解析，active dataguard节点很可能不得不做更多的工作，因为字典缓存中的项目较少 (因为它不像主要使用的那样活跃)