Why does my PARSE times always equal to EXECUTE times when I query data using the same sql statement with SQLPLUS? The initiation parameter OPEN_CURSORS equals to 100, is it too small? How can I tune it?
Any suggestions will be appreciated.
There is a relationship between the No pf parse call to Execute calls .We have to find the solution for 2 categories as mentioned below.
Identifying Unnecessary Parse Calls and Reducing Unnecessary Parse Calls should be done.
Increasing the open_cursors parameter can be based on
Find from v$sysstat or v$sessstat the name 'opened cursors cumulative' this can help you to query and increase the open_cursors parameter.
(or) if you find the following error
ORA-28554 pass-through SQL: out of cursors
Cause: The maximum number of open cursors has been exceeded.
Tuning Pvt SQL Area
1)By setting the below init parameters.
HOLD_CURSOR = yes
RELEASE_CURSOR = no
MAXOPENCURSORS = value
Tuning Shared SQL Area
If an application makes a parse call for a SQL statement and the parsed representation of the statement does not already exist in a shared SQL area in the library cache, Oracle parses the statement and allocates a shared SQL area. You may be able to reduce library cache misses on parse calls by ensuring that SQL statements can share a shared SQL area whenever possible.
If an application makes an execute call for a SQL statement and the shared SQL area containing the parsed representation of the statement has been deallocated from the library cache to make room for another statement, Oracle implicitly reparses the statement, allocates a new shared SQL area for it, and executes it. You may be able to reduce library cache misses on execution calls by allocating more memory to the library cache.
For more information look oracle doc.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it