From oracle-l-bounce@freelists.org Thu Dec 1 23:25:13 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB25PDIk000726 for ; Thu, 1 Dec 2005 23:25:13 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jB25OtAX000688 for ; Thu, 1 Dec 2005 23:24:56 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 357CB23FEC1; Fri, 2 Dec 2005 00:24:48 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07058-06; Fri, 2 Dec 2005 00:24:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8974923F322; Fri, 2 Dec 2005 00:24:47 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 02 Dec 2005 00:22:49 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5586023F4FB for ; Fri, 2 Dec 2005 00:22:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06362-09 for ; Fri, 2 Dec 2005 00:22:49 -0500 (EST) Received: from smtp806.mail.ukl.yahoo.com (smtp806.mail.ukl.yahoo.com [217.12.12.196]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 7B32A23F3C1 for ; Fri, 2 Dec 2005 00:22:48 -0500 (EST) Received: (qmail 88204 invoked from network); 2 Dec 2005 05:22:47 -0000 Received: from unknown (HELO porgand) (tanel@btinternet.com@200.33.188.176 with login) by smtp806.mail.ukl.yahoo.com with SMTP; 2 Dec 2005 05:22:43 -0000 Message-ID: <067001c5f700$6bf360e0$35bc21c8@porgand> From: =?utf-8?Q?Tanel_P=C3=B5der?= To: "ORACLE-L" References: <9177895d0512011006o13be5edj1e6a357552faa193@mail.gmail.com> <048c01c5f6b6$a7f79ab0$35bc21c8@porgand> <9177895d0512011925i2cd1551fo903780ea8ac7a85d@mail.gmail.com> Subject: Re: event 10079 question Date: Thu, 1 Dec 2005 23:22:26 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0669_01C5F6CE.175E0260" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 28842 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-To: tanel.poder.003@mail.ee X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.8 required=5.0 tests=AWL,BAYES_00, FROM_ENDS_IN_NUMS,HTML_MESSAGE,RCVD_IN_SORBS autolearn=no version=2.63 ------=_NextPart_000_0669_01C5F6CE.175E0260 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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:=20 http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci09= adv.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.=20 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: 1) OCI only (pure OCI, JDBC Thick), for JDBC Thin you could use some = Java method interception stuff for measuring start and end of each = method invocation or JVMPI. 2) Linux examples build well with GCC, on AIX you probably have to use = some IBM standard compiler (for Solaris, Forte was required). 3) Performance/storage space hit on heavy-load environments if not = planned carefully 4) Don't mess up OCI stuff (as the library runs in the same address = space with OCI binaries) Cheers, Tanel. ----- Original Message -----=20 From: rjamya=20 To: tanel.poder.003@mail.ee=20 Sent: Thursday, December 01, 2005 9:25 PM Subject: Re: event 10079 question 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=C3=B5der 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. ----- Original Message -----=20 From: rjamya=20 To: Oracle Discussion List=20 Sent: Thursday, December 01, 2005 12:06 PM Subject: event 10079 question 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?=20 told you it was a dumb question. we are doing 10046 to identify if = there are any sql bottlenecks. TIA Raj=20 ---------------------------------------------- This space is available for rent. --=20 ---------------------------------------------- This space is available for rent. ------=_NextPart_000_0669_01C5F6CE.175E0260 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable =EF=BB=BF
Hi,
 
The best would be to instrument the = application=20 (during desing and build stage) to record and save db call timing = information=20 (selectively, if data volume is an issue, only when db call response = times=20 exceed some predefined threshold). This requires code change, though and = has a=20 chance that some queries will remain uninstrumented, if not using an = uniform=20 interface to database.
 
An alternative would be to use OCI = transparent=20 dynamic callbacks, as described in OCI Programming Advanced=20 Topics: 
http://download-west.oracle.com/docs/cd/B1930= 6_01/appdev.102/b14250/oci09adv.htm#sthref1456
 
Basically you compile your own shared = library based=20 on Oracle's specifications, then set ORA_OCI_UCBPKG environment variable = to=20 point to this library in your DB client before starting up the = application. When=20 you start up the application now, your custom library will be loaded = along=20 standard OCI ones and an initialization function is called. The init = function=20 can then hook to different OCI calls selectively (e.g. only to parse = calls,=20 execs, fetches, binds etc.. ) and this hook can call = whatever code you=20 like. Probably gettimeofday style stuff. 
 
You can do pre-processing, = post-processing and even=20 instead-of processing - which could be used to make OCI-based = applications=20 directly talk to SQL Server without a single change in application side = code for=20 example ;)
 
You don't have to build all from=20 scratch:
If you install Oracle demos with = server, you'll=20 have sample dynamic tracing files under $ORACLE_HOME/rdbms/demo - = ociucb.c for=20 example. Look for *ucb* from there. You could the instrumentation you = need with=20 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=20 variable again) you should always enable it and have some other = mechanism for=20 controlling instrumentation/tracing as a file which would regularily be = checked=20 for some parameter (not on every OCI invocation though, as it would hit = your=20 performance).
 
You have to compile and run this stuff = on client=20 side, no server side changes needed.
 
Issues:
 
1) OCI only (pure OCI, JDBC Thick), for = JDBC Thin=20 you could use some Java method interception stuff for measuring start = and end of=20 each method invocation or JVMPI.
2) Linux examples build well with GCC, = on AIX you=20 probably have to use some IBM standard compiler (for Solaris, Forte was=20 required).
3) Performance/storage space hit = on heavy-load=20 environments if not planned carefully
4) Don't mess up OCI stuff (as the = library runs in=20 the same address space with OCI binaries)
 
Cheers,
Tanel.
 
----- Original Message -----
From:=20 rjamya
Sent: Thursday, December 01, = 2005 9:25=20 PM
Subject: Re: event 10079 = question

Tanel

can ou elaborate on that? We run the db on = AIX,=20 but clients come from all over USA over Citrix envirnoment. what I'd = like to=20 do it to see if I can get a network measurement when client reports a=20 problem.

TIA
Raj

On 12/1/05, Tanel=20 P=C3=B5der <tanel.poder.003@mail.ee>=20 wrote:
Hi,
 
Which platform are you = on?
 
If 10046 doesn't show anyhting = obvious (e.g.=20 only minority of total client response time is recorded in there), = then you=20 either need to start guessing whether the problem is in app code or = network=20 latency (by running simultaneous pinging and vmstat/mpstat on = client/app=20 server) or go the proper way and instrument database calls = in the=20 client code.
 
This can be quite easily done at = OCI level,=20 transparently, without modifying applications source code - take a = look into=20 $ORACLE_HOME/rdbms/demo/ociucb.c (if the demos are installed). = Adding a=20 simple timing call to this program would show you the db call = response times=20 from client perspective and if you combine it with 10046 trace info, = you=20 could find out whether the majority of total client response time is = spent=20 in app code, db server process or somewhere in between (network = stacks,=20 transport, network layer).
 
Tanel.
 
-----=20 Original Message -----
From:=20 rjamya
To:=20 Oracle = Discussion=20 List Sent:=20 Thursday, December 01, 2005 12:06 PM Subject:=20 event 10079 question

Here is dumb question,

I have a remote user = who=20 often complains about slowness, sometimes it is network, sometimes = it=20 isn't. Network guys are doing their bit to identify, I need to do = my bit.=20 will a trace with 10046 and 10079 highlight any network issues?=20

told you it was a dumb question. we are doing 10046 to = identify if=20 there are any sql bottlenecks.
TIA
Raj=20
----------------------------------------------
This space = is=20 available for = rent.


-- =
----------------------------------------------
This=20 space is available for rent.
------=_NextPart_000_0669_01C5F6CE.175E0260-- -- http://www.freelists.org/webpage/oracle-l