Re: How to trace a remote statement

From: Stefan Koehler <contact_at_soocs.de>
Date: Fri, 3 Oct 2014 12:43:39 +0200 (CEST)
Message-ID: <1381290349.143674.1412333019155.open-xchange_at_app02.ox.hosteurope.de>

Hi Amir,
i don't know VERTEX at all, so i am a little bit confused what kind of application you wanna trace and how you call it remotely.

In one way your a talking about a packaged application (Oracle Apex??) and then you are talking about calling a procedure from a package (PL/SQL package??)? How do you call the remote procedure - through database link?

The following assumes that you are talking about calling a PL/SQL procedure through a database link.

You can use PL/SQL procedure DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (on system B) to enable a dedicated trace based on the service name, module_name and (or) action_name, if the application is well instrumented. Otherwise you may want to create a separate database link, that uses a special service name for tracing. You omit the module_name and (or) action_name on system B and trace all the stuff for that special service name.

Afterwards you can profile the extended SQL trace with free tools like TVD$XTAT ( http://antognini.ch/top1/downloadable-files/ ) by Christian Antognini or tools with costs like Method R Profiler ( http://method-r.com/software/profiler ) and drill down, if this is a PL/SQL coding or SQL processing issue on system B.

Best Regards
Stefan Koehler

Oracle performance consultant and researcher http://www.soocs.de

> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 2. Oktober 2014 um 21:04 geschrieben:
>
> There are two systems involved in this scenario:
> - System A: A system that runs a program which calls a procedure on a remote system to calculate tax
> - System B: The remote system that runs an application that calculates tax
>
> System B is a packaged application from vendor VERTEX. The problem that we are trying to solve is that when code from system A calls a procedure
> that is part of a package in the taxing application on system B, the application on system A spends a lot of time waiting for the call to complete
> on system B. The issue is that VERTEX does not provide a way to enable tracing in its package.
>
> What would be the best way to enable tracing on the remote system to find out the root cause of the issue?
>
> Thank you,
> Amir

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 03 2014 - 12:43:39 CEST