排查逻辑Standby日志应用缓慢之性能分析(原创)

通常来讲逻辑备库可为业务提供报表查询来分担主库上的查询压力，而逻辑备库数据同步的缓慢也在一定程度上影响了用户的正常使用。至于什么样的备库才认为是数据同步缓慢呢,笔者认为这和业务人员对逻辑备库的需求相关，因人而异。下面从几个方面来描述，如何排查逻辑备库数据同步缓慢的问题。
a) 检查所有的日志是否同步到逻辑备库中
1.确认主库是否配置正确的远程归档路径。例如
log_archive_dest_2="SERVICE=stby LGWR"
log_archive_dest_n的配置可参考
http://czmmiao.iteye.com/blog/1311070
2.确认成功归档到远程归档路径
在主库上进行日志切换后进行如下查询。
SQL> select dest_id "id",
???? status "db_status",
???? destination "Archive_dest",
???? error "Error"
???? from v$archive_dest;
如果远程归档路径没有成功归档，那么上述查询将会显示出错误信息，应从以下几方面进行排查：
检查tnsnames.ora中是否正确配置网络服务名。
检查LOG_ARCHIVE_DEST_n中的网络服务名是否正确配置
检查LOG_ARCHIVE_DEST_STATE_n是否为enable，而不是defer等参数。
检查listener.ora是否正确配置，监听是否启动，逻辑备库是否启动
检查逻辑备库是否被正确创建
检查逻辑备库上的standby_archive_dest路径下是否有最新的归档日志，如果没有配置standby_archive_dest，则到归档路径下查找在逻辑备库上执行如下查询
3.确认逻辑备库的日志应用进程是否正在运行，在逻辑备库上执行如下查询
SQL> select pid, type, status, high_scn
???? from v$logstdby;
仔细关注下HIGH_SCN列，看是否在主库进行归档后的每次查询时都会发生变化，如果没有则说明未启用日志应用进程。
启用逻辑备库上的日志应用即可
SQL> alter database start logical standby apply;
b) 日志gap
逻辑备库上执行如下查询
SQL> select applied_scn,newest_scn from dba_logstdby_progress;
APPLIED_SCN NEWEST_SCN
----------- ----------
???? 162497???? 162497
该查询需多次执行以确认是否正常同步。如果查询结果中NEWEST_SCN和APPLIED_SCN一致，说明逻辑备库已经应用了所有接收到的日志。如果不一致，可在主库上执行，如下SQL，确认是否有日志GAP产生，备库上缺少的是哪些日志文件
SQL> select thread#,sequence#,first_change#
???? from v$archived_log
???? where first_change# > &newest_scn_from_standby;

?? THREAD#? SEQUENCE# FIRST_CHANGE#
---------- ---------- -------------
???????? 1???????? 87??????? 163232
???????? 1???????? 88??????? 163235
从上述查询中可得，产生了GAP，备库上缺失的日志未thread 1的87、88号日志文件。确认远程归档路径是否正确
SQL> select dest_id,target,db_unique_name,status
???? from v$archive_dest
???? where target='STANDBY';

?? DEST_ID TARGET? DB_UNIQUE_NAME???????????????? STATUS
---------- ------- ------------------------------ ---------
???????? 1 STANDBY LR12?????????????????????????? ERROR
查询结果表明归档到逻辑备库LR12的log_archive_dest_1出现错误，需要检查该配置，以解决日志GAP的问题
当然上述问题也可以通过如下SQL查询解决
SQL> colormat 999
SQL> col sequence# format 99,999 heading Seq#
SQL> alter session set nls_date_format='YY/MM/DD HH24:MI:SS';
Session altered.

SQL> select thread#, sequence#, first_time, first_change#,
???????? next_time, next_change#
???? from dba_logstdby_log
???? where next_change# > ( select read_scn from dba_logstdby_progress)
???? order by first_change#;
THREAD#??? SEQ# FIRST_TIME?????? FIRST_CHANGE# NEXT_TIME???????? NEXT_CHANGE#
------- ------- ----------------- ----------- ----------------- ------------
????? 1????? 88 05/02/21 13:00:32????? 163235 05/02/21 13:00:33?????? 163238
????? 1????? 98 05/02/21 13:28:06????? 163919 05/02/21 13:28:36?????? 163931
????? 1????? 99 05/02/21 13:28:36????? 163931 05/02/21 13:28:52?????? 163938
可以看到逻辑备库缺失89~97号日志文件。在主库上确认远程归档路径是否正确配置
SQL> select dest_id,target,db_unique_name,status
???? from v$archive_dest
???? where target='STANDBY'

?? DEST_ID TARGET? DB_UNIQUE_NAME???????????????? STATUS
---------- ------- ------------------------------ ---------
???????? 1 STANDBY LR12?????????????????????????? DEFERRED
本例是由于log_archive_dest_1的状态为DEFERRED造成的。更改该状态后DG的日志GAP问题可自行解决
c) 确认逻辑备库在应用日志时是否出现报错
逻辑备库并不少所有的SQL语句都能应用，有些DML,DDL语句无法正常应用。当逻辑备库遇到无法正常应用的SQL语句或者包时，SQL应用操作将被停止。我们可以通过查询DBA_LOGSTDBY_EVENTS视图查看SQL应用时是否存在错误。具体SQL如下
SQL> select xidusn, xidslt, xidsqn, status, status_code
???? from dba_logstdby_events
???? where event_time =
?????????? (select max(event_time)
??????????? from dba_logstdby_events);
遇到SQL应用的问题，我们一般有两种就解决方式，
1、手工在备库上执行，如家表空间，数据文件等。
2、通过DBMS_LOGSTDBY.SKIP过程，在发生SQL应用冲突的对象上不进行SQL应用。注意：使用DBMS_LOGSTDBY.SKIP时需关闭sql应用，操作成功后再开启sql应用，用法举例：
alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'DML',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
alter database start logical standby apply;
通过以下查询确认当前的skip规则:
select * from dba_logstdby_skip;
取消skip的方法如下
alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','EYGLE','SALES');
exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');
alter database start logical standby apply;

dbms_logstdby.skip的语法：
DBMS_LOGSTDBY.SKIP (
stmt IN VARCHAR2,
schema_name IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL,
proc_name IN VARCHAR2 DEFAULT NULL,
use_like IN BOOLEAN DEFAULT TRUE,
esc IN CHAR1 DEFAULT NULL);
除stmt外，其它都是可选参数，并且看字面意义就能明白其所指，下面简单描述一下stmt参数调用的关键字都是指定值，详细见下列：

剩下的就和日常优化SQL无异了
1、分析SQL执行计划
2、查看v$session_wait中是否有和APPLIER相关的等待。
f) applier不够用？
查看当前逻辑备库上需要处理的事务
SQL> select available_committed_txn from v$logmnr_session
AVAILABLE_COMMITTED_TXN
-----------------------
?????????????????? 4437
我们可以通过如下SQL计算applier的应用速度
SELECT c.VALUE / (a.VALUE - b.VALUE) / 1024 / 1024 AS "APPLY_RATE"
????? FROM v$logstdby_stats a, v$logstdby_stats b, v$logstdby_stats c
??? WHERE a.NAME = 'coordinator uptime'
????? AND b.NAME = 'seconds system is idle'
????? AND c.NAME = 'bytes of redo processed';
Oracle文档上建议当查询值大于applier值时就应增加applier数量，但个人认为，该值需长期观察，如果普遍维持在某个值，比如说4000，且日志应用并不缓慢，则没有必要增加applier数量，那么我们也可以把4000当成逻辑备库上的合理值对待。
另外也可以通过查询V$LOGSTDBY_STATS还获得相关的状态信息。这些状态信息均可在DBMS_LOGSTDBY.APPLY_SET这个存储过程中设定。通过观察V$LOGSTDBY_STATS视图中的transactions ready和transactions applied值，我们可以确认是否事务是否及时同步。例如
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';
??? NAME??????????????????????? VALUE
??? -------------------------- ----------------------------
??? transactions ready????????? 159
??? transactions applied??????? 159
如果此时ready(等待apply)和applied(已经apply)的值基本同步，则设置的APPLIER进程合适或偏多。根据IDLE_APPLIER的进程数，可减少APPLIER进程数目.如果transactions ready - transactions applied的差比APPLIER进程数的2倍还多，则需要增加APPLIER进程数目了。增加APPLIER进程数目的操作如下
查看applier个数
SQL> SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';
查看空闲的applier个数
SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;
停止日志应用
SQL> alter database stop logical standby apply;?????????
Database altered.
增加applier个数
SQL> execute dbms_logstdby.apply_set('MAX_SERVERS',28);
PL/SQL procedure successfully completed.
或者
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);
PL/SQL procedure successfully completed.
开启日志应用
SQL> alter database start logical standby apply;
Database altered.
注意：applier的数量不应超过parallel_max_servers参数指定的值。
还有种情况，logical standby上有很多transactions等待apply, 但是还有空闲的applyer进程，但已经没有idle状态的PREPARER进程，这时需要增加PREPARER(调制机)的进程数。操作如下：
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4);
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
g) eager transaction
可通过如下查询，查询出SQL应用引擎正在做什么
select type,status_code,count(1) No_Of_Txns
? from v$logstdby
?group by type,status_code;

TYPE?????????????????????????? STATUS_CODE No_Of_Txns
------------------------------ ----------- ----------
READER?????????????????????????????? 16117????????? 1
APPLIER????????????????????????????? 16113????????? 2
APPLIER????????????????????????????? 16123????????? 6
APPLIER????????????????????????????? 16124??????? 106
BUILDER????????????????????????????? 16116????????? 1
ANALYZER???????????????????????????? 16116????????? 1
PREPARER???????????????????????????? 16116????????? 1
COORDINATOR????????????????????????? 16116????????? 1
代码对应如下
16113 -> Apply change to a particular object
16116 -> No work available
16117 -> Processing
16123 -> Transaction Waiting for Commit Approval
16124 -> Transaction Waiting on Another Transaction before proceeding
如果有较多applier的状态为16124则说明，逻辑备库上正在执行Eager Transaction。
Eager Transaction是dml超过200行数据的事务，从10g R1开始，更新行数超过200行的事务都被定义为Eager Transaction。Eager Transaction的目的主要是为了当超过200行的数据操作时，可以及时同步到备库中，而不必等待事务被完整建立(笔者个人看法，应该是当dml超过200行时，不必等待该dml操作结束即同步到备库上，待考证)。
多数情况下，Eager Transaction的机制很好的解决了数据实时同步的问题。但当应用程序产生的Eager Transaction较多时，则有可能出现applier不够用的情况，毕竟每个Eager Transaction都会消耗较多applier。
通过如下查询查看引起applier 16124, 16123状态的原因
col event format a50 trunc
select ls.status_code, s.event, count(1) No_Of_Appliers
? from v$logstdby ls
????? , v$streams_apply_server sas
????? , v$session s
? where ls.type = 'APPLIER'
?? and ls.status_code in ( 16124, 16123 )
?? and ls.logstdby_id = sas.server_id
?? and s.sid = sas.sid
?group by ls.status_code, s.event;

STATUS_CODE EVENT????????????????????????????????????????????? No_Of_Appliers
----------- -------------------------------------------------- --------------
????? 16123 rdbms ipc message????????????????????????????????????????????? 12
????? 16124 rdbms ipc message????????????????????????????????????????????? 97
????? 16124 db file sequential read???????????????????????????????????????? 1
可以看到大量的applier在等待rdbms ipc message事件。也就意味着有较多数量的eager transaction正在执行。我们可以通过增大eager transaction定义的数据行来达到减少eager transaction的目的。具体操作如下：
SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.apply_set('_EAGER_SIZE',<no_of_rows>);
SQL> alter database start logical standby apply [immediate];
注意：隐含参数_EAGER_SIZE的值不应超过1000。扩大该值同时有可能导致LCR Cache(通过MAX_SGA控制)被填满。
扩大_EAGER_SIZE后，建议检查是否出现换页，如果出现换页现象可以增大MAX_SGA或者适当减少_EAGER_SIZE来解决

参考至:《Oracle10g Data Guard SQL Apply Troubleshooting [ID 312434.1]》
??????????????? https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28userQuery=ID%20312434.1&startIndex=1&docsRequested=20&back=true&defaultSearch=true&facetIdClicked=&powerview_search=&cmd=getAllNodes&enterprise=true&clickstreamSource=Bookmark&requestedFacets=0&filterSource=KB%29%29
??????????????? http://www.eygle.com/digest/2009/02/logical_standby_manual.html
??????????????? http://junsansi.itpub.net/post/29894/458507

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@163.com