Initial error in call to procedure is cached

Hi Tomas,

new grants usually only work immediately after a user reconnects to the database. This also affects technical users like APEX_PUBLIC_USER.
For performance reasons, the APEX Listener uses connection pools, i.e. it doesn't open a new connection for each request, but reuses existing ones managed in a pool. OHS uses a similar concept, which is why you'll always see a certain amount of database sessions for that technical user, no matter how many parallel requests you currently have. The number of sessions present depends on your pool configuration. You can influence that configuration in the JDBC settings of the APEX Listener.
I'm not sure why restarting the APEX Listener deployment isn't possible on your WLS, but I'm not a WLS expert... Certainly a restart (or redeployment) would flush the connection pool, so your problem would be gone.
I guess you only have rare cases where such things happen, so I wouldn't recommend to change the JDBC settings to much faster recreation of database sessions, because this can influence your performance significantly. It might be worth considering that on a development system if you are in an early stage of developing a new application. You could use the APEX Listener in standalone mode for such purposes, which could be restarted without caring about other people...

-Udo

Hello Tomas,

your scenario fit's to what will happen:

The second call can now get the same or a new connection, right?

Right. If your load didn't change much lately it is very likely to reuse an existing one from pool as well.

If it's a new, the call should succeed.

Right.

If it's the first connection, shouldn't the call be done again?

I'd expect to see the call actually is issued again. But it's still the same session in the database, and as mentioned before, the database gathers privileges only once, i.e. when the session is created. So the database will still refuse because the session "profile" is still the same. You could try that in sqlplus or SQL Developer as well - you need to reconnect to make new grants effective.

Now it seems that connection looks at the url and says "I've tried that one and it didn't work, here's the error you got then"...

I don't think that acutally happens, unless you have some caching mechanism in between that doesn't work well. There is a possibilty that something like Web Cache would store the error page as result, but usually error pages aren't cached.
The APEX Listener doesn't cache like that. You can specify file caching for certain procedures, but it doesn't sound like you've enabled this feature (and it is disabled by default).

I still think as long as you don't hit the reuse count for the connections (it's 1000 requests per default in the current APEX Listener release) or get fresh connections due to high load (i.e., the existing amount of connections isn't sufficient to serve all requests and your pool definition allows to increase the number of active sessions) you'll probably always get that error. In the latter case, you might still get that error if you get a connection that was opened before the new grant was issued.

-Udo

I'd expect to see the call actually is issued again. But it's still the same session in the database, and as mentioned before, the database gathers privileges only once, i.e. when the session is created. So the database will still refuse because the session "profile" is still the same. You could try that in sqlplus or SQL Developer as well - you need to reconnect to make new grants effective.

That's not true, in my experience. In Oracle, grants ("grant XXX on YYY to ZZZ") are immediately visible to a connected session, no need to reconnect.

I don't think that acutally happens, unless you have some caching mechanism in between that doesn't work well. There is a possibilty that something like Web Cache would store the error page as result, but usually error pages aren't cached.

I believe the Listener caches the "signatures" of procedures (probably for the lifetime of the Listener process, not the individual database connections), to avoid looking up the metadata (parameter names and types) for every call. Maybe the wrong signature is cached if the very first call is wrong. This is just speculation on my part, since I don't know how the Listener implements this (but having written a mod_plsql replacement for Microsoft IIS myself, see http://code.google.com/p/thoth-gateway/ , I am familiar with the topic).

- Morten

http://ora-00001.blogspot.com

Hi Morten,

That's not true, in my experience. In Oracle, grants ("grant XXX on YYY to ZZZ") are immediately visible to a connected session, no need to reconnect.

Shame on me, you're right. We do a lot with roles and recently had troubles with that - I probably mixed that up. Of course, even roles are available immediately, it's just that not every client is capable of enabling a new role at runtime.

I believe the Listener caches the "signatures" of procedures (probably for the lifetime of the Listener process, not the individual database connections), to avoid looking up the metadata (parameter names and types) for every call. Maybe the wrong signature is cached if the very first call is wrong. This is just speculation on my part, since I don't know how the Listener implements this (but having written a mod_plsql replacement for Microsoft IIS myself, see http://code.google.com/p/thoth-gateway/ , I am familiar with the topic).

Just to assume your speculation is truth and there is metadata caching: I'd expect no metadata to be returned if you don't have access rights, so I'm not sure how anything could be cached towards backend. This would only be possible if the entry is created before the result was fetched and stays empty because it's not being removed upon exception...
But I don't think this happens in APEX Listener. Perhaps I'll have time to put on a simple test case for this in a few days when I get back to my test environment... Shouldn't be too hard to reproduce the scenario and see if it's sufficient to do some reconfiguration to the pool to get new connections fast, or if it's really necessary to restart the APEX Listener...

-Udo

why would the listener look up metadata for the procedure to call?
Either the user has the correct procedure name and parameter names - the call succeeds.
Or the name or some parameter is wrong - the call fails and is reported to the user.

Hi Tomas,

Let me just repeat again that I don't know exactly how the Apex Listener implements its database calls.

My comments are based on my own experiences from writing the Thoth Gateway, which essentially does the same thing as mod_plsql and the Apex Listener, except it is written in .NET and runs on Microsoft IIS. See http://code.google.com/p/thoth-gateway/ for more details.

One reason for caching the procedure metadata is this:

Let's say you have a procedure with a parameter which is a "table of varchar2" (such as the f01, f02, etc. parameters in the "wwv_flow.accept" procedure in Apex). These can accept zero, one or more values, depending on how many times you have repeated that input element name on the HTML page.

So the Listener receives a call for the "wwv_flow.accept" procedure with a single value for the "f01" parameter. Without looking in the data dictionary (see DBA_ARGUMENTS), there is no way the Listener can know that the f01 parameter is actually a "table of varchar2" (and needs to be bound as that datatype when invoking the stored procedure). If the value is bound as a string (plain varchar2) parameter, the procedure call fails.

Now, there are at least two ways to handle this: You can either catch the error, assume it was really a "table of varchar2" parameter, and try again with that; or you can look it up in the data dictionary beforehand to avoid the error. In either case, it's not good for performance to do this for every procedure call.

The Thoth Gateway actually works this way: It first checks whether it has cached metadata for the procedure. If not, it tries to call the procedure based on just the names and values. If this fails, it silently catches the error and then looks up the procedure metadata from the data dictionary to make sure the parameter types are correct. It then calls the procedure again (and hopefully that should be successful). Then the metadata is cached. Which means that any subsequent invocations of the same procedure don't need to go through the same work again.

Looking up (and caching) the metadata also has another benefit: If there are input elements on the HTML page that do not correspond to parameter names in the procedure (perhaps some client-side JS framework added some input fields dynamically), then the procedure call would fail. You can see this in action using mod_plsql and DBMS_EPG (you get an error along the lines of "arguments in form not in procedure..."). However, the Thoth Gateway (and I believe also the Apex Listener) checks the incoming parameter names against the metadata, and just discards any parameter names/values that are not in the procedure.


- Morten

http://ora-00001.blogspot.com

Edited by: mobra on Oct 20, 2011 10:52 AM