提高程序可扩展性一定要减少各类资源冲突

某批处理进程中有一个步骤，单进程运行10分钟，10个进程时运行超过3小时，即使使用parallel参数也无法提高速读，是cpu资源不足吗？语句如下：

[@more@]

create table test_table as
select
to_number(substr(bpid,1, 11)) ACCOUNT_NO,
bill_ref_no,
statement_date,
seq_bid_row.nextval BILL_INVOICE_ROW,
prd_inst_id SUBSCR_NO,
main_devno USER_ID,
…
from BILL_TABLE; （千万记录级别）

通过监控发现系统资源并没有非常紧张，而是产生大量row cache lock的等待事件，进一步发现这个等待事件和sequence有关。因为取sequence值会访问数据字典，当大批量取sequence值时会引起资源竞争。例如上面例子中bill_table是一张大表，因此会取大量sequence。如果是多进程就更严重，如果是RAC会'更更'严重…上面批处理进程就满足了大批量、多进程的特征。
减少这种资源瓶颈的方法是将sequence值cache到内存中，
alter sequence seq_bid_row cache 100;
修改之后，row cache lock大大降低，速度也上去了。

建sequence时，有两个参数和性能相关，需要注意：
1. CACHE
如果不指定CACHE/NOCACHE，默认是CACHE ，cache size为20。这在大多数系统中是足够的。如果大批量取，也可设大为100，也可以设更大。但要注意，很多工具生成的脚本却是NOCACHE。

NOCACHE在很多OLTP系统中也不是什么问题，但批量取性能较差，RAC中就更有问题了。
单Instance数据库单会话循环不间断去1-4万个值结果如下：
nocache： 37.7s 10000　　　
cache ：20 4.31s 10000

RAC两个会话分别处于不同node同时并发循环间断去取4万个值：
nocache：　　 2100s 40000
cache ：1000 　　 55s 40000

设置Cache，如果db重启，就会丢失这些sequence值，个数最多为cache size，当然这不是什么大问题。

2. Order
缺省是noorder。 Order对单实例数据库没有影响，在RAC情况下，多个节点会需要同步sequence值，因此有一定开销，除非必要，否则不要使用ORDER。

ORDER NOCACHE这个组合性能最差，要慎用。

ITPub博客

Sql优化(十) 程序的可扩展性—sequence上的竞争

最新文章