Sometimes, a developer or application user will report that a process that normally takes seconds to run is now taking several minutes and does not appear to be doing anything. In these situations, the problem is usually one of the following:

Space-related issue (e.g., the archive redo destination is full and has suspended all transactions).

A process has a lock on a table row and is not committing or rolling back, thus preventing another session from modifying the same row.

Oracle 23c has automated the process of aborting a low-priority transaction that holds a row lock and is blocking higher priority transactions. The priority is set to high, medium, or low for a user transaction. Users can also configure a maximum time a transaction will wait.

First check the alert log to see if there are any obvious issues that have occurred recently such as a wait on tablespace to extend. If there is nothing obvious in the alert log file, run a SQL query to look for locking issues.

SQL> set lines 80
SQL> col blkg_user form a10
SQL> col blkg_machine form a10
SQL> col blkg_sid form 99999999
SQL> col wait_user form a10

This situation is typical when applications do not explicitly issue a commit or rollback at appropriate times in the code. Oracle 23c does provide a way to prioritize the transactions to know how long a transaction will wait on the lock being released before aborting the lower-priority transaction.

You can also manually kill one of the sessions. Keep in mind that terminating a session may have unforeseen effects, and using the new features will allow for the transactions to roll back.

Resolving Open-Cursor Issues

The OPEN_CURSORS initialization parameter determines the maximum number of cursors a session can have open. This setting is per session. The default value of 50 is usually too low for any application. When an application exceeds the number of open cursors allowed, the following error is thrown:

ORA-01000: maximum open cursors exceeded

Usually, the prior error is encountered when

•     OPEN_CURSORS initialization parameter is set too low

•     Developers write code that does not close cursors properly

To investigate this issue, first determine the current setting of the parameter:

SQL> show parameter open_cursors;

If the value is less than 300, consider setting it higher. It is typical to set this value to 1,000 for busy OLTP systems. You can dynamically modify the value while your database is open, as shown here:

SQL> alter system set open_cursors=1000;

If you are using an spfile, consider making the change both in memory and in the spfile, at the same time:

SQL> alter system set open_cursors=1000 scope=both;

After setting OPEN_CURSORS to a higher value, if the application continues to exceed the maximum value, you probably have an issue with code that is not properly closing cursors.

If you work in an environment that has thousands of connections to the database, you may want to view only the top cursor-consuming sessions. The following query uses an inline view and the pseudocolumn ROWNUM to display the top 20 values:

SQL> select * from (select a.value, c.username, c.machine, c.sid, c.serial# from v$sesstat  a,v$statname b ,v$session     cwhere a.statistic# = b.statistic#and     c.sid and b.name and  a.value
= a.sid= ‘opened cursors current’ != 0and     c.username IS NOT NULL order by 1 desc,2) where rownum < 21;

If a single session has more than 1,000 open cursors, then the code is probably written such that the cursors are not closing. When the limit is reached, somebody should inspect the application code to determine whether a cursor is not being closed.

Tip It is recommended that you query V$SESSION instead of V$OPEN_CURSOR to determine the number of open cursors. V$SESSION provides a more accurate count of the cursors currently open.