排查逻辑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参数调用的关键字都是指定值，详细见下列：

STMT关键字	包含的操作
NON_SCHEMA_DDL	不属于模式对象的所有其它ddl操作。 提示：使用该关键字时， SCHEMA_NAME 和 OBJECT_NAME 两参数也必须指定。
SCHEMA_DDL	创建修改删除模式对象的所有ddl操作 ( 例如 : tables, indexes, and columns)。 提示：使用该关键字时， SCHEMA_NAME 和 OBJECT_NAME 两参数也必须指定。
DML	Includes DML statements on a table (for example: INSERT, UPDATE, and DELETE)
CLUSTER	AUDIT CLUSTER CREATE CLUSTER DROP CLUSTER TRUNCATE CLUSTER
CONTEXT	CREATE CONTEXT DROP CONTEXT
DATABASE LINK	CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK DROP DATABASE LINK DROP PUBLIC DATABASE LINK
DIMENSION	ALTER DIMENSION CREATE DIMENSION DROP DIMENSION
DIRECTORY	CREATE DIRECTORY DROP DIRECTORY
INDEX	ALTER INDEX CREATE INDEX DROP INDEX
PROCEDURE	ALTER FUNCTION ALTER PACKAGE ALTER PACKAGE BODY ALTER PROCEDURE CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PACKAGE BODY DROP PROCEDURE
PROFILE	ALTER PROFILE CREATE PROFILE DROP PROFILE
ROLE	ALTER ROLE CREATE ROLE DROP ROLE SET ROLE
ROLLBACK STATEMENT	ALTER ROLLBACK SEGMENT CREATE ROLLBACK SEGMENT DROP ROLLBACK SEGMENT
SEQUENCE	ALTER SEQUENCE CREATE SEQUENCE DROP SEQUENCE
SYNONYM	CREATE PUBLIC SYNONYM CREATE SYNONYM DROP PUBLIC SYNONYM DROP SYNONYM
TABLE	ALTER TABLE CREATE TABLE DROP TABLE
TABLESPACE	CREATE TABLESPACE DROP TABLESPACE TRUNCATE TABLESPACE
TRIGGER	ALTER TRIGGER CREATE TRIGGER DISABLE ALL TRIGGERS DISABLE TRIGGER DROP TRIGGER ENABLE ALL TRIGGERS ENABLE TRIGGER
TYPE	ALTER TYPE ALTER TYPE BODY CREATE TYPE CREATE TYPE BODY DROP TYPE DROP TYPE BODY
USER	ALTER USER CREATE USER DROP USER
VIEW	CREATE VIEW DROP VIEW

d) 没有日志Gap - Paging?
当确认没有日志GAP，但 NEWEST_SCN和APPLIED_SCN依旧不一致时，我们应检查逻辑备库是否出现了数据页交换。相关资料可查阅
http://czmmiao.iteye.com/blog/1462941
应多次执行如下SQL，以确认是否进行了数据页交换
SQL> select value bytes
     from v$logstdby_stats
     where name = 'bytes paged out';

BYTES
--------------------------------------------------------
0
如果该查询结果不段上涨，则证明逻辑备库引擎正在进行换页。如果查询结果没有上涨，则说明在先前的某个时间点出现了换页，但该换页有可能不是引起日志应用延时的原因，需要进一步排查。
如果需要扩大逻辑备库的SGA可，通过如下步骤
1、确认当前SGA大小
SQL> select value     
     from v$logstdby_stats
     where name = 'maximum SGA for LCR cache';

VALUE
------------------------------
30
2、停止日志应用
SQL> alter database stop logical standby apply;
Database altered.
3、扩大SGA至100mb
SQL> execute dbms_logstdby.apply_set('MAX_SGA',100);
PL/SQL procedure successfully completed.
4、启动日志应用
SQL> alter database start logical standby apply immediate;
Database altered.
注意 ：逻辑备库上的 MAX_SGA， 10g之前最大值为 2047,10g R2之后增大至4095
e) 没有发生换页，倒底是发生了What
如果没有发生换页，则应查询当前逻辑备库上正在应用的SQL
SQL> select ls.serial# "Apply Process"
         , sas.state "State"
         , sas.sid SID
         , s.sql_address "SQL Address"
         , s.sql_hash_value "SQL Hash Value"
         , sa.sql_text "SQL Text"
     from v$logstdby ls
       , v$streams_apply_server sas
       , v$session s
      , v$sqlarea sa
    where ls.type = 'APPLIER'
    and sas.state != 'IDLE'
    and sas.serial# = ls.serial#
    and s.sid = sas.sid
    and sa.address (+) = s.sql_address
    and sa.hash_value (+) = s.sql_hash_value;

Apply Process State                       SID SQL Addr SQL Hash Value
------------- -------------------- ---------- -------- --------------
SQL Text
----------------------------------------------------------------------
           15 EXECUTE TRANSACTION         144 74925AA4     2104688481
update /*+ streams or_expand(p "FILLER" )restrict_all_ref_cons  */ "SCOTT"."FTS" p set "FILLER"=decode(:1,'N',"FILLER",:2), "ID"=decode(:3,'N',"ID",:4) where  (:5="FILLER" or(:5 is null and "FILLER" is null)) and(:6="ID") and rownum < 2
剩下的就和日常优化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

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

如有错误，欢迎指正

邮箱:[e-mail]