This discussion is archived
2 Replies Latest reply: Sep 27, 2012 3:31 AM by Fran RSS

Explain plan comparision

DBA112 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Please check:
    http://ba6.us/book/export/html/117
  • 2. Re: Explain plan comparision
    Dom Brooks Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points