The 12.0.1 Help on Plan caching says "The plan cache is a per-connection cache of the data structures used to execute an access plan."

However, observed behavior would indicate that "per-connection" may not apply when the separate connections are different invocations of the same web service... that a Very Bad Plan cached for a Query From Hell inside a web service stored procedure can persist (for a while, long enough to confuse(1) the observer :) across multiple invocations of the web service.

I suspect the details are complex and hard to explain for us Deltas (ok, Gammas :) but any attempt to do so would be welcome!

Footnote (1): An example of "confuse the observer" is "How come the new clustered index makes my plan look a million times faster in the ISQL Plan Viewer, but my web service is still running slow?, wait, NOW it's faster, after running it fifty times!"

In a comment on his answer to a related question Mark Culp wrote this: "We have discussed (and have experimented with) cross connection plan caching but it is hard to get it right so this feature has not (yet) made it into the released product."

asked 03 Oct '11, 07:19

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 05 Oct '11, 16:41

I added the "insistent-question" tag for that matter - though apparently not that successful...and references to Alpha Pluses don't seem to help, either(:

(05 Oct '11, 17:42) Volker Barth

If I remember correctly in SA 12 we introduced connection pooling for web service requests. This means that the database connection is put back into a pool when the web service request completes. If a new web service request arrives for the database within a short period of time (30 seconds I think) then the database connection will be pulled out of the pool and used to handle the request. This pooling technique saves the time to create and tear down the database connection ... but the new web service request must still go through the same parsing, mapping, and authentication process.

Now here's the rub: to ensure that there is no leakage of information from one request to the next, when a connection goes into the pool the connection is "cleaned" by closing all cursors, dropping all temp tables and connection variables, and a few other things (that I can't remember right now). If I remember correctly we initially tried to keep the plan cache intact between requests... but we ran into some issues doing this so we ended up dropping the plan cache as well. The plan cache is kept when the connection is put into the web services connection pool... but an additional rule applies when the connection is reused: When picking a connection to reuse out of the web services connection pool only previous connections that belonged to the same user as the user that is to run the new web service request are considered for reuse. This restriction ensures there is no leakage of information via the plan cache from one request to the next.

Net result is that the plan cache is not maintained across web requests for normal web request.

I say "normal" web requests because if you are using HTTP sessions then everything that I have said above does not apply. When you use HTTP sessions the database connection used to handle the web request is maintained completely intact between web requests - the connection is associated with the HTTP session and will only be used to handle requests associated with the session and therefore there is no concern about information leakage between requests (since that is exactly why you would use sessions - to keep information on the connection between requests) and therefore the no cleaning is performed.

So how do you solve the "I have a bad cached plan; how do I get rid of it?"? You have two methods:

  • The first is to delete the web service connection pool. To do this you need to temporarily disable connection pooling by setting the http_connection_pool_basesize option to zero, wait a minute (or two) and then set it back to a non-zero value (or just leave it as zero).

  • The second is to set max_plans_cache option to zero within the web service request itself. This likely requires you to modify you web service procedures.

The first solution is likely easiest to do on a running system.


permanent link

answered 05 Oct '11, 20:06

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

edited 06 Oct '11, 09:38

If I remember correctly in SA 12 we introduced ...

Obviously you happen to work on the next few releases, so what us users call a new version is rather old in the developer's view:)

(06 Oct '11, 02:41) Volker Barth
Replies hidden

As an answer to the question it is said, that the complete connection string must match for a pooled connection to be reused. This means that also the AppInfo parameter is considered which can already be different if the calling thread is not the same. Which can not be quaranteed if called from inside a webservice.

(06 Oct '11, 05:36) Martin
Replies hidden

@Mark: Sadly, in this case, the HTTP SessionID option is not being used, so the observed behaviour (a Very Bad Plan being used) must have some other explanation... and (sigh) a more difficult solution because the engine must be picking the Very Bad Plan all on its own. This is confirmed by setting max_plans_cached = 0 having no effect, and neither does OPTION ( FORCE OPTIMIZATION ). Alas, although procedure profiling points directly to the Query From Hell, Database Tracing seems to ignore it most of the time; i.e., the Query From Hell will be executed a dozen times but only show up once in the tracing database... or not at all. So I'm on my own using dbisql and copy and paste to get the Graphical Plan... but at least procedure profiling found the query for me, for that I am grateful. This ... stuff ... takes ... so ... long ... to ... do :)

(06 Oct '11, 06:42) Breck Carter
Replies hidden

Ah yes, it was too long ago!

(06 Oct '11, 09:04) Mark Culp

The question that you refer to is talking about connection pooling when connecting via a client using traditional TCP/IP connection (i.e. dblib, odbc,, etc). This question is asking about web services connections which is done via HTTP or HTTPS. The pools used in each are different and have different behaviour.

(06 Oct '11, 09:07) Mark Culp

Yes, I did not remember the full story... I will revise my answer.

(06 Oct '11, 09:08) Mark Culp
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 03 Oct '11, 07:19

question was seen: 5,141 times

last updated: 06 Oct '11, 09:38