This discussion is archived
9 Replies Latest reply: Sep 23, 2013 11:57 PM by user8686720 RSS

SQL Plan Baseline

user8686720 Newbie
Currently Being Moderated

Hi,

 

I observed many sqls having null value against sql_plan_baseline field in v$sql view, even if they have been executed several times. Database version is 11.2.0.3 and optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines are both set to TRUE.

 

As per my understanding I was expecting baselines against sqls executed more than once. There must be something I am missing. Thanks in advance for any help in this regard.

 

SQL> select sql_id, executions, sql_plan_baseline,sql_profile

     from v$sql

     where sql_id='708ttsyv645s4';

 

SQL_ID        EXECUTIONS SQL_PLAN_BASELI SQL_PROFILE

------------- ---------- --------------- ---------------

708ttsyv645s4      45603

 

Regards,

Joy

  • 1. Re: SQL Plan Baseline
    Mustafa KALAYCI Journeyer
    Currently Being Moderated

    please check this, run :

     

    explain plan for

      select * from dual; /* your query here */

     

    and after :

     

    select * from table(dbms_xplan.display);

     

    or

     

    select * from table(dbms_xplan.display_cursor('708ttsyv645s4'));

     

    at the result you will see the execution plan and at last row you should see "note: sql plan baseline used for this sql statement" info if sqlplan has been used.

  • 2. Re: SQL Plan Baseline
    user8686720 Newbie
    Currently Being Moderated

    Thanks Mustafa for attending the question.

    I am providing o/p to a different sql as the earlier one was an INSERT statement.

     

    SQL> select sql_id, executions, sql_plan_baseline,sql_profile
      2  from v$sql
      3  where sql_id='gwvwagmgang0t';

     

    SQL_ID        EXECUTIONS SQL_PLAN_BASELI SQL_PROFILE
    ------------- ---------- --------------- ---------------
    gwvwagmgang0t      12143

     

    SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwvwagmgang0t'));

     

    SQL_ID  gwvwagmgang0t, child number 0

    -------------------------------------

    select count(*) cnt from order where account_id = :1  and

    order_msg_rcvd_date >= :2   and order_type in ( :"SYS_B_0",:"SYS_B_1" )

    and order_status = :"SYS_B_2"

     

    Plan hash value: 1854219677

     

    ----------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

    ----------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                    |                |       |       |    11 (100)|          |       |       |

    |   1 |  SORT AGGREGATE                     |                |     1 |    30 |            |          |       |       |

    |   2 |   PARTITION RANGE ITERATOR          |                |     1 |    30 |    11   (0)| 00:00:01 |   KEY |1048575|

    |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| ORDER      |     1 |    30 |    11   (0)| 00:00:01 |   KEY |1048575|

    |*  4 |     INDEX RANGE SCAN                | ORDER_NDX5 |     1 |       |    11   (0)| 00:00:01 |   KEY |1048575|

    ----------------------------------------------------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

     

       3 - filter(("ORDER_STATUS"=:SYS_B_2 AND INTERNAL_FUNCTION("ORDER_TYPE")))

       4 - access("ACCOUNT_ID"=:1 AND "ORDER_MSG_RCVD_DATE">=:2)

  • 3. Re: SQL Plan Baseline
    Mustafa KALAYCI Journeyer
    Currently Being Moderated

    okey then, your db is not using baseline for this sql. can  you find your sql in DBA_SQL_PLAN_BASELINES data dictionary view ?

  • 4. Re: SQL Plan Baseline
    sagargole Newbie
    Currently Being Moderated

    Hi,

     

    Are there any outlines applied on these particular SQLs. If yes then try removing those outlines. Also, you can try enabling the baseline at the session level and running the SQL you mentioned more than once in the session. Post this you can check whether the baseline reflects in the data dictionary table for the particular SQL.

     

    alter session set optimizer_capture_sql_plan_baselines = true;

  • 5. Re: SQL Plan Baseline
    user8686720 Newbie
    Currently Being Moderated

    Hi Mustafa,

    Sorry for quite a late reply. Last few days I was devoid of any network.
    I lost the last sql. But the purpose of question was primarily to clear my doubt. Similar to the last sql I have another sql doing multiple select operation. They are executed thousand times but no baseline got created. As mentioned earlier the db version is 11.2.0.3 and optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines are both set to TRUE.

     

    Thanks

     

    select s.child_number,s.sql_id, s.executions, s.sql_plan_baseline,s.sql_profile,
    b.SQL_HANDLE,b.PLAN_NAME,b.ENABLED,b.ACCEPTED, b.fixed
    from v$sql s, dba_sql_plan_baselines b
    where sql_id='gga03t7s8n0qk'
    and s.EXACT_MATCHING_SIGNATURE=b.signature;

     

    CHILD_NUMBER SQL_ID        EXECUTIONS SQL_PLAN_B SQL_PROFIL SQL_HANDLE      PLAN_NAME       ENA ACC FIX
    ------------ ------------- ---------- ---------- ---------- --------------- --------------- --- --- ---
               0 gga03t7s8n0qk      64580                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c207e45455

               0 gga03t7s8n0qk      64580                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c223cb404f

               0 gga03t7s8n0qk      64580                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c2f325ce1e

               1 gga03t7s8n0qk      66165                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c207e45455

               1 gga03t7s8n0qk      66165                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c223cb404f

               1 gga03t7s8n0qk      66165                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c2f325ce1e

               2 gga03t7s8n0qk      61909                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c207e45455

               2 gga03t7s8n0qk      61909                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c223cb404f

               2 gga03t7s8n0qk      61909                       SYS_SQL_ac7d6d7 SYS_SQL_PLAN_b9 YES NO  NO
                                                                8b9e248c2       e248c2f325ce1e

  • 6. Re: SQL Plan Baseline
    mtefft Journeyer
    Currently Being Moderated

    None of your baselines are 'Accepted'. So they are not going to be used to override a plan proposed by the optimizer.

     

    Now, this situation seems unusual in itself, because if a SQL has a baseline captured, and there is no existing baseline yet, then the first one is created in an ACCEPTED state, but those that follow are not.

     

    Did you do anything to manipulate your baselines? Such as, import them from another system, or explicitly create them from a SQL Tuning Set or from the cursor cache? Or did you drop one?

     

    To make the baselines available for use, you would use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE.

  • 7. Re: SQL Plan Baseline
    user8686720 Newbie
    Currently Being Moderated

    AFAIK we don't do any kind of manipulation. Though i'll definitely confirm this. I've observed there are thousand other sqls with same behavior as the one reported here. Thanks you for attending this.

  • 8. Re: SQL Plan Baseline
    Alvaro Pro
    Currently Being Moderated

    Are you using Stored Outlines on top of the SPM? Stored outlines take precedence over the baselines: please upload the following:

     

    select NAME, SIGNATURE, ENABLED from dba_outlines;

  • 9. Re: SQL Plan Baseline
    user8686720 Newbie
    Currently Being Moderated

    Alvaro,

    We are not using stored outlines for any query.

     

    Thanks

     

     select NAME, SIGNATURE, ENABLED from dba_outlines;

    no rows selected

Legend

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