On 12th july 2007, I wrote a post about new feature of 11g related to sequence.

In this post I mention that in 11g we don’t need to use dual table to fetch next value of a sequence and using simple PL/SQL expression the next value can be fetched.

Any way thats the old story and lot of people have wrote about it.

But recently I got to know from Asif blog, what exactly is happening in background.

I follows what he did and confirms that he 100% right!

Created the new sequence

SQL> create sequence new11g_seq;

Sequence created.

Give a name to the trace file that will be generated

SQL> alter session set tracefile_identifier=’new11g_seq’;

Session altered.

Set the Event No and Level till I want to trace

SQL> alter session set events ‘10046 trace name context forever,level 12′;

Session altered.

Run one PL/SQL involving the new sequence as a PL/SQL expression

SQL> declare
2 l_no number;
3 begin
4 l_no := new11g_seq.nextval;
5 end;
6 /

PL/SQL procedure successfully completed.

Set the tracing off:

SQL> alter session set events ‘10046 trace name context off’;

Session altered.

Checked the destination of the trace file:

SQL> select value from v$diag_info where name = ‘Default Trace File’;

VALUE
————————————————————————-

d:\oracle\diag\rdbms\dcs\dcs\trace\dcs_ora_4084_new11g_seq.trc

Now I opened the given trace file and find some interesting fact in following lines:

=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=5 oct=47 lid=5 tim=12331650243 hv=2968413633 ad=’1e1874a8′ sqlid=’b5v90tqsfwtf1′
declare
l_no number;
begin
l_no := new11g_seq.nextval;
end;
END OF STMT
PARSE #3:c=31250,e=81603,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=12331650236
BINDS #3:
=====================
PARSING IN CURSOR #5 len=35 dep=1 uid=5 oct=3 lid=5 tim=12331651486 hv=3823849384 ad=’1e18717c’ sqlid=’9fhs7ymjyqmx8′
Select NEW11G_SEQ.NEXTVAL from dual
END OF STMT
PARSE #5:c=0,e=989,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=12331651479
BINDS #5:
EXEC #5:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=12331651654
=====================

Wow!

It means even though oracle has made life easier for programmers as we don’t need to write whole SQL to fetch new sequence number, but from performance point of view and internal process of fetching the sequence nothing has changed

It still converts the PL/SQL expression to SQL and fetch the next value!