Due to the asynchronous nature of our WebFOCUS reports, we're running out of available connections to our Oracle database. That's hindering normal users of that database, as their applications sometimes can't connect to the DB. It is also breaking our reports with TNS service not available messages:

 (FOC1400) SQLCODE IS 12519 (HEX: 000030E7)
 (FOC1394) CONNECT FAILURE
 : ORA-12519: TNS:no appropriate service handler found
 L    (FOC1406) SQL OPEN CURSOR ERROR.

Is there some way to reduce the number of Oracle connections that WebFOCUS uses? I'm looking for some kind of built-in connection pooler or some-such.

I found we can reduce the number of agents that a user can use (and the ability to queue those once past that limit), but we have several other databases for which we don't need that limit and it would only get in the way of our report users...

We also have a suspicion that perhaps WebFOCUS isn't closing its connections (or not quick enough) once a report has loaded. But, the reports aren't really the problem here - it's the launch-pages that create most of those parallel connections (due to async requests to fill dropdowns and such), so when to close a connection is actually a little more complicated...
Is there some command to explicitly close a connection (I suppose we need to figure out the correct connection handle for the request somehow)?

People here aren't eager to increase the number of connections of the database (it's at 75 now; I believe that's the default), as they suspect that it will just postpone the same issue a little longer.
I suspect we'll reach a stable number of connections once we have enough available, but I'll have to somehow convince them first.

This message has been edited. Last edited by: Wep5622,

My limited exposure to WF (8) has only shown me that connections are made to the ORCL DB on demand and drop off when the request (SQL) is complete rather than having persistent connections as seen with Oracle SOA and other software. So you are saying that your connections persist on ORCL DB after the report is rendered?

Are you using Parallel Execution in the DB? On tables that I have this enabled on and the appropriate init parms set, I have seen a single WF (TSCOM3.exe) request spawn as many as 100+ PX sessions. If you only monitor the WF process, you may miss this.

I would also check your Listener.log - if your server doesn't have enough memory to establish a PGA memory space for the new connection, nothing will be able to logon. (?)

quote:

We also have a suspicion that perhaps WebFOCUS isn't closing its connections (or not quick enough) once a report has loaded.

So, you are seeing the INACTIVE sessions linger in ORCL and not dropping off? Maybe Oracle Profiles can help here...

Well, part of the problem is that the connections from our launch-pages are all short-lived, so I haven't been able to catch them as tscom3 processes in the v$session table. The reporting connections are usually no more than a few - those are no problem.

What's odd is that I can usually only see about 40-50 sessions in v$session, while the limits in v$resource_limit tend to be over 70 of max. 75 connections. Most of those are client apps that people use throughout the company, but because of this there's sometimes not enough room for all the queries from our async launch pages and form elements stay empty...

I'd like to make our launch pages more robust by requiring fewer connections, preferably without changing the underlying code too much.

Of course, we also need to review our connection usage - we shouldn't be this close to the limit in the first place.

I investigated this px stuff, but querying v$px_session returns no rows. I suppose we don't use parallel execution then?

This message has been edited. Last edited by: Wep5622,

Do you have Toad ? I use the Session Monitor there but usually prefer the old java version of OEM for tasks like this. It is usually found in your $ORACLE_HOME (Enterprise Manager Console).

You can also look at the Explain Plans and see if any "PX" routes the Optimizer is taking?

I don't have Toad, I usually use Oracle SQL Developer for querying Oracle. It works, once it has finished starting up (oil tankers leave harbours faster than that OSD starts up!).

I can't check whether EXPLAIN PLAN mentions PX as I have no insight into what queries most of the connected clients execute. In the queries originating from our WebFOCUS reports I have NEVER seen any mention of PX, so I assume it's not being used there.

Plans from EXPLAIN PLAN aren't reliable anyway, as there's no guarantee that the plan from explain is the plan that the query optimizer actually used. Which is kind of ridiculous, but hey, this is Oracle we're talking about. If they make things too easy, then people would stop paying for those expensive Oracle courses and certifications :P

What I use is a script that I found somewhere on the internet and its output is a whole lot more usable as well - at least it contains the actual time each plan step took! Oh yes, you probably need to turn off autocommit for this (which is a good thing anyway).

ALTER SESSION SET statistics_level=ALL;

-- Your SQL query here

SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor (
NULL, -- this automatically uses the sql_id of the last SQL*Plus query
NULL, -- this automatically uses the child_number of the last SQL*Plus query
'TYPICAL IOSTATS LAST +PEEKED_BINDS'));

ROLLBACK;

Sometimes the query has already gone from the plan table before the plan has been fetched and the above results in an error - just rerun it.

One thing I miss in the output is how much actual time each step took. They only provide cumulative actual time values, so you're doing a whole lot of subtractions in your head reading the output.

Anyway, our problem is not with WebFOCUS, but with the tool that the database we're reporting on was designed for - we have too many users with open (inactive) connections and thus we're left with too few remaining connections for WebFOCUS. Those limits will be increased (actually, that should have happened last weekend).