| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: event 10079 question
Hi,
The best would be to instrument the application (during desing and build stage) to record and save db call timing information (selectively, if data volume is an issue, only when db call response times exceed some predefined threshold). This requires code change, though and has a chance that some queries will remain uninstrumented, if not using an uniform interface to database.
An alternative would be to use OCI transparent dynamic callbacks, as described in OCI Programming Advanced Topics: http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci09adv.htm#sthref1456
Basically you compile your own shared library based on Oracle's specifications, then set ORA_OCI_UCBPKG environment variable to point to this library in your DB client before starting up the application. When you start up the application now, your custom library will be loaded along standard OCI ones and an initialization function is called. The init function can then hook to different OCI calls selectively (e.g. only to parse calls, execs, fetches, binds etc.. ) and this hook can call whatever code you like. Probably gettimeofday style stuff.
You can do pre-processing, post-processing and even instead-of processing - which could be used to make OCI-based applications directly talk to SQL Server without a single change in application side code for example ;)
You don't have to build all from scratch: If you install Oracle demos with server, you'll have sample dynamic tracing files under $ORACLE_HOME/rdbms/demo - ociucb.c for example. Look for *ucb* from there. You could the instrumentation you need with a simple modification there. The issue is, that if you want to have only selective tracing (without having to restart the app with this ORA_OCI_UCBPKG variable again) you should always enable it and have some other mechanism for controlling instrumentation/tracing as a file which would regularily be checked for some parameter (not on every OCI invocation though, as it would hit your performance).
You have to compile and run this stuff on client side, no server side changes needed.
Issues:
Cheers,
Tanel.
Tanel
can ou elaborate on that? We run the db on AIX, but clients come from all over USA over Citrix envirnoment. what I'd like to do it to see if I can get a network measurement when client reports a problem.
TIA
Raj
On 12/1/05, Tanel Põder <tanel.poder.003_at_mail.ee> wrote: Hi,
Which platform are you on?
If 10046 doesn't show anyhting obvious (e.g. only minority of total client response time is recorded in there), then you either need to start guessing whether the problem is in app code or network latency (by running simultaneous pinging and vmstat/mpstat on client/app server) or go the proper way and instrument database calls in the client code.
This can be quite easily done at OCI level, transparently, without modifying applications source code - take a look into $ORACLE_HOME/rdbms/demo/ociucb.c (if the demos are installed). Adding a simple timing call to this program would show you the db call response times from client perspective and if you combine it with 10046 trace info, you could find out whether the majority of total client response time is spent in app code, db server process or somewhere in between (network stacks, transport, network layer).
Tanel.
Here is dumb question,
I have a remote user who often complains about slowness, sometimes it is network, sometimes it isn't. Network guys are doing their bit to identify, I need to do my bit. will a trace with 10046 and 10079 highlight any network issues?
told you it was a dumb question. we are doing 10046 to identify if there are any sql bottlenecks.
TIA
Raj
----------------------------------------------
This space is available for rent.
--
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 01 2005 - 23:25:13 CST
 |
 |