2 Replies Latest reply: Sep 27, 2012 5:40 AM by Dom Brooks RSS

    Explain plan comparision

    DBA112
      Dear Experts,

      RDBMS - 11.1.0.7

      I have an INSERT stmt running fine in production but running long in UAT. Execution steps 9 - 12 differ b/w 2 plans. Tables/ Indexes are all in sync b/w 2 envt's, stats are fine.

      I want to copy the explain plan of prod to UAT. Can you suggest how this can be done? Also wanted to confirm if plan_hash_value number matches after execution plans are made in sync?

      Edited by: Ora DBA on Dec 11, 2012 1:00 AM
        • 1. Re: Explain plan comparision
          Fran
          Please check:
          http://ba6.us/book/export/html/117
          • 2. Re: Explain plan comparision
            Dom Brooks
            Have you checked that the defaul parallel settings of all objects are the same between both environments?
            If not then:
             ALTER TABLE <table_name> PARALLEL (DEGREE <degree>);
            I want to copy the explain plan of prod to UAT. Can you suggest how this can be done?
            Either a SQL Plan Baseline or a SQL Profile using the COE method (see Oracle Support doc id 215187.1)
            Then you can use the pack/export/unpack method to move between environments.

            However, both the SQL Plan Baseline and a COE SQL Profile work by capturing the outline hints from an existing plan in V$SQL_PLAN or DBA_HIST_SQLPLAN.
            You can see these hints using DBMS_XPLAN and the parameter FORMAT=>'+OUTLINE'.

            So, at the simplest level, you can just copy these hints from the execution plan output in one environment and paste them directly into the SQL in another environment to see if these hints produce the plan you want (and if they do then maybe use the proper built-in plan stability mechanism to enforce these hints).


            However, depending on the plan, these hints may not be sufficient to reproduce the plan if the non-default parallel degrees of the objects are not the same.

            For a SQL Plan Baseline, this will result in the baseline being rejected as the V$SQL_PLAN.OTHER_XML.PLAN_HASH_2 has to be reproduced for the baseline to be valid.
            For a SQL Profile, this may result in a similar looking plan but one that is not the same - perhaps your issue here?

            Also wanted to confirm if plan_hash_value number matches after execution plans are made in sync?
            The plan_hash_value is a hash of the execution plan and one particular execution plan should always have the same plan hash whether in one particular environment or across multiples.