Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    North Dakota
    Posts
    3

    Unanswered: database link performance

    I have 2 databases on the same server, one production, the other for archive purposes, both V7.3.4 on HPUX 11. I employ database to make each others' tables available to the user's within each db. Performance is terribly slow.

    Both querying data, and writing data over these links is very slow, 4 or 5 minutes for a query over the link that take an eyeblink within the same SID; the interbox communication appears to be going out to sql*net and back again.

    Is there a means of improving this communication? Is it an HPUX/Oracle problem, or a database tuning issue?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Why have a database link if it is the same database?
    Database links are for seperate databases.

    If you want to query the archive environment then:
    select * from archive.table_name;

    simple as that.

    please explain why this would not work.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is the db link in question a public or private dblink ???

    Have you tried anonymous public db link and using global database
    name ??? select * from global_name;

    alter database rename global_name to "linkname"

    Gregg

  4. #4
    Join Date
    Aug 2003
    Location
    North Dakota
    Posts
    3
    Thanks for your reply,

    These are two distinct databases. They each contain identical schemas, however one database contains archived records, the other live production records.

    I have tested queries against tables that exist in both databases, when I execute against the 'local' tables, the are instantanious, against the 'foriegn' table, they are very slow. Same indexes exist in each database.

    I am trying to write queries that merge production records with archived records, but they are so slow, (5 min up to an hour).

    My purge process writes to the archive tables via After Delete triggers. A purge without archiving takes 30 min, with the triggers on takes 12 hours or more.

    Where do I attack this problem?

  5. #5
    Join Date
    Aug 2003
    Location
    North Dakota
    Posts
    3
    Originally posted by gbrabham
    Is the db link in question a public or private dblink ???

    Have you tried anonymous public db link and using global database
    name ??? select * from global_name;

    alter database rename global_name to "linkname"

    Gregg
    The database link is public, and I have defined private synonyms for the tables (i.e. livepart = owner.part@live.world), then I select * from livepart.....

    What benefit does the global name offer? Or the anonymous link? Is an anonymous link supported by 7.3.4?

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One thing which might help is to use DRIVING_SITE hint.

    from the concepts manual:

    DRIVING_SITE
    The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. Syntax of this hint is:

    where table is the name or alias for the table at which site the execution should take place.

    Example:

    SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@rsite
    WHERE emp.deptno = dept.deptno;


    If this query is executed without the hint, rows from DEPT will be sent to the local site and the join will be executed there. With the hint, the rows from EMP will be sent to the remote site and the query will be executed there, returning the result to the local site.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oh another reason for a slow link is the size of net8 packet mismatches the size of your network protocol's packet size i.e. if your TCP packet is 1548 bytes large and you net8 packet is 1550 bytes long then every net 8 packet causes two TCP packets to be sent. If you reduce the net 8 packet size to less that the TCP packet size then it would be more efficient.

    for more info try this
    http://www.jlcomp.demon.co.uk/sdu.html

    Alan

Posting Permissions

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