For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
3510875 wrote:Dear Experts,Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?Thank you
3510875 wrote:
Dear Experts,
Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?
Thank you
Parse once & use many can be achieved by using BIND variables.
Is application 3-tier?
Does application utilize Connection Pooling?
user3510875, no. To parse once and execute many you need for the code to be written using bind variables rather than constants. That is if the application creates the SQL statement by concatenating strings together and passes the result to Oracle as a query, for example:
select * from part_master where part_no = '1234' followed by
select * from part_master where part_no = '5678' ....
then each of these is a unique statement to Oracle. What you want is to pass a statement that looks like
select * from part_master where part_no = :variable
then Oracle will hard parse this statement once and soft parse it each time a new value is received in the :variable.
- -
Every statement has to be parsed every time however what you want is one hard parse the first time the statement is submitted and soft parses from then on out.
HTH -- Mark D Powell --
Others have pointed out that "use bind variables" is a strategy for minimising the costs of parsing but your question allows a lot of room for interpretation, partly because the phrase "parse once execute many" doesn't define its terms clearly.
A parse call is a specific call made by the client code to the server - if your client issues a parse call you will see your session records a "parse".
However, the consequences of a parse call may (in simplfied terms) be one of:
a) create a cursor area in the SGA for the supplied SQL text, do the syntactic and semantic checks, then optimise and generate the plan
b) search the library cache for an existing cursor that could be used for the statement
c) use a state object that points directly to a suitable previously used cursor for the statement (avoiding the search).
If your client code has a statement that it has used in the recent past then a parse call will (by default) be following (b) - which is why you have had comments about "use bind variables, not literals" as the use of bind variables means you will probably keep reusing the same statement even though the bound values change.
If you have enabled the session cursor cache (which is enabled with a cache size of 50 by default) then a parse call will (after the 2nd or 3rd usage) result in the creation of a state object which means you will subsequently be following (c).
---
If your client code is written to issue a single parse call and then hold the cursor for subsequent executions it will (in effect) be following a slightly more efficient version of (c) - bypassing some of the peripheral activitiy associated with a parse call.
In fact you may find that some DRIVERS have a cunning client-side cache which holds cursors even when the client code thinks it has closed the cursors, so you may find some cases where the client code appears to be issuing repeated parse calls, but those calls never reach the server.
Coincidentally (though perhaps not) Cary Millsap has just tweeted out a link to an article he's written about bind variables vs. parse calls: https://www.quora.com/How-do-you-fix-a-non-bind-variable-usage-in-Oracle/answer/Cary-Millsap?srid=uKTz
Regards
Jonathan Lewis
Thanks for all the updates.
Dear Jonathan,
Confused with above statement.what kind of activity associated with parse call is avoided?
upto my knowledge:
1)syntactic and semantic checks are avoided with session_cached_cursors...
2)searching the library cache for the sql is avoided using session_cached_cursors
Will oracle takes any latches with session_cached_cursors?If yes for what activities it may take the latches for?
what kind of activity associated with parse call is avoided?
3510875 wrote:Thanks for all the updates.Dear Jonathan,If your client code is written to issue a single parse call and then hold the cursor for subsequent executions it will (in effect) be following a slightly more efficient version of (c) - bypassing some of the peripheral activitiy associated with a parse call.Confused with above statement.what kind of activity associated with parse call is avoided?Thank you
The only one I can think of right now is adaptive cursor sharing - if you continually issue parse calls then the code path for adaptive cursor sharing becomes available.
3510875 wrote:upto my knowledge:1)syntactic and semantic checks are avoided with session_cached_cursors...2)searching the library cache for the sql is avoided using session_cached_cursorsWill oracle takes any latches with session_cached_cursors?If yes for what activities it may take the latches for?what kind of activity associated with parse call is avoided?Thank you
Second question answered in response to the previous post.
First queston - most of the library cache activity is now done on mutexes rather than latches so it's very hard to see any difference in latching when the session cursor cache is in place. There is probably some (insignificant) effect due to the way that Oracle precreates arrays of spare library cache locks and library cache pins and then allows sessions to reserve a limited number of them that might become observable at high levels of concurrency when the number of different statements every session keeps calling is larger than the session_cached_cursors and the number of concurrent sessions is high. (In other words if the effect does exist then very few people are likely to see it.)
If you want to understand more of how this type of stuff works then you need to get a copy of Oracle Core: https://www.amazon.co.uk/Oracle-Core-Essential-Internals-Developers/dp/1430239549 - but bear in mind that Oracle Corp. has had nearly 8 years to change the code since I wrote the book.
Thanks for the update.
Please correct us if our understanding is wrong:
1)We cannot avoid the parse calls with session_cached_cursors.
2)The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times
3)If we want to reduce the parse calls,that should be done from application end
3510875 wrote:Thanks for the update.Please correct us if our understanding is wrong:1)We cannot avoid the parse calls with session_cached_cursors.2)The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times3)If we want to reduce the parse calls,that should be done from application endThank you
That is a reasonable summary.
Two points - both about item (2)
a) session cached cursors also reduce the scope for losing time to contention due to concurrent activity
b) there are times when it is important to be very fussy about the choice of words "execution" is what happens after parsing - an expression like "overall response time" would have avoided any ambiguity.