Logical Standby日常维护与性能优化(二)—性能诊断与优化

   本篇主要讲述10gR2 logical stanby的性能优化方法.一些日常维护和故障处理要点,请参考

Logical Standby日常维护与性能优化(一)—日常维护与故障排除

    Logical Standby较physical standby容易出现性能上的问题。对于一个DML繁重，尤其是大事务比较多的库，SQL APPLY往往会出现性能上的瓶颈。因此导致备库‘hang’住或者无法跟上主库同步速度的情况也并不少见。
    下面描述一下逻辑备库在性能排查和诊断方面的一些切入点和方法：

1.诊断并调整LCR Cache

    LCR Cache由shared pool 中的一部分来分配，用于缓存LCRs。过小的cache会引起page out，严重减缓LCR BUILDER过程。如果时常存在大量而且频繁的page out,意味着当前设置的LCR cache过小或者数据库中存在较大的事务。
    查询v$v$logstdby_process视图，如果BUILDER进程经常出现ORA-16243: paging out *** bytes of memory to disk 之类的事件，说明LCR cache可能存在问题。
    同样，通过计算某段时间的pageout activity(%)，可了解LCR Cache的设置是否合适，通常该指标被认为低于5%
    A时间点：

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE ‘%page%’ or NAME LIKE ‘%uptime’ or name like ‘%idle%’;
NAME VALUE
——————————————————
coordinator uptime in secs 68752
bytes paged out 12521
secs spent in pageout 21
system idle time in secs 1301

    B时间点：

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE ‘%page%’ or NAME LIKE ‘%uptime’ or name like ‘%idle%’;
NAME VALUE
——————————————————
coordinator uptime in secs 69058
bytes paged out 225872
secs spent in pageout 56
system idle time in secs 1309

    Pageout activity计算如下：

Change in coordinator uptime (C) = (69058 – 68752) = 298 secs
Amount of additional idle time (I) = (1309 – 1301) = 8 secs
Change in time spent in pageout (P) = (56 – 21) = 35 secs
Pageout activity(%) = P/(C-I) = 35/(298-8) ~ 12.06%

    如果诊断LCR cache设置不合理，使用dbms_logstdby.appply_set包进行设置。

SQL> exec dbms_logstdby.appply_set(’MAX_SGA’, 2048);

2.诊断并调整SQL apply process

    在SQL Apply各进程中，可以调整的进程为PREPARER和APPLIER进程。所有的进程数目之和受MAX_SERVERS控制,而MAX_SERVERS最大值受初始化参数PARALLEL_MAX_SERVERS控制。
    Oracle推荐MAX_SERVERS数目设置为 (3*CPU) + 3
    Oracle会根据MAX_SERVERS的设置自动调整PREPARER和APPLIER的进程数目,手工指定PREPARER和APPLIER进程数目在10gR2版本上不是必须的。
    查询如下视图

SQL> select available_committed_txn from v$logmnr_session;
AVAILABLE_COMMITTED_TXN
———————–
50

或者

select a.value-b.value from v$logstdby_stats a ,v$logstdby_stats b where a.name=’transactions ready’ and b.name=’transactions applied’;
a.value-b.value
———————–
52

    如果得到的结果经常远大于appler进程的数目,则意味着需要调整appler的进程数量。

SQL> execute dbms_logstdby.apply_set(’MAX_SERVERS’,30);

3.诊断并调整long transaction

    实际环境中往往存在一些异常缓慢的apply事务，例如Apllier进程出现如下状态：ORA-16124: transaction * ** *** is waiting on another transaction
    这类情况会成为备库无法跟上主库同步进度的罪魁祸首。在apply进程数目和lcr cache设置得当的情况下，如果存在这类情况，需要根据情况逐步考虑。

(1).缺失高效索引造成的sql apply效率低下

    如果一次事务只是操纵了少量行数据，依旧造成了备库SQL Apply的异常运行缓慢，往往是由于索引缺失或者低效的问题。因为主库上的一个DML语句，到备库上会根据操纵的行数解析成多个语句执行。假设 primary端一次事务对A表删除了10条数据，进行了一次table scan，而在logical standby，如果依旧没有可用索引,会进行10次table scan来应用此事务
    可以根据v$logstdby_process得到活动的applier进程的当前sid，关联v$session,v$sql,v$sql_plan,v$session_wait等一系列视图，根据得到的SQL执行计划或者session等待情况进行判断。
    如必要,可以在备库上适当增加索引。

(2).Eager Transaction

    Eager Transaction是指那些操纵了大量行数据或能产生大量LCRs的事务,引入Eager Transaction的目的是为了提高对大事务的apply速度和减少对LCR cache的压力。在在10gR2里,区分Eager Transaction的默认临界点是201 LCRs,该参数可以通过隐含参数_eager_size来控制，在大部分情况下,并不推荐更改该参数的设置。而且,鉴于SQL Apply的原理和特点,增加该参数的设置对提升一些大批量操作的事务应用的性能提升非常有限。而且，过大的_eager_size会造成LCRs Cache的过度使用。
    对于一些大批量操作的事务,可以通过skip的方式跳过被操纵的表上的这段大事务处理后,恢复unskip。然后通过dbms_logstdby.instantiate_table或者手工方式初始化数据。

(3).其他争用

    同一般的库一样，高并发的环境同样在logical standby上出现一些资源上的争用,比如itl上,buffer上的争用等.具体可以关联v$session_wait/v$segment_statistics等查询判断并做处理。

4.调整TRANSACTION CONSISTENCY

    logical standby端,10gR2下有2种事务一致性模式:事务的提交顺序可以和主库上完全一致,也可以不一致。后者可以提高SQL APPLY的性能,只保证相关事务的一致性,但会得到不一致的数据版本。某些情况下，为了提高应用的进度,可以临时设置成后者，然后待需要时恢复。

SQL>exec dbms_logstdby.apply_set(’PRESERVE_COMMIT_ORDER’,'FALSE’);
SQL>exec dbms_logstdby.apply_set(’PRESERVE_COMMIT_ORDER’,'TRUE’);