5 Replies Latest reply: Oct 21, 2009 9:29 AM by Hemant K Chitale RSS

    DELETE in dblink on 10g

    user522961
      Hi,
      on 10g we have created DBLINKS toward some other DBs. Insert in remote tables using DBLINK is enough fast but the deletes are very slow. Any idea ? Any oracle bugs for DBLINK in 10g ?

      Thanks.
        • 1. Re: DELETE in dblink on 10g
          JustinCave
          Do you have any more information about the problem?

          - How many rows are you deleting?
          - Are you referencing local tables as well as remote tables? Are you referencing multiple remote tables?
          - Can you quantify "very slow"? For example, does deleting a single row take 1 second? 10 seconds? 10 minutes?
          - What is the query plan?

          There are any number of potential explanations-- without more information, though, it is tough to do more than grasp at straws.

          Justin
          • 2. Re: DELETE in dblink on 10g
            user522961
            Thank you justin.
            We can have more information from client but the question is that if there is any known bug on DELETE via DBLINK in ORACLE 10g.
            Regards.
            • 3. Re: DELETE in dblink on 10g
              728644
              you should ask youself the above question first.
              It's most likely there is something wrong in sql.
              • 4. Re: DELETE in dblink on 10g
                JustinCave
                user522961 wrote:
                Thank you justin.
                We can have more information from client but the question is that if there is any known bug on DELETE via DBLINK in ORACLE 10g.
                I am sure that if you scour Metalink, you will find at least one bug that involves a database link and a DELETE statement on whatever version of Oracle you are using (10g might mean 10.1.0.2 or 10.2.0.4 or anything in between). It is relatively unlikely, however, that you are actually hitting a bug. It is substantially more likely that you are encountering the far more common situation of a complex system not behaving the way you would want, in which case the information I asked for will help narrow down the source of the problem. Distributed DML is always extremely difficult for the optimizer to handle, so any performance issues that involve distributed queries make me suspect that the optimizer has made a poor decision.

                Justin
                • 5. Re: DELETE in dblink on 10g
                  Hemant K Chitale
                  How are you issuing the DELETE ? Is it a DELETE ... WHERE rowid=... OR WHERE indexed_column= .... ?

                  Any other form (e.g. delete being driven by a NOT IN operation against another table !!) can be more complex as a Distributed Transaction.


                  Hemant K Chitale