on 2011 Oct 03 8:19 AM
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? ...no, wait, NOW it's faster, after running it fifty times!"
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.
HTH
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
As an answer to the question http://sqlanywhere-forum.sap.com/questions/782/connection-pooling-and-connection-parameters 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.
@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 🙂
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, ado.net, 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.
User | Count |
---|---|
71 | |
11 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.