Responding to both posts:

> -----Original Message-----
> From: Denys Beauchemin [mailto:[log in to unmask]]
> 
> [what] comes to mind is that the change ... on the Oracle side is 
> the culprit. Proving it might be difficult. ...
> more information about the system abort.  Maybe the number and the
> entire message could be posted.

I don't have it -- this is the first time I've been in an HP shop where I'm physically removed from being able to "go in and get my hands dirty" making the system work...

> I will go out on a limb here and ask you if you have updated 
> statistics on the Oracle side ...

OK, you're beginning to speak in Greek here... [pun fully intended ;)] how does one "update statistics"?  [again, I don't have access to the oracle system, I'll have to "suggest" this to the powers-that-be and hope they keep up on it]  Does this need to be done on a regular basis, or only when "structural" changes occur on the HP [IMAGE] side?

> [...] A minute per transaction sounds awful long to me

it does for me, too...

> and could leave the calling processes hanging in  mid-air for a long time.

I've also heard, somewhat anecdotally, that this "system" also access ANOTHER database on another system in the pursuit of processing "an invoice" [which I find can be made up of a handful of "transactions" (perhaps they meant "line items"?), typically 4-8 per invoice, however] so while it was stated as "roughly a minute per transaction", they really meant a minute per "invoice" -- even still, taking a minute to process maybe 8 "lines" is still "forever" in computer terms...

> -----Original Message-----
> From: Craig Lalley [mailto:[log in to unmask]]
> 
> First, what OS and what version of Oracle are you using.

HP: 6.5  [RELEASE: C.65.02   MPE/iX HP31900 C.25.06   USER VERSION: C.65.02]
Oracle gateway:
:print oraprods.pub.oragtway

Oracle Server
Oracle Transparent GTW for IMAGE/SQL 4.0  Installation Product List
        Wed Apr  1 20:00:00 1998

-------------------------------------------------------------------------------
Installation of Gateway v 4.0 in standalone mode
Oracle Transparent Gateway for IMAGE/SQL    V 4.0.0.0.0
                        With Two-phase Commit feature
                        With Passthrough feature
SQL*Net                                     V 2.2.3.0.0
Oracle7 RDBMS XL for GTW              V 7.2.3.0.0
==========

OK, I'm REALLY leary of an "april 1st" release date... :)

> You can turn on system file logging ... analyze the log files
> to determine the number of records read. 
> This could show a serial vs. index read issue.

??? SYSTEM logging shows number of DBGETS that occur on behalf of the oracle gateway?

> Are you using any tools, like Glance to monitor the Gateway 
> to determine if it is heavy I/O?

Not specifically, but a week or two ago, on the "test" system, we've run into a situation where the gateway has gone into a heavy CPU loop [90%+]  (however this is for a different program/procedure entirely; OTOH, it is one being developed for the production system...)

> Finally, do you have the memory dump, maybe you could send a
> snapshot of PROCESS_CURRENT and post it to the list.

[as mentioned above, I don't really have access to such -- I'll see what I can get, however, but I'll bet the answer is "not much"]
 
> Finally, you may want to check the TNS names file for corruption or changes.

Good thought, but so far as I can tell, this file hasn't changed in, well, years.  However, looking for this revealed something else that may be contributing to the problem: it doesn't appear that this product has been "tuned" in any way -- I see from what oracle calls "logs" that the gateway is configured for "max sessions=5, max cursors=50", and several other values which appear to be default or "what is supplied in the EXAMPLE configuration file" type values.

For comparison, what does oracle consider to be "a session"? (a seperate connection?)  Just to thouroughly mix things up, the "process" is initiated by a stored procedure or equvialent on the "oracle" database itself, which in turn spawns [several] threads/processes/whatevers that query the image database via this gateway (to retrieve essentially an order number and it's status: open, closed, partially received, cancelled, etc.)  So the concept of "session" [and/or a question as to how many "cursors" each session starts] may be of concern here...

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *