This discussion is archived
7 Replies Latest reply: Jan 21, 2013 7:44 AM by 441858 RSS

improving performance of DML over links

441858 Newbie
Currently Being Moderated
Oracle 11gR2/RHEL5

I wanted to know what the experts have to say about possible ways of improving performance (speeding up) of DML when performing it over a database link. For example, performing UPDATES/DELETES from database A on database B over the network (database link).
Primary looking at reducing the 'SQL*Net message to client' wait event.

Thank you all.

JrOraDBA
  • 1. Re: improving performance of DML over links
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) Why are you doing DML over a database link in the first place? Oracle provides a variety of tools to replicate changes (Streams, materialized views, CDC, etc.). It is almost certainly more efficient to use one of those rather than coding DML over database links in the first place (plus those approaches are much more robust when there are inevitably network issues or one site is down).

    2) Are you doing a bunch of single-row operations? Or large set-based operations? If you are doing single-row operations, can you move to set-based operations?

    3) How much of the delay is due to the inherent limitations of your network? Can you move to a network with higher bandwidth and lower latency?

    You haven't provided any code, you haven't shown us any AWR/ Statspack/ ASH reports to show what current performance is like, you haven't told us what you would like performance to be, etc. so it's hard to be very specific.

    Justin
  • 2. Re: improving performance of DML over links
    441858 Newbie
    Currently Being Moderated
    Thanks for your reply Justin.

    Sorry for being vague and not providing details. I did think about the other alternatives like streams and MViews. This procedure was put together by a developer not me.

    In any case, I will try to summarize what I see in the ASH report for this issue (as I can't post all of it here). And a correction...I am looking at reducing the 'SQL net message FROM dblink' not 'to'.

    The Top Events are:
    - CPU + Wait for CPU (CPU)
    - SQLNet message from dblink (network)
    - log file sync (Commit)

    The 'SQLNet message from dblink' wait is for a PL/SQL block as follows:
    DECLARE 
      v_sqlcode number; 
      v_sqlerrm varchar2(300); 
    
    BEGIN
       abc.upd_person 
       EXCEPTION when OTHERS 
          then v_sqlcode := SQLCODE; 
               v_sqlerrm := SQLERRM; 
       dbms_output.put_line('UPD_PERSON ERROR: '||v_sqlcode||' '||v_sqlerrm); 
    END;
    The procedure that is being called (abc.upd_person) has in it a update statement that is updating a table with several thousands of rows. It is doing this within a loop. Something like this:
    ...
    ...
    update lg_person
    set processed = systimestamp
    where lg_seq_num = v_sl    - - - this is a NUMBER that has a minimum value of a table
    To me it looks like this is where the issue is. It is executing the procedure within the PL/SQL block above, which is updating a table looping through....all while going over a database link.

    I hope this makes sense. I am still trying to understand what the developer is trying to do :)

    Thanks and sorry for the late reply.
  • 3. Re: improving performance of DML over links
    rp0428 Guru
    Currently Being Moderated
    You said you wanted help but then you don't respond at all to ANY of the questions/issues Justin ask about
    >
    1) Why are you doing DML over a database link in the first place? Oracle provides a variety of tools to replicate changes (Streams, materialized views, CDC, etc.). It is almost certainly more efficient to use one of those rather than coding DML over database links in the first place (plus those approaches are much more robust when there are inevitably network issues or one site is down).

    2) Are you doing a bunch of single-row operations? Or large set-based operations? If you are doing single-row operations, can you move to set-based operations?

    3) How much of the delay is due to the inherent limitations of your network? Can you move to a network with higher bandwidth and lower latency?
    >
    That information is needed first.
    >
    I hope this makes sense. I am still trying to understand what the developer is trying to do
    >
    Until you do understand that it is premature to be thinking about solutions. That is precisely why Justin ask the questions he did.

    Focus on the problem first. Identifying the possible solutions comes later.
    >
    The procedure that is being called (abc.upd_person) has in it a update statement that is updating a table with several thousands of rows. It is doing this within a loop
    >
    That type of processing is a red flag no matter what system it is trying to work with; local or remote.

    That is exactly why it is necessary to understand the problem first rather than just try to patch what may be a flawed implementation of the wrong solution.
  • 4. Re: improving performance of DML over links
    Justin Cave Oracle ACE
    Currently Being Moderated
    So, then, is it safe to assume that the procedure is doing a bunch of single-row updates?

    If you are waiting for a message from the database link, that means that you are waiting for the remote database to reply. If you are doing a bunch of single-row updates, that means that for every iteration of the loop, you are waiting for at least one packet (the request) to go from your machine to the remote machine, for the remote machine to process the update, and for one packet (the acknowledgement) to go from the remote machine back to your machine. That can easily take 10s of milliseconds which add up quickly if you're iterating through the loop thousands of times.

    Can you move to set-based operations instead of row-by-row operations?
    Can you make use of Streams or materialized views or any other built-in replication technology? You say you've thought about those alternatives but not whether you have discarded them (or why).

    Justin
  • 5. Re: improving performance of DML over links
    441858 Newbie
    Currently Being Moderated
    Thank you both for your replies.

    I apologize for being vague and not understanding the problem. I just wanted to have possible options in my hand as I analyzed each section of the problem (while having a rough idea of what was trying to be achieved) :). Of course, in most cases we understand the problem first and then look for solutions....as you mentioned.


    Yes, we can assume that the procedure is doing a bunch of single-row updates.

    I probably could move to set based operations. Could you give a simple example of a set-based operation vs single-row.

    We can definitely make use of one of those technologies. I have already implemented Streams....but for this specific problem I am leaning towards MViews because we will be moving towards the DW type setup. I will just have to make sure we meet the requirements of MViews like fast refresh, query rewrite, etc.

    Thank you.
  • 6. Re: improving performance of DML over links
    Purvesh K Guru
    Currently Being Moderated
    Why not consider the use of Updatable Materialized Views? Data from Replication site can be pushed to Master site during Refresh.
  • 7. Re: improving performance of DML over links
    441858 Newbie
    Currently Being Moderated
    Sorry perhaps I was unclear. We have Streams configured, but for a different environment althogether and a different purpose.

    There is no replication setup for this environment at all and there is no link between the 2 environments nor do we want one.

    Thanks.

Legend

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