1 Reply Latest reply on Dec 5, 2014 6:36 PM by jaminsql

    Old Query Plans

    eniacpx

      I have noticed we have old query plans hanging out in DPA. Recently I removed an index hint from a query, but DPA still shows the hinted plan as being in use. When I view the plan in Plan Explorer, it still shows the hint in the query.

       

      How can we prevent this from happening, it makes it nearly impossible to determine how query changes are affecting query performance.

        • Re: Old Query Plans
          jaminsql

          It could be that the plan hash didn't change so DPA is not gathering the plan text. If you clear the plan text from the database it should force the new plan text to be recollected.

           

          select id, name from cond; -- note the id for the instance name in question

          select PLAN_HASH_VALUE,PIECE,QUERY_PLAN_XML from conspt_<id from cond> where plan_hash_value = '<our hash value>'

           

          If you remove the rows for this plan you should get the new plan text on next collection. Note, we highly recommend backing up the DPA/Ignite table (conspt_id) prior to doing this.