5 Replies Latest reply on Sep 5, 2012 2:14 PM by Matthew Morris

    SQL performance issue with remote table

    Kurt Geens
      We have a query that takes forever to complete, while it really should finish within a matter of seconds if one thinks it through. However, Oracle comes up with an execution plan that I can't explain...

      The query:
      SELECT ...
      FROM   svbarce
      WHERE  defooid IN (SELECT defooid FROM srbarce)
      MINUS
      SELECT ...
      FROM   srbarce
      In this query, svbarce is a synonym refering to a view vwbarce_aq using a database link. This view retrieves 590.309 records. The table srbarce on the other hand is a local table which is empty.

      The execution plan of this selection read as follows:
      OPERATION              OBJECT        OPTIONS       COST  CARD
      
      SELECT STATEMENT                                   3674     1
        MINUS
          SORT                             UNIQUE           4     1
            NESTED LOOPS                                    3     1
              REMOTE         VWBARCE_AQ                     2     1
              INDEX          SRBARCE_1     FULL SCAN        1     1
          SORT                             UNIQUE        3670     1
            TABLE ACCESS     SRBARCE       FULL          3669     1
      Which means that Oracle retrieves all the data from the remote table, and checks record by record against the index to match with the local table (which, I repeat, is empty).

      There's a few things that puzzle me in this explain plan:

      a) the cardinality of 1 for the local table, while it's empty (with statistics gathered)
      b) the cardinality of 1 and low cost of the remote view, while it retrieves more than 500.000 records (and all source tables have their statistics gathered)
      c) the (relative) high cost for the full table access in the second part of the minus set operation

      The database used is an Oracle 10.2.0.5.0 EE 64bit. The database link is linked to another schema within the same database. This is temporary however, as one schema will be moved to another database soon, therefore it's not an option to rewrite the synonyms using schema.tablename.

      It should also be noted that the VWBARCE_AQ is quite a complex view, using multiple other views and tables. It consists of a few queries combined with UNION ALL operators, and has an explain plan that indicates a cost of 33.398.082, and a cardinality of 33.481.814.611! However, I don't see an immediate relation between this and the inexplicable execution plan above.

      Any and all input and thoughts on the matter are more than welcome...
        • 1. Re: SQL performance issue with remote table
          vlethakula
          Try with
          1)driving site hint
          2)Do you have stats gathered on base tables on which complex view is defined.
          3)To isolate stats issue, run with rule hint
          • 2. Re: SQL performance issue with remote table
            Kurt Geens
            Thanks for your swift reply.

            I should have mentioned that the query can not be altered as it is part of a standardised synchronisation process used by many clients. Adding hints to the query is therefore not an option.

            The source tables have been analysed fairly recently, I am re-analysing them however just in case.
            • 3. Re: SQL performance issue with remote table
              Matthew Morris
              I should have mentioned that the query can not be altered...
              Are you saying that the query 110% can not be altered or that you don't want to alter it because alterations have wide-ranging impacts. I can understand not wanting to use HINTs, but if there is a away to write this query so that it works faster for all of your clients, are you saying that you still can't change it? Not being able to alter the query in any way really limits the possible options for tuning this.
              • 4. Re: SQL performance issue with remote table
                Kurt Geens
                For now, changing the query is not an option, exactly for the reason you indicate: this process runs perfectly on other sites. Furthermore, we have a work around to make it work on this system too: load SVBARCE manually. Once it contains data, the selection is a matter of minutes. My main objective with this thread therefore is not producing a more performant query, but understanding why Oracle ends up with the execution plan in the first place, and why it takes ages to complete...
                • 5. Re: SQL performance issue with remote table
                  Matthew Morris
                  Then if it's just one site having a problem, did you try creating a SQL profile for that site?

                  Edit: As soon as I hit 'Save' I thought -- this will only work if the query is run from the individual sites rather than the (I'm guessing) central location updating the individual clients.

                  Edited by: matthew_morris on Sep 5, 2012 10:13 AM