oracle cursor

Oracle will generate something called cursor after it parse an statement. This thing--cursor will be stored in Library cache, if next time you need to execute the same statement, you can use that cursor and this will reduce the work oracle need to do. But cursor will consume resources like memory space. So if one session open a lot cursors -- one million for example. This may exhausting the memory space of entire DB. So oracle introduce a initialization parameter called open_cursors. This parameter controls the upper limit for how many cursors a session can open concurrently.

Frustratingly for developers, the session statistic ‘currently open cursors‘ can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable". The cursor may not actually be closed until Oracle needs the space for another cursor.

So it‘s not possible to test to see if a complex application is closing all its cursors by starting a session, running a test, and then checking to see if currently open cursors has gone down to 1. Even if the application is closing all its cursors properly, currently open cursors may report that some "closeable" cursors are still open.

But we still have to use this inaccurate method because there seems  no more accurate way.

oracle cursor,布布扣,bubuko.com