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