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;
Give a name to the trace file that will be generated
SQL> alter session set tracefile_identifier=’new11g_seq’;
Set the Event No and Level till I want to trace
SQL> alter session set events ’10046 trace name context forever,level 12′;
Run one PL/SQL involving the new sequence as a PL/SQL expression
2 l_no number;
4 l_no := new11g_seq.nextval;
PL/SQL procedure successfully completed.
Set the tracing off:
SQL> alter session set events ’10046 trace name context off’;
Checked the destination of the trace file:
SQL> select value from v$diag_info where name = ‘Default Trace File’;
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′
l_no := new11g_seq.nextval;
END OF STMT
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
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!