Updating incremental values in a plsql cursor

Part of investigating a bind variable SQL regression problem with the report required a SQL trace.

The report was instrumented with: The tracing made the report run over six times longer.

Using a similar PL/SQL test block but with only 100,000 executions on a lab database showed the following results measuring CPU time (in seconds): Hence we can see that with even an extremely high CACHE setting for the sequence, the 10046 trace adds roughly 300% to 400% overhead for this one particular statement.

And that the caching sweet-spot seems to be around 100.

5000000 loop x := s.nextval; end loop; end; END OF STMT PARSE #140264395012488:c=0,e=256,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1418680119565401 ===================== PARSING IN CURSOR #140264395008592 len=26 dep=1 uid=74 oct=3 lid=74 tim=1418680119565686 hv=575612948 ad='a541eed8' sqlid='0k4rn80j4ya0n' Select S.

NEXTVAL from dual END OF STMT PARSE #140264395008592:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3499163060,tim=1418680119565685 EXEC #140264395008592:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3499163060,tim=1418680119565807 ===================== PARSING IN CURSOR #140264395000552 len=129 dep=2 uid=0 oct=6 lid=0 tim=1418680119566005 hv=2635489469 ad='a575c3a0' sqlid='4m7m0t6fjcs5x' update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 END OF STMT PARSE #140264395000552:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1935744642,tim=1418680119566003 BINDS #140264395000552: Bind#0 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=a52eb120 bln=22 avl=02 flg=09 value=1 Bind#1 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=a52eb132 bln=22 avl=02 flg=09 value=1 Bind#2 oacdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=a52eb144 bln=22 avl=15 flg=09 value=9999999999999999999999999999 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7f91d96ca6b0 bln=22 avl=01 flg=05 value=0 Bind#4 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7f91d96ca6c8 bln=22 avl=01 flg=01 value=0 Bind#5 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=a52eb156 bln=22 avl=02 flg=09 value=20 Bind#6 oacdty=02 mxl=22(05) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=a52eb168 bln=22 avl=05 flg=09 value=5000021 Bind#7 oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0 kxsbbbfp=a52eb17a bln=32 avl=32 flg=09 value="--------------------------------" Bind#8 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7f91d96ca668 bln=22 avl=02 flg=05 value=8 Bind#9 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7f91d96ca680 bln=22 avl=04 flg=01 value=86696 EXEC #140264395000552:c=1000,e=798,p=0,cr=1,cu=2,mis=0,r=1,dep=2,og=4,plh=1935744642,tim=1418680119566897 STAT #140264395000552 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE SEQ$ (cr=1 pr=0 pw=0 time=233 us)' STAT #140264395000552 id=2 cnt=1 pid=1 pos=1 obj=79 op='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=23 us cost=0 size=69 card=1)' CLOSE #140264395000552:c=0,e=3,dep=2,type=3,tim=1418680119567042 FETCH #140264395008592:c=1000,e=1319,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=1,plh=3499163060,tim=1418680119567178 STAT #140264395008592 id=1 cnt=1 pid=0 pos=1 obj=86696 op='SEQUENCE S (cr=1 pr=0 pw=0 time=1328 us)' STAT #140264395008592 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)' CLOSE #140264395008592:c=0,e=1,dep=1,type=3,tim=1418680119567330 EXEC #140264395008592:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3499163060,tim=1418680119567378 FETCH #140264395008592:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=3499163060,tim=1418680119567425 CLOSE #140264395008592:c=0,e=1,dep=1,type=3,tim=1418680119567458 ...

Therefore no sequence values were being cached and an update to SEQ$ was necessary after every single NEXTVAL call. Caching sequence values adds the risk of skipped values (or a sequence gap due to the loss of the cached values) when the instance crashes.

(Note, no sequence values are lost when the database is shutdown cleanly.) However in this case, since the sequence is just being used as a surrogate key this was not a problem for the application.

The question was then: could this really be due to the overhead of tracing or something else?(Similar results to the 8.47 times increase we saw with the actual production database report execution.) And no surprise, the resulting trace file is extremely large.As we would expect, since the sequence was created with the default CACHE value of 20 it’s recording each UPDATE with the set of binds followed by 20 NEXTVAL executions: ===================== PARSING IN CURSOR #140264395012488 len=100 dep=0 uid=74 oct=47 lid=74 tim=1418680119565405 hv=152407152 ad='a52802e0' sqlid='dpymsgc4jb33h' declare x integer; begin for i in 1 ..Investigating the original report problem required an AWR analysis and a SQL trace (actually a 10046 level 12 trace – tracing the bind variables was of critical importance in troubleshooting the initial problem with the report).SELECTing a sequence value using the NEXTVAL function is supposed to be a fairly lightweight process.

Leave a Reply

  1. professional on line dating uk 07-Mar-2020 03:35

    All of the gay webcams found on Sexcamly are real people just like you, wanting to engage in free gay chat and more.

  2. drdating ru 25-Dec-2019 10:04

    To turn on this feature please click on the settings icon () in the chat message field to turn on "Tip Anonymously".