I am not an Oracle DBA so I can't answer your question directly. I went
to the Oracle website to find out how to update statistics and wouldn't
you know it, one needs to subscribe to get web support at Oracle. I
invite you to do that, the site is at www.oracle.com.
But a quick search on Google reveals a plethora or information and
amongst many others, links to this page:
http://www.dbaoncall.net/references/ht_update_statistics.html
It's a start. Good luck.
Denys
-----Original Message-----
From: HP-3000 Systems Discussion [mailto:[log in to unmask]]On
Behalf Of Emerson, Tom
Sent: Wednesday, November 12, 2003 12:22 PM
To: [log in to unmask]
Subject: Re: [HP3000-L] More or less ON topic: oracle gateway for 3k
(problem)
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 *
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
|
