9 Replies Latest reply: Sep 24, 2013 1:57 AM by user8686720 RSS

    SQL Plan Baseline

    user8686720

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          Alvaro,

                          We are not using stored outlines for any query.

                           

                          Thanks

                           

                           select NAME, SIGNATURE, ENABLED from dba_outlines;
                          

                          no rows selected