Re: Database link performance problems

Steve B wrote:
> We are experiencing performance problems across a db link between the two..
>
> We are accessing instanceA using OAS4.0.7.1 PL/SQL cartridge. A stored
> procedure in instanceA retreives data from instanceB across the link...this
(snip snip)
>
> When ran from a browser the time taken is always in the region of 100
> ms....when ran from a SQL*Plus session the initial run takes approx 100 ms,
> then after that it takes 2-3 ms....it seems to me that the OAS method needs
> to establish a new session on instanceB every time...??
>

I suspect that the SQLPLus session is so much faster for the 2nd thru n executions across the link is that Oracle will keep the session on the remote db created by the use of a link open until the local session terminates (or is explicitly closed.) Thus the 1st SQLPLus execution is slow, like the OAS execution, but the 2nd thru n executions avoid the overhead of opening the session.

I seem to recall that OAS could be configured to pre-create one/more sessions on each instance that it can (directly) connect to, for each cartridge you are using when the OAS server starts up. (I forget the exact mechanics, but it was something like "minimum" objects in the Applications & Objects screen.) I would try to configure the OAS server to pre-create sessions to the instance it connects to directly to avoid the overhead of session-creation when the app. executes. Unfortunately, AFAIK there is no way to do this with a link -- use of a link creates a session to the "remote" database when it is needed. And the session to the remote db is closed when the local session terminates -- I assume your OAS application is not creating a session across hits to the Oracle database, so that each access is a new session? If so there is probably no way to avoid the link overhead except maybe to use snapshots to make all of the tables needed by the OAS application "local" to instance A.
--

Vincent Ventrone    |  The MITRE Corp.
DBA, Dept. R101     |  M/S C020
vav_at_mitre.org       |  202 Burlington Rd.
(781) 271-7048      |  Bedford, MA 01730