DBlink performance slowdown
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: DBlink performance slowdown

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    I have a cursored SELECT statement in a stored procedure in database LOCAL that selects a bunch of columns from a single table on database REMOTE via a public DB link. The cursor loops and runs the SELECT 1000 times. If you run this procedure locally on the REMOTE database, it fetches 8,000 rows in 30 seconds over 1000 executes - not bad. However, when run over the DB link, it takes 330 seconds to fetch the same rows with the same executes - not good at all. I have tried several solutions, but have not brought the remote fetch time down. When the procedure is running, however, using a monitoring tool I see alot of system waits under the "SQL*Net Message from dblink" and "SQL*Net Messages to dblink" categories. Does anyone have any suggestions as to what may be slowing this query down, or further items to check on?
    Tim Hussar

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    On your database server, usually the biggest bottleneck is I/O. Your I/O's get executed at about 60-80 MB / sec on a typical SCSI drive.

    With DB links, your biggest bottleneck is the network. Your I/O's happen at 12.5 MB / sec on a 100BaseT network. The most critical part of a distributed solution is gobs and gobs of bandwidth (Gigabit network).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    you're right about that, marist89...however, I failed to mention that these 2 databases are on the same UNIX server. Oracle Support is saying that networking is not an issue because of that. I'm unclear as to why that would take networking out of the picture...is it still not resolving the dblink name against the tnsnames.ora and then sending a request to the listener, whether the entire process is done on the same server or not?
    Tim Hussar

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Depends on how your tnsnames.ora defines the remote alias. If the remote is an IPC connection, then yes, I could see their point. If it is setup like any other tnsnames.ora alias, I would suspect it's going over the network. I believe your db link waits kinda support this theory. I would either have your network guys run a trace on the network or run some netstats from the server to confirm.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    3

    Similar experience

    I have a similar experience with a dblink to a remote machine.

    The same update statement runs in milli seconds in our pre-production environment, and runs in multiples of hours in our production environment.

    The update statement looks sonething like this;

    update
    set = (select
    from @, @, @
    where...)
    and = <>

    The update statement with a hard-coded value for field2 runs in sub second times. The select statement by itself, using the dblink, runs in sub second times.

    Our DBA's tell me that both environments have been setup the same - same Oracle version, same init.ora settings, same network.

    Is there something in the Oracle environment that could be causing this hige difference in speed?
  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    same server use IPC

    also you understand that queries over db links the remote table is brought to local database?

  7. #7
    Join Date
    Oct 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    3

    Really?

    Thanks Pando.

    None of our DBA's suggested that a remote table is brought to the local db.

    If that is the case, can you explain this - when the select query is run by itself, it runs in sub second times.

    Also, in one environment, the DBA's report that 4k is tranferred across the db link, whereas in the other environment, 56Mb is transferred.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    of coz the table is brought to local database

    if I run

    select *
    from emp, dept@dblink
    where emp.deptno = dept.deptno

    where do you think the join takes place? do you think emp is sent to remote database and joined there then data is sent back?

    if you look the SQL run in remote database you will see

    select * from dept

  9. #9
    Join Date
    Oct 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    3

    Sounds feasible

    pando,

    I understand what you are saying. The update statement is;
    update table_name
    set Meter_Charge =
    (select qv.Charge_Rate
    from NFTSSEE.QUOTE_LINE@LNK_NFTSSEE ql,
    NFTSSEE.QUOTE_VALUE@LNK_NFTSSEE
    qv, NFTSSEE.QUOTE_HDR@LNK_NFTSSEE qh
    where qh.qb_ref_no =:NFTS_QUOTE_ID
    and qh.qb_id = ql.qb_id
    and ql.qb_id = qv.qb_id
    and ql.qbl_seq = qv.qbl_seq
    and ql.CHARGE_CATEGORY = 'METER'
    and qv.qbv_end_date = (select max(qbv_end_date)
    from NFTSSEE.QUOTE_VALUE@LNK_NFTSSEE
    where qb_id = ql.qb_id
    and qbl_seq = ql.qbl_seq))
    where NFTS_QUOTE_ID = :NFTS_QUOTE_ID

    (sorry about the formatting).

    What would make this statement behave differently. In one environment, 4k is dragged across the link, in the other 56Mb is dragged across the link.

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    ...because the join actually is taking place on the link in one case and brought local in the other.

    Try using the driving_site hint.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  11. Posting Permissions

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



    Click Here to Expand Forum to Full Width