HPE Community read-only access December 15, 2018
This is a maintenance upgrade. You will be able to read articles and posts, but not post or reply.
Dec 15, 4:00 am to 10:00 am UTC
Dec 14, 10:00 pm CST to Dec 15, 4:00 am CST
Dec 14, 8:00 pm PST to Dec 15, 2:00 am PST
Showing results for 
Search instead for 
Did you mean: 

select query hangs .


select query hangs .

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
Volker Borowski
Honored Contributor

Re: select query hangs .


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?