Does using a sequence in a PL/SQL expression improve performance?

In versions of the Oracle Database prior to 11g Release 1, to populate a variable with the next value from a sequence you would have call the NEXTVAL function from within a SQL statement. From Oracle Database 11g Release 1 you can now use the NEXTVAL function in a PL/SQL expression.

So other than less typing, does using a sequence in this way improve performance?

The following examples are all based on the following sequence


and were developed using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release
Oracle SQL Developer

To recap, the pre 11g method was:

SELECT emp_seq.nextval
  INTO some_variable
  FROM dual;

and from 11g Release one you can use the following:

some_variable := emp_seq.nextval;

Better Performance?

Using the method described by Adrian Billington you will be able see if the 11g method improves performance.



   emp_id PLS_INTEGER;


   FOR i IN 1 .. 10000

      emp_id := emp_seq.NEXTVAL;   


   FOR i IN 1 .. 10000

      SELECT emp_seq.NEXTVAL AS nval
        INTO emp_id
        FROM dual;



SQL Trace is enabled and the new 11g method of using NEXTVAL via an expression is called 10000 times after which the pre 11g method of using a SQL statement is also called 10000 times. Note the alias of nval is used with the SQL statement method this will help us identify the SQL statement method.

Here is the output from the trace file. The first entry is for the new 11g method and the second entry is for pre 11g method (as proved by the use of the alias nval)


The trace file shows very little differences when using the new 11g method. Which is not surprising as the trace file also shows that “under the covers” Oracle has not changed the implementation for the new 11g method and NEXTVAL is still obtained via a SQL statement.


In this article I have shown the two methods that are available to populate a variable with the next value from a sequence and that there is no performance gains to be had by choosing one method over another.


Adrian Billington (

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.