This discussion is archived
10 Replies Latest reply: Jan 27, 2013 6:08 PM by Hemant K Chitale RSS

I need to generate 5000 numbers in a second using oracle sequence

986905 Newbie
Currently Being Moderated
Hi,
I need to generate 5000 numbers in a second using oracle sequence; Is it possible? If not, what is the maximum number that can be generated?

Thanks
Venkatesh
  • 1. Re: I need to generate 5000 numbers in a second using oracle sequence
    karan Pro
    Currently Being Moderated
    Can you drive your car at 100 KM/s in traffic ?? Can you drive it at 300 Km/s without traffic ? How can you say that 5000 sequence numbers can be generated when there is already load on dictionary cache... If there is no activity what speed are you getting ? And what do you mean by GENERATE here?? Do you mean caching ? If you do you may be right because caching of sequences does help to speed up DML's and is especially important for high-DML applications with lots on insert and update activity. If there are performance problems due to sequence cache waits, examine the row cache locks statistics in the v$system_event view to determine whether the problem is due to the use of Oracle sequences. In oracle RAC, sequence enqueue delays are shown in the eq_type column of the gv$enqueue_stat view. A value of "SQ Enqueue" indicates that there is contention for sequences.

    Regards
    Karan
  • 2. Re: I need to generate 5000 numbers in a second using oracle sequence
    986905 Newbie
    Currently Being Moderated
    Thanks for the quick reply Karan.

    I have not started on anything; I have a requirement to generate 5000 IDs using oracle sequence; before accepting that requirement, I wanted to know if it is a real possibility - if not, is there a maximum number of sequence be created within a time frame with or without load?

    Thanks
    Venkatesh
  • 3. Re: I need to generate 5000 numbers in a second using oracle sequence
    karan Pro
    Currently Being Moderated
    Set CACHE to 5000 in your case and you would be ok as long as you dont see contention problems in v$ performance view like i said above.
  • 4. Re: I need to generate 5000 numbers in a second using oracle sequence
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    983902 wrote:

    I have not started on anything; I have a requirement to generate 5000 IDs using oracle sequence; before accepting that requirement, I wanted to know if it is a real possibility - if not, is there a maximum number of sequence be created within a time frame with or without load?
    It's posisble.
    SQL> create sequence s1 cache 10000;
    
    Sequence created.
    
    Elapsed: 00:00:00.03
    SQL>
    SQL> set autotrace traceonly statistics
    SQL>
    SQL> with generator as (
      2     select
      3             rownum id
      4     from
      5             dual
      6     connect by
      7             level <= 5000
      8  )
      9  select s1.nextval from generator
     10  ;
    
    5000 rows selected.
    
    Elapsed: 00:00:00.06
    
    Statistics
    ----------------------------------------------------------
              3  recursive calls
              3  db block gets
              3  consistent gets
              0  physical reads
            780  redo size
          51268  bytes sent via SQL*Net to client
           2604  bytes received via SQL*Net from client
            201  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
           5000  rows processed
    That's 5,000 values generated and returned in 0.06 seconds.
    But whether or not you can write code that does it that quickly depends on your coding language, environment, and requirement.

    Regards
    Jonathan Lewis
  • 5. Re: I need to generate 5000 numbers in a second using oracle sequence
    EdStevens Guru
    Currently Being Moderated
    983902 wrote:
    Thanks for the quick reply Karan.

    I have not started on anything; I have a requirement to generate 5000 IDs using oracle sequence;
    That's not a requirement. That's a pre-conceived (and probably ill-conceived) technical solution to some as yet unknown business requirement. Whatever the business requirement really is, I'll bet that there is a far better technical solution than "generate 5000 IDs using oracle sequence"
    before accepting that requirement, I wanted to know if it is a real possibility - if not, is there a maximum number of sequence be created within a time frame with or without load?

    Thanks
    Venkatesh
  • 6. Re: I need to generate 5000 numbers in a second using oracle sequence
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I have a requirement to generate 5000 IDs using oracle sequence
    Why does it have to be a sequence ? Do you want to ensure that after the 5000 IDs, there will be more IDs and these must start at a value higher than 5000 ?

    Where do you want to "place" (i.e. save) these IDs ? In a table ? What other data must be saved along with each ID. For example, if you are generating 5000 Invoice IDs, you must also have information for 5000 Invoices. If the information on these 5000 invoices is already present in a table, you'd need to add an ID column and update 5000 rows.
    But simply "generating" 5000 IDs that stand by themselves "up in the air" doesn't make sense. You'd want to associate them with something. The business requirement has to be well defined.


    Hemant K Chitale
  • 7. Re: I need to generate 5000 numbers in a second using oracle sequence
    986905 Newbie
    Currently Being Moderated
    Hi,
    We want to support 5000 concurrent users; Every operations these users do will result in generating an unique id that gets into the relevant tables and that calls for this requirement of 5000 numbers to be generated from an oracle sequence; ID will formed with the timestamp and a number from the sequence.

    Thanks
    Venkatesh
  • 8. Re: I need to generate 5000 numbers in a second using oracle sequence
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    983902 wrote:
    We want to support 5000 concurrent users; Every operations these users do will result in generating an unique id that gets into the relevant tables and that calls for this requirement of 5000 numbers to be generated from an oracle sequence; ID will formed with the timestamp and a number from the sequence.
    There is a massive difference between 5,000 concurrent users and 5,000 active sessions. In a typical OLTP system you might expect a couple of seconds of compute time for a few minutes of end-user thing time - call it 1 to 100 as a fairly arbitrary, but reasonable ballpark, figure for CPU to User seconds. This would suggest 50 actual active sessions - with careful control of connection pooling at the mid-tier. (I am assuming the application has been designed properly with a good understanding of the technologies involved when I say this.)

    When you need your sequence values, you can do varius inefficient things, or a couple of efficient things. Two things that move you in the efficient direction (apart from the cache setting) are: (a) use one sequence for each table (and column) that needs a meaningless number (b) don't pull the sequence value to the client before inserting into the table.

    As others have indicated, if you want good advice you have to supply context not just simple mechanical requirements. At your stage of development, a request about simple mechanics could give you an answer that is unhelpful and counter-productive.

    Regards
    Jonathan Lewis
  • 9. Re: I need to generate 5000 numbers in a second using oracle sequence
    EdStevens Guru
    Currently Being Moderated
    983902 wrote:
    Hi,
    We want to support 5000 concurrent users; Every operations these users do will result in generating an unique id that gets into the relevant tables and that calls for this requirement of 5000 numbers to be generated from an oracle sequence; ID will formed with the timestamp and a number from the sequence.

    Thanks
    Venkatesh
    Confirming my suspicions that " generate 5000 numbers in a second using oracle sequence" was NOT a requirement, but an ill-conceived solution to a requirement ...
  • 10. Re: I need to generate 5000 numbers in a second using oracle sequence
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Every operations these users do will result in generating an unique id that gets into the relevant tables
    ID will formed with the timestamp and a number from the sequence
    How about multiple sequences ? A separate sequence for a different type of operation OR for a different target table. Do you need to maintain timestamp serialisation across all operations, target tables and sessions ?


    As Jonathan Lewis has pointed out, in a real-world scenario, 5000 sessions doesn't necessarily mean 5000 concurrent calls to the sequence.
    The number of calls per second could be significantly lower.


    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points