SQL(plus) performance with sequence

Question

Hi All,

I have to generate some million update from some table, to update themselves. I had just recently learned about parallel(tablename,threads) which really improved the performance in PLSQL developer when I had run something like this:

select /* + parallel(table1,100) parallel(table2,100) */
       'update table1 set id = 1 where ... and id = '||table1.id||' ...
where ...

(I'm doing it like this, because it gives me the backup data in the update. And my BOSS told me to do it like this ;-))

So it really worked fast when using a static number in the set id = 1 part and in PLSQL Developer.

But then I wrote it to a file, and inserted a create sequence before, and tried to use the sequence as follows:

create sequence myseq
 start with   4200000
 increment by 1
 maxvalue     11200000;


select /* + parallel(table1,100) parallel(table2,100) */
       'update table1 set id = '||myseq.nextval||' where ... and id = '||table1.id||' ...
where ...

But now it's terribly slow. And I don't know why. I just rewrote the update generator, to insert static data in there, and used awk to replace that with a sequence of numbers, but could someone explain what had caused this (and can I do something about it)?

Thanks in advance!

Answer 1

Sequences can be quite a bottleneck, especially when multiple sessions/threads are using them at once.

The main thing you can do to improve this is to increase the number of sequence values that are cached: ALTER SEQUENCE CACHE n, replacing n with a value greater than 20 (the default). I would suggest setting it much higher since you will be using many or all of the sequence values in one statement. You can go as high as (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT).