session_cached_cursors

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 --

3510875 wrote:

 

Dear Experts,

 

Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?

 

Thank you

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

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.

Regards

Jonathan Lewis

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_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?

 

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.

Regards

Jonathan Lewis

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 times

3)If we want to reduce the parse calls,that should be done from application end

 

Thank 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.

Regards

Jonathan Lewis