Re: What's the meaning of OPEN_CURSOR?

In article <34C5668F.436E_at_comp.polyu.edu.hk>, Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:
>Hello all,
>
> I developed Form4.5 and find that the max. of open_cursor is reached.
>So I increased the value in the init.ora and works fine. But why my
>application is reaching the limit of open_cursor? What's is the meaning

It might mean you are using up cursors instead of re-using them. You may have similar SQL statements that could be replaced by identical SQL statements, using up less shared pool and requiring fewer cursors. Any time you access the database you implicitly declare a cursor - a private SQL area. Implicit cursors require twice as many requests from the database - the second to see if there were any errors.

Also, SQL statements are kept in the shared pool of the SGA, so if you use the same statements in different forms applications, you will increase performance.

>of open_cursor and why it works fine when I changed open_cursor to
>larger value? And also what is the unit of open_curosr? Is this mean
>memory? When I set to larger value, will the performance of Oracle (or
>UNIX) be affected?

Each cursor is a context area for your session. Open_cursors is the maximum context areas that each session can have. So, the performance of Oracle could be affected if sessions are using up to their maximum. If you are explicitly declaring cursors, you might group together the definitions to reduce network traffic, and group together the closes, so you don't forget to close them.

See you pl/sql users guide for lots more info about cursors.

jg

>
>Thanks,
>Jimmy

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!