Re: How to trace a remote statement

  • From: Stefan Koehler <contact@xxxxxxxx>
  • To: Amir.Hameed@xxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 3 Oct 2014 12:43:39 +0200 (CEST)

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 
( ) by Christian
Antognini or tools with costs like Method R 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

> "Hameed, Amir" <Amir.Hameed@xxxxxxxxx> 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

Other related posts: