rac上的sequence

2025-04-16 技术教程

昨天和网友探讨，RAC中的SEQUENCE，awr报告如下，摘取关键部分：

在RAC环境中，序列的Cache问题可能会对性能有着决定性的影响，缺省的序列Cache值为20，这对RAC环境远远不够。

如果存在序列号使用的竞争，就可能在数据库中看到明显的队列等待：

enq: SQ - contention

在RAC情况下，可以将使用频繁的序列Cache值增加到10000，或者更高到50000，这些值在客户的环境中都有采用。

这是RAC设置和RAC使用的基本常识，不可或忘。

在以下测试中，可以显示Cache序列对于性能的影响：

RAC两个会话分别处于不同node同时并发循环间断去取4万个值：

nocache：　　 2100s

cache ＝1000：　　 55s

差别却是好大。

单Instance数据库单会话循环不间断去1-4万个值测试（在家里笔记本上测试结果）过程如下：

nocache： 37.7s 10000　　　

cache ：20 4.31s 10000

cache ：100 2.92s 10000

cache ：1000 5.56s 40000

nocache： 97.7s 40000

基本上cache 大于20的时候性能基本可以接受，最好设置100以上，

nocache的时候性能确实很差，最大相差20倍．

排序参数：oracle默认是NOORDER，如果设置为ORDER；在单实例环境没有影响，在RAC环境此时，多实例实际缓存相同的序列，此时在多个实例并发取该序列的时候，会有短暂的资源竞争来在多实例之间进行同步。因此性能相比noorder要差，所以RAC环境非必须的情况下不要使用ORDER，尤其要避免NOCACHE ORDER组合；

在某些版本中存在BUG，会导致过度的 enq : SQ 竞争。

如在Oracle Database 11g中存在 IDGEN$ 序列 cache 设置过小问题，可能导致严重竞争，建议增加该序列的Cache值设置。

RAC环境下与sequence相关的锁

oracle为了在rac环境下为了sequence的一致性，使用了三种锁：row cache lock、SQ锁、SV锁。

row cache lock的目的是在sequence指定nocache的情况下调用sequence.nextval过程中保证序列的顺序性；

SQ锁是应用于指定了cache+noorder的情况下调用sequence.nextval过程中。

SV锁（dfs lock handel) 是调用sequence.nextval期间拥有的锁。前提是创建sequence时指定了cache 和order属性 (cache+order)。order参数的目的是为了在RAC上节点之间生成sequence的顺序得到保障。

创建sequence赋予的cache值较小时，有enq:sq-contention等待增加的趋势。

cache的缺省值是20.因此创建并发访问多的sequence时，cacheh值应取大一些。否则会发生enq:sq-contention等待事件。

rac上创建sequence时，如果指定了cache大小同时赋予了noorder属性，则各节点将会把不同范围的sequence值cache到内存上。

create sequence TX_SEND_SEQ_ACC

minvalue 1

maxvalue 999999999999999999999999999

start with 673560

increment by 1

cache 20;

RAC1取序列

SQL>selecttx_send_seq_acc.nextvalfromdual;NEXTVAL----------673560SQL>selecttx_send_seq_acc.nextvalfromdual;NEXTVAL----------673561RAC2取序列SQL>selecttx_send_seq_acc.nextvalfromdual;NEXTVAL----------673580SQL>selecttx_send_seq_acc.nextvalfromdual;NEXTVAL----------673581

若两个节点之间都必须通过依次递增方式使用sequence，必须赋予如下的order属性

如果是已赋予了cache+order属性的sequence，oracle使用SV锁进行同步。SV锁争用问题发生时的解决方法与sq锁的情况相同，就是将cache 值进行适当调整。

在RAC多节点环境下，Sequence的Cache属性对性能的影响很大。应该尽量赋予cache+noorder属性，并要给予足够的cache值。如果需要保障顺序，必须赋予cache+order属性。但这时为了保障顺序，实例之间需要不断的交换数据。因此性能稍差。

oracle RAC环境sequence不一致问题

SequencesinOracle10gRACJustrecentlyIgotacallfromadeveloper.Hehadatablewithaprimarykeypopulatedbyasequence,atimestampcolumnwiththecurrentdateandsomeothercolumns.Hehadaspecificsetofdatathat,whenorderedbytheprimarykeyhadoutofordertimestamps.Hewaspuzzledhowthiscouldbe.ThisisaRACdatabaseandthesequencewascreatedwiththedefaultvalues.Notonlythesequencescachewasthedefaultof20,butitwas“noordered”.Being“noordered”Oraclewillnotguaranteetheorderinwhichnumbersaregenerated.Exampleof“noorder”sequencein10gRAC:Session1onnode-A:nextval->101Session2onnode-A:nextval->102Session1onnode-B:nextval->121Session1onnode-B:nextval->122Session1onnode-A:nextval->103Session1onnode-A:nextval->104Thesequencecacheisinthesharedpool,thereforesessionsonthesamenodecansharethecachedentry,butsessionsondifferentnodescannot.IwonderwhyOracledoesntmake“ordered”thedefaultforsequences.SoIexplainedtothedeveloperhowsequencesworkinRACandhoweachnodehasitsown“cache”.Wechangedthesequenceto“ordered”andincreasedthecacheto1000.Nowselectingoneithernodegetsthenextnumberasheexpected.Iwarnedhimthattherewouldbesomeperformanceimplicationsduetoclustersynchronization.Himbeenaresponsivedeveloper,askedmewhatwouldbetheimpact,soItesteditout.HowdoesRACsynchronizesequences?InOracle10gRAC,ifyouspecifythe“ordered”clauseforasequence,thenagloballockisallocatedbythenodewhenyouaccessthesequence.Thislockacquisitionhappensonlyatthefirstsequenceaccessforthenode(A),andsubsequentusesofthesequencedonotwaitonthislock.Ifanothernode(B)selectsfromthatsequence,itrequeststhesamegloballockandonceacquireditreturnsthesequencesnextvalue.Thewaiteventassociatedwiththisactivityisrecordedas"eventsinwaitclassOther"whenlookedingv$system_event.Somuchforeventgroups,itcouldn'tbemoreobscure.Thatviewshowsoverallstatisticsforthesession.Howeverifyoulookinthegv$session_wait_historyitshowsas“DFSlockhandle”withthe“p1″parameterbeentheobject_idofthesequence.Thissecondviewhasasampleofthelast10waiteventsforasession.InaSQL_TRACEwithwaitevents(10046trace)itwillbea"DFSlockhandle"butinAWRorstatspackreportsitwillbe“eventsinwaitclassOther”.Somuchforconsistency.Howdoesthatchangeourexample?Session1onnode-A:nextval->101(DFSLockhandle)(CRread)Session2onnode-A:nextval->102Session1onnode-B:nextval->103(DFSLockhandle)Session1onnode-B:nextval->104Session1onnode-A:nextval->105(DFSLockhandle)Session1onnode-A:nextval->106(moreselects)Session1onnode-A:nextval->998Session1onnode-B:nextval->999(DFSLockhandle)Session1onnode-B:nextval->1000(CRread)ThecachesizealsohassomeRACsynchronizationimplications.Whenthecachedentriesforthesequenceareexhausted,thesequenceobjectneedstobeupdated.ThisusuallycausesaremoteCR(currentread)overtheinterconnectfortheblockthathasthespecificsequenceobject.Soabitmoreactivityhere.Testcase:createsequencetest_rac;declaredummynumber;beginforiin1..50000loopselecttest_rac.nextvalintodummyfromdual;endloop;end;/Results:50000loopswithcache=20(default)1node=5seconds2nodesatsametime=14seconds2nodesatsametimeordered=30seconds50000loopswithcache=10001node=1.5seconds2nodesatsametime=1.8seconds2nodesatsametimeordered=20secondsWithasmallercache,the“noordered”stillhasassignificantimpactasevery10fetches(cache20dividedby2nodesfetching)ithastosynchronizebetweenthe2nodesTheconclusionBydefaultsequencesin10gRACarecreatedwithoutordering.BewareofusingapplicationsthatrelyonsequencestobeorderedandusingitinaRACenvironment.Considerchangingallusersequencesto“ordered”asaprecautionandincreasingthecachesize.Thedefaultcachevalueisstillverylowandevennot-orderedsequenceswillcausecontentioninahighly-activesequenceeveninnon-RACandcausinganadditionalblockexchangeevery20valuesinRAC.Forhighvolumeinsertoperationswhereorderingisnotperformedonthevaluereturnedfromthesequence,considerleavingthesequence“noordered”butincreasingthecachesizesignificantly.Eitherway,thesequenceparametersshouldbereviewed,aschancesare,thedefaultsarenotwhatyouneed.IrememberreadingsomewherethatinOracle9ithe“ordered”clauseinRACwasequivalentto“nochache”.Icantimaginehowbadthatwouldbeinconcurrentselectsfromthesamesequence.Itwouldbeinterestingifsomeonerunning9iRACperformsthetestcaseandIwouldappreciateifyouposttheresultsinthecomments.

下面是公司环境，看到对于频繁更新的表都是cache很大，并且都是默认的noorder，在rac中，cache+noorder

Select'createsequence'||Sequence_Name||'minvalue'||Min_Value||'maxvalue'||Max_Value||'startwith'||Last_Number||'incrementby'||Increment_By||'cache'||Cache_Size||';'FromDba_Sequences;createsequenceSEQ_CMS_ACCESSORYminvalue1maxvalue999999999999999startwith1incrementby1cache10000;createsequenceSEQ_CMS_CHANNELminvalue1maxvalue999999999999999startwith100606incrementby1cache10000;createsequenceSEQ_CMS_IMAGEminvalue1maxvalue999999999999999startwith260430incrementby1cache10000;createsequenceSEQ_CMS_INFOminvalue1maxvalue999999999999999startwith671134incrementby1cache10000;createsequenceSEQ_CMS_INFO_CHANNEL_LINKminvalue1maxvalue999999999999999startwith210007incrementby1cache10000;createsequenceSEQ_CMS_INFO_PROPminvalue1maxvalue999999999999999startwith60001incrementby1cache10000;createsequenceSEQ_CMS_INFO_PROP_CONFIGminvalue1maxvalue999999999999999startwith50003incrementby1cache10000;createsequenceSEQ_CMS_INFO_USER_LINKminvalue1maxvalue999999999999999startwith460003incrementby1cache10000;createsequenceSEQ_CMS_LONG_TEXTminvalue1maxvalue999999999999999startwith681286incrementby1cache10000;createsequenceSEQ_CMS_TEMPLATEminvalue1maxvalue999999999999999startwith20187incrementby1cache10000;createsequenceSEQ_CMS_TEMPLATE_VIEW_VERSIONminvalue1maxvalue999999999999999startwith44incrementby1cache10000;createsequenceSEQ_CMS_WEBSITEminvalue1maxvalue999999999999999startwith40001incrementby1cache10000;createsequenceSEQ_CMS_WEBSITE_DOMAINminvalue1maxvalue999999999999999startwith100incrementby1cache10000;createsequenceSEQ_SCH_DISTRIBUTE_TASK_EXEC_Cminvalue1maxvalue999999999999999startwith181incrementby1cache2