Skip to Main Content
  • Questions
  • Do tons of sequences create any kind of problems?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Robert.

Asked: August 04, 2016 - 5:00 pm UTC

Last updated: August 11, 2016 - 6:50 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Do sequences take up much memory or cause any other trouble if you end up with 10s of thousands of them?

=========================
Unnecessary details:

We have to design a service to generate unique IDs.
We can do to a couple ways -
One involving logic and storing values in a table
One involving no logic but possibly thousands of sequences.


Unique ID structure comprised of 4 parts:

-- Product Code
-- Customer Code
-- Year
-- Numeric sequence unique to each Product Code, Customer Code, Year

e.g., IDs for two of the sequences would be:

GGEU88-T111-2016-0001 --> GGEU88-T111-2016-9999
VTBB98-T111-2016-0001 --> VTBB98-T111-2016-9999

Options as I see it:

A) A table with 4 columns outlined above, and lock the table each time to get the next value for the 4th column
OR
B) Dynamically create and then use Oracle sequence objects with names like:
GGEU88_T111_2016
VTBB98_T111_2016


and Connor said...

You only need 1 sequence surely ?

Then the unique ID is the concatenation of product, customer, year, sequence

As long as the sequence is defined as NOCYCLE, then every ID will be unique

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up question

Robert Barry, August 08, 2016 - 8:17 pm UTC

You are correct. However I omitted some design details in my original post. Added at the bottom of this response. But to clarify my question...

Other than bookkeeping, are there any downsides related to memory, performance, [other] if a system is going to have thousands of sequences? I feel the answer is probably "NO" but just wanted to pose it to you guys since I can't find a definitive answer in documentation.
==========================================================

Based on client requirements, sequence segment of the ID number is only going to be 4 digits and were we to share a single sequence, those numbers would go above 9999.

Also - though some gaps are allowable, the client is expecting to have the numbers stay in order and grouped for any ID values that share [product code] and [customer code].

Connor McDonald
August 09, 2016 - 3:00 am UTC

mod(seq.nextval,9999)+1

If you don't know answer that is fine and I will stop asking the same question

Robert Barry, August 10, 2016 - 3:20 pm UTC

...but I will ask it one more time because it would be nice to know...

Original question:

Do sequences take up much memory or cause any other trouble if you end up with 10s of thousands of them?
Chris Saxon
August 11, 2016 - 6:50 am UTC

They'll use up some dictionary cache, and it would follow that 10,000+ sequences will lead to 10,000+ (or more) different SQL statements that use them. That could have a hefty impact on parsing.

So you could test for impact by keeping an eye on the parsing stats in AWR reports and the like. If it hurts, then consider a home-grown solution that mimics sequences but is customised for you eg,

SQL> create table my_sequences (
  2    prod varchar2(10),
  3    cust varchar2(10),
  4    year int,
  5    seq  int ,
  6    constraint my_sequences_pk primary key (prod,cust,year,seq)
  7    )
  8  organization index;

Table created.

SQL>
SQL>
SQL>
SQL> create or replace
  2  function  seqval(p_prod varchar2,p_cust varchar2,p_year int) return int is
  3    l_seq int;
  4  begin
  5    update my_Sequences
  6    set    seq = seq + 1
  7    where  prod = p_prod
  8    and    cust = p_cust
  9    and    year = p_year
 10    returning seq into l_seq;
 11
 12    if sql%notfound then
 13      insert into my_sequences (prod,cust,year,seq)
 14      values (p_prod,p_cust,p_year,0)
 15      returning seq into l_seq;
 16    end if;
 17
 18    return l_seq;
 19  end;
 20  /

Function created.

SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line(seqval('P1','C1',2016));
0

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(seqval('P1','C1',2016));
1

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(seqval('P1','C1',2016));
2

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_output.put_line(seqval('P1','C2',2016));
0

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(seqval('P1','C2',2016));
1

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(seqval('P1','C2',2016));
2

PL/SQL procedure successfully completed.

SQL>
SQL> select * from my_sequences;

PROD       CUST             YEAR        SEQ
---------- ---------- ---------- ----------
P1         C1               2016          2
P1         C2               2016          2

2 rows selected.

SQL>
SQL>



More threats

Jonathan Lewis, August 14, 2016 - 2:04 pm UTC

Apart from the pure memory (which would be pretty small), there are many other aspects to the problem of large numbers of (dynamically generated) sequences.

At the end (or maybe start) of each year you have to create thousands of new sequences; when you add a new product you have to create a sequence for every combination of customer and current year; when you add a new customer you have to create a sequence for every combination of product and current year.

You're using any one sequence roughly once per hour on average (9,999 values, 365 days) - the chances of a sequence still being cached when you want it are minimal, and the chances of the SQL that accessed being in the library cache are minimal, so every sequence use would probably require dictionary cache latching (on the dc_sequences latch, and the dc_objects latch), as well as library cache latching, following by a couple of SQL statement executions to identify and load the sequence.

Given the requirement you've stated a single table - created as an index organized table, with compression on two columns out of three ((year, cust_id, prod_id) as the PK) is the obvious implementation to pre-empt all sorts of contention threats. You might consider (year, prod_id, cust_id) if you have a selection of products that many of your customers frequently order.

Regards
Jonathan Lewis


Good answers both - thanks

Robert Barry, August 18, 2016 - 7:27 pm UTC

Thanks for the ideas and feedback.

I was taking the question to an extreme and had to change my actual first and second columns to something generic (not actually cust_id and prod_id).

At this point it looks like we'd need 144 sequences per year.
With the idea I had in mind - a function would take three parameters - then concatenate them - then attempt to pull NEXVAL from the sequence with a name matching the concatenated strings. If error because it didn't exist, then it would create a sequence, then get the first value from it. So the maintenance would be low.

I wanted to explore the option above but at this point it looks like we are going in another direction:
table with 4 columns - first three for the "prod_id", "cust_id","fy" and then a 4th number(4) column to store the numeric values that will represent the sequential part of the entire unique ID.

Thanks again.
Bob