select query hangs .

[kumasudh]

Hi ,

I am facing some issues with my Oracle9i database. There are few instances when select query hangs

select SMSDBA.NOTIF_ID.NEXT_VAL() from dual;

here NOTIF_ID is reaching 1968635007 . Can somebody help me to stat that this big sequence number is causing this "select" query to hang ? Could this lead to a deadlock ?

Thanks in advance for you help.

Best Regards
Sudhir

[Volker Borowski]

Hi,

could have to do with CACHE oder ORDER atrribute of the sequence.

Check DBA_SEQUENCES to find out the actual values.

If you do not need to rely on both, it could
help to use unordered sequence and a a higher
value of numbers cached.

If you need the exact sequence in your data,
it becomes difficult. You could only try to speed up the transaction that gets the sequence then, i.e. by committing earlier.
But this could cause other problems depending
on how the sequence is used inside the application.

Just to clearify: Is this a RAC installation?

Volker