OPEN_CURSORS in Oracle


Open_Cursor Parameter , in this article we will discuss what this parameter do  , I used Oracle Documentation to describe this parameter but in simpler way .

In Oracle Documentation

Open_cursor : specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

to check in database

    SQL> show parameter Open_cursor

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    open_cursors                         integer     300
    

As we see in the last Line , "its prevent Session from opening excessive number of cursor"  how is that !!!

Open_cursor located in shared_pool which is part of SGA ( library Cache) , The benefit of this parameter is to prevent Session clogging CPU with requests .

But what i mean when I set this Parameter to integer , let take the above example :

-Parameter is set to 300
-That mean each session can have  300 cursors

If this session fill the 300 What will happened !!!

     "ora-1000 maximum open cursors exceeded"

On Documentation Oracle Recommended to set this Parameter for high value  for Application usage , if the above error raised and you already set to high Value then make sure your developer is closing their cursor and this is common issue.

    SQL> Show parameter cursor

    NAME                                      TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing                             string      EXACT
    cursor_space_for_time                boolean     FALSE
    open_cursors                               integer     300
    session_cached_cursors               integer     20


But what others Parameter mean,  what each parameter mean lets ask this question what if User or session run query include the same cursor , is it take another space in memory !!!

it dose not make sense , so in this case we use SESSION_CACHED_CURSOR.

regarding to oracle documentation

SESSION_CACHED_CURSOR : its Number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. 

you have to know some truth about this parameter :
1) its cache only close cursor which mean if you cursor still opened how could it be cached !!!

2) you can set this parameter > OPEN_CURSOR , < OPEN_CURSOR Or equal OPEN_CUROSR , that indicate us between relation between this parameter ----> NOTHING.

3) if you are not setting this parameter ( Value 0 ) then no cursor will be cached for session . But Wait we said oracle used shared pool in cursor Yes , your cursor will be in SHARED_POOL but session have to find this cursor , so if you set to non zero first thing oracle will do is check SHARED_POOL ( Library cache ) and back to you with result .


After discuss point number 3 we see the main advantage for cache cursor which is better performance , enhance query execution time.

But the shared_pool has limited size so i can't cache all my cursor , now next parameter will avoid this

CURSOR_SPACE_FOR_TIME

Boolean value (TURE|FALSE) each one of them mean something

FALSE : Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.

TRUE : Shared SQL areas are kept pinned in the shared pool , if you are using application contain lot of cursor it will be good to set it to enhance execution time ( to do that shared pool must be large enough to hold all open cursors simultaneously) .


If we have 3 query each one want to use same cursor how oracle handle this , Parameter number 4 will control that.

CURSOR_SHARING

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

this parameter take three value ( FORCE | SIMILAR | EXACT ) i will describe each one of them , Oracle Documentation definition : 

-Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

-Similar Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

-Exact Only allows statements with identical text to share the same cursor.

you can't imagine how many document you will find if you search on google for this parameter , examples , article and tuning for it.

to set this parameter you have to make sure what you need , understand it.the best way to do that with trying to test it.

Thanks
NJ