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.
Printable View
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.
EXECUTE TIME or elapse time , I think parse time is no relationship with execute time
http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1137834554916,{soft}%20and%20{parse}
basically sqlplus doesnt use cache cursors and causes soft parse everytime u execute the same query
thanks pando for ur help and for the link ...
pras
Hi, 11th April 2001 12:03 hrs chennai
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
================
Parse
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.
Execute
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.
Cheers
Padmam