Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Question Unanswered: Accessing data using database link

    We are having two databases on our HP K-class (Oracle dbms is 7.3.4.1./HP UX 11) server. One is (say "d1") for all our inhouse applications. The other is (say "d2") is for oracle applications. We are having Oracle Inventory, Oracle Purchasing live in our production database.

    Now as we need to access data in "d2" database from "d1" database, we have created a private database link in user "u1" of "d1" database. Using this database link we access data of database "d2". Generally the SQLs are working fast. But at some random point of time the link seems to function quite slower. Its 30-40 times slower than its normal execution time.

    If following SQL is issued from database "d1"
    select column1
    from tab1@dblink_pointing_2_d2
    from tab1;

    It generally takes 1 seconds to execute, but sometimes it takes 40 secons or even more.
    If I issue this query frm directly "d2" database (without database link) then it wont even take 1 second, it would be executed in a flash.

    How could we resolve this problem ? What are the parameters we need to understand to optimize database link ? What could be the problem that the query executes in 1 seconds in most cases, but takes nearly 40 seconds at some random point of time. We have seen that the system load is not a cause of this problem. Its nearly equally distributed.

  2. #2
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    may be :
    - networks problem (see network administrator)
    or
    - overLoad remte database for various reasons

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    do a STATSPACK snap on the database that runs the query and then have it analyzed at oraperf. Normally the analyzation suggests a few solutions.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •