veritas wrote:do packet sniffing or enable SQL*Net trace.
Oracle version : 188.8.131.52
Os : Win Server 2008 64x
2 oracle instances running on the same server.
1 public dblink created on one of the instance towards the other.
Every time I open a session (sqlplus) and run a select using the dblink, it take at least 4.69 seconds, even for a simple query as "select sysdate from dual@db_link_name";
This happens only at the first run, after that, for next executions, the time is 0.04 seconds.
If I close the dblink with "alter session close database link ...' and run the query again, I get 4.49 seconds again.
So, somehow, this time seems related to opening the dblink.
From the autotrace and trace files, looks like this time is going into parsing phase and it's accompanied with 1 db block gets and 256 redo size
The db block get seems to be from undo, because from what I understand, any select from a db link starts a distributed transaction and gets an undo block. That can be check with :
select t.used_ublk,t.used_urec from v$session s,v$transaction t where s.taddr=t.addr and s.sid=(select sid from v$mystat where rownum=1);
after running the first select through dblink;
So the questions are : where exactly are spent those 4.69 seconds and how can be tuned ?