4 Replies Latest reply: Jan 25, 2013 4:23 AM by 45102 RSS

    execution plan change

    45102
      Hi,

      IN Oracle 11g,

      I have a query like this,

      SELECT *
      FROM query1
      WHERE M.LOG_ID IN ( guery2 ) ;

      when I run first the query2 and paste the values instead of query2, it works fine.
      But when I run the query as above, have poor performance.

      How can I tell the optimizer, which I did manually, Is there a hint for that.

      Thanks.
        • 1. Re: execution plan change
          971895
          try with exists...
          • 2. Re: execution plan change
            Karthick_Arp
            bekir.ozdil wrote:
            Hi,

            IN Oracle 11g,

            I have a query like this,

            SELECT *
            FROM query1
            WHERE M.LOG_ID IN ( guery2 ) ;

            when I run first the query2 and paste the values instead of query2, it works fine.
            But when I run the query as above, have poor performance.

            How can I tell the optimizer, which I did manually, Is there a hint for that.

            Thanks.
            Its very hard to give any comment with the information offered by you. I would suggest you read the FAQ {message:id=9360003} and Post the necessary information which could help us to understand your problem better.
            • 3. Re: execution plan change
              45102
              I may add that,

              query2 doesn't have any relation with query1.
              • 4. Re: execution plan change
                Dom Brooks
                See template tuning thread [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request to see how to approach the question.
                when I run first the query2 and paste the values instead of query2, it works fine.
                This is because you have a known number of values for log_id and so based on the statistics and the number of distinct values, the optimizer should have an accurate idea of the number of rows returned.

                But when I run the query as above, have poor performance.
                When you run with a subquery, the number of inputs for log_id are not known and are based on a estimate.

                Run your query with the gather_plan_statistics hint and fetch the actual execution statistics using dbms_xplan.display_cursor.
                1. You will see where the query spent most of the time
                2. Alongside how many rows the optimizer estimated, you will see the actual rows from each step and this may indicate what can be changed or improved