Re: oracle log user activity, from outside db machine?

From: Igor Racic <igor.racicC_at_freesurf.fr_no_C>
Date: Mon, 12 Jul 2004 01:04:30 +0200
Message-ID: <40f1c70d$0$27297$626a14ce@news.free.fr>

Bjoern Wiechmann wrote:
>
>
> Igor Racic schrieb:
>

>>
>> It seems he doesn't have performance problem but bug in application.

>
> Yes, thats the problem. Some data "meet" on the server in a strange way,
> then several funny effects are happen. Some apps run, some crash, but
> nothing to easily identify whats wrong. And everytime new combination of
> crashing/running apps, almost never same combination twice. Looks like
> it depends on the data and/or on the statements executed at that time,
> thats why I was asking for chances to enable logging and so on. The bug
> seems to be a combination of a performance problem (too slow - or not
> finished when trying to do something) and an application timing problem,
> difficult to analyze without database on heavy load. My own apps I may
> control, but with oracle - sometimes more black-box than I like ;-)

Just some ideas...
Have you tried to see what SQL run in moment when some client goes down ? It might be that you are passing some application defined time-out in some longer-than-allowed SQL execution.
Could be that some query return more rows that schould and app doesn't react properly. Check this kind of queries. Are you sure that crash are related to database and not to some memory alloc/dealloc (let's say C++) problem or anything that is linked only to application?
Application could not do some client side logging ? You don't have unstable network ?

Least impact would (theoretically) be something like Sniffer (with Oracle module) to get passed things over network. When I looked last time it looked promising. But it's not a cheap thing. But, also, if some crypting is on place, you probably wont go further with that.

Key is, of course, to find pattern, being able to repeat problem and then will be much easier to catch it.

But, actually, your application is black box here :-)

> Thats the reason for my question - I know it has a measurable effect on
> the database performance, when I enable logging. But I don't know how
> big - and to prevent from any effects, I was asking for a logging on
> "outside" the machine.

Depends on many things.
How many connections could be suspitious in a moment ? Do a test yourself running 1 app with/without in trace mode and see the difference, try then with 2 and then you might have better perception.

Igor Received on Sun Jul 11 2004 - 18:04:30 CDT