This discussion is archived
4 Replies Latest reply: May 4, 2012 7:42 AM by 713555 RSS

time to open a Dblink

817469 Newbie
Currently Being Moderated
Hello,

Info :
Oracle version : 11.2.0.3
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.

Problem :
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 ?
  • 1. Re: time to open a Dblink
    sb92075 Guru
    Currently Being Moderated
    veritas wrote:
    Hello,

    Info :
    Oracle version : 11.2.0.3
    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.

    Problem :
    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 ?
    do packet sniffing or enable SQL*Net trace.
    Problem may reside at OS/Networking level; external to Oracle.
  • 2. Re: time to open a Dblink
    KuljeetPalSingh Guru
    Currently Being Moderated
    what tnsping output saying.

    tnsping TESTDB
    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-MAY-2012 19:43:28

    Copyright (c) 1997, 2005, Oracle. All rights reserved.

    Used parameter files:
    D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB) (INSTANCE_NAME = TESTDB1)))
    OK (20 msec)
  • 3. Re: time to open a Dblink
    817469 Newbie
    Currently Being Moderated
    1. tnsping is fine (10, 20MS), it's the first thing I checked ...
    2. I don't see nothing relevant in listener trace. Both instances are on the same server and use the same listener. If you want I can send you a fragment from that trace, although I'm not sure the problem is at network level because in session trace file I see :
    "
    PARSING IN CURSOR #391753152 len=34 dep=0 uid=120 oct=3 lid=120 tim=896959474039 hv=1678898297 ad='7ffbb52e1d0' sqlid='c4n1fzdk13x3t'
    select sysdate from dual@link_db
    END OF STMT
    PARSE #391753152:c=0,e=*4679660*,p=0,cr=0,cu=1,mis=1,r=0,dep=0,og=1,plh=0,tim=896959474036
    .................
    "
  • 4. Re: time to open a Dblink
    713555 Pro
    Currently Being Moderated
    any logon trigger? sqlnet stuff going on? what happens when you sqlplus from the command line?

    set sql_trace=true on the remote database and tkprof it to show all waits as the connection comes in.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points