Skip to Main Content
  • Questions
  • Performance Tuning: Tracing Over a DB Link

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, krishna.

Asked: July 29, 2018 - 11:48 am UTC

Last updated: July 31, 2018 - 11:34 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

hi Team,

currenlty in our environment i see many query execute on dblink .

we face performacne problem over their , can you let us know how we can trace sqlid of those sql's which are running remotely and how to figure out their bind variables values which are running remotley over dblink ?


thanks

and Chris said...

You need to trace the session on the remote database. An easy way to do this is to create a procedure on the remote DB that starts tracing:

create or replace procedure start_trace as
begin
  execute immediate 'alter session set tracefile_identifier = remote';
  dbms_monitor.session_trace_enable ( waits => true, binds => true );
end start_trace;
/


Then call this before running your query:

exec start_trace@remote;
select count(*) from t@remote;


You can read more about this in MOS note 422455.1 https://support.oracle.com/knowledge/Oracle%20Database%20Products/422455_1.html

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 31, 2018 - 10:16 am UTC

Can you share MOS note 422455.1 doc here or some important notes?
Chris Saxon
July 31, 2018 - 11:34 am UTC

The important bit is in the answer I gave: create a procedure on the remote database that starts tracing.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database