12 Replies Latest reply: Nov 4, 2013 9:04 AM by AnjumShehzad RSS

    Stored Outlines & Restore Table Stats

    AnjumShehzad

      Hi all,

       

      I would like to know how restoring of table stats change execution plans when stored outlines are in place?

       

      DB version is 10.2.0.4.

       

      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ------------------------------
      optimizer_dynamic_sampling           integer                          0
      optimizer_features_enable            string                           10.2.0.4
      optimizer_index_caching              integer                          0
      optimizer_index_cost_adj             integer                          65
      optimizer_mode                       string                           FIRST_ROWS_10
      optimizer_secure_view_merging        boolean                          TRUE

      query_rewrite_enabled                string                           TRUE
      star_transformation_enabled          string                           FALSE

      Following actions were done last month:

      1. dbms_stats.gather_table_stats(schema IMANAGE, Table DIT_TRUSTEE)

      2. dbms_stats.lock_table_stats(schema IMANAGE, Table DIT_TRUSTEE)

      3. Once the desired execution plan for Query 1 (e.g. sql_id=ah8nzqhyu7xh4) was in V$SQL and was used by query, I created a Stored Outline using DBMS_OUTLN.create_outline, and enabled outline by setting use_stored_outlines to the desired outline category.

      4. Every thing remained fine for almost 30-35 days

       

      Today, there was another query (e.g. sql_id=7anr84k12d5rt, using same DIT_TRUSTEE table) whose  query plan got changed. This query didn't have any stored outline in place and was performing without any problem earlier. So, I gathered stats for the same table (DIT_TRUSTEE) after unlocking its stats. It didn't help. So, I thought to restore old stats against which query used to perform better while also rebuilt some indexes. After the restore of table stats (DIT_TRUSTEE) to a date which was earlier than the date of creation of 1st stored outline, the execution plan of query 1 (e.g. sql_id=ah8nzqhyu7xh4) got changed. Can we know the reason why it got changed? I was under the impression that any change in stats doesn't impact the execution plan if stored outline is in place for a query. But it looked like this wasn't the case.

       

      It would be good if someone can explain or share their experiences.

       

      regards,

      -

      Anjum

        • 1. Re: Stored Outlines & Restore Table Stats
          AnjumShehzad

          Can anyone share their experiences? or do you suggest creating an SR.

          • 2. Re: Stored Outlines & Restore Table Stats
            JohnWatson

            I have never seen the behaviour that you describe.'In my experience, outlines will stabilize execution in the circumstances you describe.

            Can you show the output of explain plan for the query, to prove that the outline really is being used? Then gather stats, and show the exec plan again to prove that it is no longer being used?

             

            I'll be covering all the techniques for stabilizing SQL execution in  courses I'm running in a couple of weeks,

            Oracle 11g SQL Tuning for Developers and DBAs: Hands-On Training | SkillBuilders.com/Oracle

            http://skillbuilders.com/instructor-led-training/Course_outlines/new/course-description.cfm?tab=course-details&c=new/Oracle-Database-11g-Wait-Event-Performance-Tuning&id=458

            if you are interested.

            --

            John Watson

            Oracle Certified Master DBA

            • 3. Re: Stored Outlines & Restore Table Stats
              AnjumShehzad

              It is a production system and I cannot repeat the process again but i am looking at my logs to explain the details:

               

              First the query is below one:

               

              ah8nzqhyu7xh4
              =============
              SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS,
              imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE,
              imanage.DIT_TRUSTEE.DOMAIN, imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE,
              imanage.DIT_TRUSTEE.HOME_LIBRARY, imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED,
              imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011,
              imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME,
              imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015,
              imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME,
              imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID, imanage.DIT_TRUSTEE.SYNCHRONIZE_ID,
              imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME, imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024, imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY,
              imanage.DIT_TRUSTEE.WORKSPACE_RSID, imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED, imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED,
              imanage.DIT_TRUSTEE.S_CCSSBL_LBRRY_LST_NHRT_F60326, imanage.DIT_TRUSTEE.NAME_FIRST, imanage.DIT_TRUSTEE.NAME_MIDDLE,
              imanage.DIT_TRUSTEE.NAME_LAST, imanage.DIT_TRUSTEE.NAME_FULL, imanage.DIT_TRUSTEE.NAME_SUFFIX,
              imanage.DIT_TRUSTEE.NAME_SALUTATION, imanage.DIT_TRUSTEE.GENDER, imanage.DIT_TRUSTEE.JOB_TITLE,
              imanage.DIT_TRUSTEE.ANNIVERSARY, imanage.DIT_TRUSTEE.BIRTHDATE, imanage.DIT_TRUSTEE.ASSISTANT_RSID,
              imanage.DIT_TRUSTEE.LOCALE_LANGUAGE, imanage.DIT_TRUSTEE.LOCALE_COUNTRY, imanage.DIT_TRUSTEE.LOCALE_TIMEZONE, imanage.DIT_TRUSTEE.EMAIL_1,
              imanage.DIT_TRUSTEE.EMAIL_2, imanage.DIT_TRUSTEE.EMAIL_3, imanage.DIT_TRUSTEE.EMAIL_FORMAT_1,
              imanage.DIT_TRUSTEE.EMAIL_FORMAT_2, imanage.DIT_TRUSTEE.EMAIL_FORMAT_3, imanage.DIT_TRUSTEE.IM_ID, imanage.DIT_TRUSTEE.PHONE_HOME,
              imanage.DIT_TRUSTEE.PHONE_BUSINESS, imanage.DIT_TRUSTEE.PHONE_MOBILE, imanage.DIT_TRUSTEE.PHONE_PAGER, imanage.DIT_TRUSTEE.PHONE_OTHER,
              imanage.DIT_TRUSTEE.FAX_HOME, imanage.DIT_TRUSTEE.FAX_BUSINESS, imanage.DIT_TRUSTEE.FAX_OTHER, imanage.DIT_TRUSTEE.MAILING_ADDRESS,
              imanage.DIT_TRUSTEE.BA_DEPARTMENT, imanage.DIT_TRUSTEE.BA_LOCATION, imanage.DIT_TRUSTEE.BA_COMPANY, imanage.DIT_TRUSTEE.BA_STREET_1,
              imanage.DIT_TRUSTEE.BA_STREET_2, imanage.DIT_TRUSTEE.BA_POBOX, imanage.DIT_TRUSTEE.BA_CITY, imanage.DIT_TRUSTEE.BA_STATE,
              imanage.DIT_TRUSTEE.BA_COUNTRY, imanage.DIT_TRUSTEE.BA_POSTAL_CODE, imanage.DIT_TRUSTEE.HA_STREET_1, imanage.DIT_TRUSTEE.HA_STREET_2,
              imanage.DIT_TRUSTEE.HA_POBOX, imanage.DIT_TRUSTEE.HA_CITY, imanage.DIT_TRUSTEE.HA_STATE, imanage.DIT_TRUSTEE.HA_COUNTRY,
              imanage.DIT_TRUSTEE.HA_POSTAL_CODE, imanage.DIT_TRUSTEE.WP_HOME, imanage.DIT_TRUSTEE.WP_BUSINESS, imanage.DIT_TRUSTEE.WP_OTHER,
              imanage.DIT_TRUSTEE.NE_DELIVERY_MODE_IMMEDIATE, imanage.DIT_TRUSTEE.NE_DELIVERY_MODE_SUMMARY, imanage.DIT_TRUSTEE.NE_USER_PREFERENCE,
              imanage.DIT_TRUSTEE.NE_USER_SUMMARY_TIME, imanage.DIT_TRUSTEE.NE_COPY_TO_ASSISTANT, imanage.DIT_TRUSTEE.IS_EXTERNAL ,
              imanage.DIT_TRUSTEE.IS_IS_EXTERNAL_INHERITED, imanage.DIT_TRUSTEE.NOTES, imanage.DIT_TRUSTEE.LOCATION_RSID,
              imanage.DIT_TRUSTEE.IS_ALL_IP_VALID, imanage.DIT_TRUSTEE.LAST_LOGON_TIME, imanage.DIT_TRUSTEE.S_HM_PG_WRKSPC_NHR_F4294966291,
              imanage.DIT_TRUSTEE.S_PRFRNCS_FCLTY_NH_F4294966292, imanage.DIT_TRUSTEE.HOME_PAGE_WORKSPACE, imanage.DIT_TRUSTEE.PREFERENCES_FACILITY,
              imanage.DIT_TRUSTEE.P_NEW_ITEM_INDICATOR, imanage.DIT_TRUSTEE.P_PWD_QUESTION, imanage.DIT_TRUSTEE.P_PWD_ANSWER,
              imanage.DIT_TRUSTEE.X_ECF_USER, imanage.DIT_TRUSTEE.X_DEPARTMENTS, imanage.DIT_TRUSTEE.X_TP_CLASSIFICATION,
              imanage.DIT_TRUSTEE.X_IMR_ENABLED_TIME, imanage.DIT_TRUSTEE.X_CAN_MANAGE_COI, imanage.DIT_TRUSTEE.X_CAN_ADD_THIRD_PARTY,
              imanage.DIT_TRUSTEE.X_IS_SECURITY_ADMINISTRATOR, imanage.DIT_TRUSTEE.X_IS_A_AND_S_DIRECT_LOAD, imanage.DIT_TRUSTEE.X_IS_ECF_DIRECT_LOAD,
              imanage.DIT_TRUSTEE.X_IS_NATIVE_REPOSITORY, imanage.DIT_TRUSTEE.X_CLIENT_CATEGORY, imanage.DIT_TRUSTEE.X_IS_ECF2_USER,
              imanage.DIT_TRUSTEE.X_IS_ECF2_ADMIN, imanage.DIT_TRUSTEE.X_CWS_UNIT, imanage.DIT_TRUSTEE.X_XCHANGING_ID,
              imanage.DIT_TRUSTEE.X_IS_DOCUMENT_FILE_VIEWER, imanage.DIT_TRUSTEE.X_IS_CWS_USER
              FROM imanage.DIT_TRUSTEE
              WHERE ((imanage.DIT_TRUSTEE.WORKSPACE_RSID = 1665798)
              AND (UPPER(imanage.DIT_TRUSTEE.RDN) = 'POLICY ADMINISTRATORS'))
              ORDER BY imanage.DIT_TRUSTEE.NAME_FIRST ASC, imanage.DIT_TRUSTEE.NAME_LAST ASC, imanage.DIT_TRUSTEE.UID_F21024 ASC,
              imanage.DIT_TRUSTEE.RDN ASC;

               

              The good plan is this one:

               

              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------
              Plan hash value: 1657249878

              ---------------------------------------------------------
              | Id  | Operation                    | Name             |
              ---------------------------------------------------------
              |   0 | SELECT STATEMENT             |                  |
              |   1 |  SORT ORDER BY               |                  |
              |   2 |   TABLE ACCESS BY INDEX ROWID| DIT_TRUSTEE      |
              |   3 |    INDEX RANGE SCAN          | IX_DIT_TRUSTEE_6 |
              ---------------------------------------------------------

               

              Once the outline is created, the hints associated with the outline are:

               

              SELECT node, stage, join_pos, hint  FROM dba_outline_hints WHERE name = 'SYS_OUTLINE_13100417531513606';

                    NODE      STAGE   JOIN_POS HINT
              ---------- ---------- ---------- --------------------------------------------------
                       1          1          1 INDEX_RS_ASC(@"SEL$1" "DIT_TRUSTEE"@"SEL$1" ("DIT_
                                               TRUSTEE"."WORKSPACE_RSID" "DIT
                       1          1          0 OUTLINE_LEAF(@"SEL$1")
                       1          1          0 FIRST_ROWS(10)
                       1          1          0 OPT_PARAM('optimizer_index_cost_adj' 65)
                       1          1          0 OPT_PARAM('optimizer_dynamic_sampling' 0)
                       1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
                       1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

              7 rows selected.

               

              select name, owner, category, used, to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') created from dba_outlines;

              NAME                                OWNER                          CATEGORY                       USED   CREATED

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

              SYS_OUTLINE_13100417531513606       IMANAGE                        IMANAGE_DIT_TRUSTEE            USED   04-OCT-2013 17:53:15

               

              Also, below query shows that it is in use:

               

              SQL> SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE s.sql_id='ah8nzqhyu7xh4';

              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              SQL_ID  ah8nzqhyu7xh4, child number 0
              -------------------------------------
              SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS,
              imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN,
              imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRUSTEE.HOME_LIBRARY,
              imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED,
              imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011,
              imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME,
              imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015,
              imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME,
              imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID,
              imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME,
              imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024,
              imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID,
              imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED,
              imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DI

              Plan hash value: 1657249878

              -------------------------------------------------------------------------------------------------
              | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
              -------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |                  |       |       |     9 (100)|          |
              |   1 |  SORT ORDER BY               |                  |     1 |   248 |     9  (12)| 00:00:01 |
              |*  2 |   TABLE ACCESS BY INDEX ROWID| DIT_TRUSTEE      |     1 |   248 |     8   (0)| 00:00:01 |
              |*  3 |    INDEX RANGE SCAN          | IX_DIT_TRUSTEE_6 |    11 |       |     2   (0)| 00:00:01 |
              -------------------------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(UPPER("DIT_TRUSTEE"."RDN")=:1)
                 3 - access("DIT_TRUSTEE"."WORKSPACE_RSID"=:SYS_B_0)

              Note
              -----
                 - outline "SYS_OUTLINE_13100417531513606" used for this statement


              38 rows selected.

               

              All above is normal behaviour. Now, I will look at my logs to explain to you technically, what I asked earlier in my post.

               

              Message was edited by: AnjumShehzad

               

              I just ran again DBMS_XPLAN and it gave following output:

               

              SQL> SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE s.sql_id='ah8nzqhyu7xh4';


              PLAN_TABLE_OUTPUT
              --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              SQL_ID  ah8nzqhyu7xh4, child number 0
              -------------------------------------
              SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS,
              imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN,
              imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRUSTEE.HOME_LIBRARY,
              imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED,
              imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011,
              imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME,
              imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015,
              imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME,
              imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID,
              imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME,
              imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024,
              imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID,
              imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED,
              imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DI

              Plan hash value: 1657249878

              -------------------------------------------------------------------------------------------------
              | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
              -------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |                  |       |       |     9 (100)|          |
              |   1 |  SORT ORDER BY               |                  |     1 |   248 |     9  (12)| 00:00:01 |
              |*  2 |   TABLE ACCESS BY INDEX ROWID| DIT_TRUSTEE      |     1 |   248 |     8   (0)| 00:00:01 |
              |*  3 |    INDEX RANGE SCAN          | IX_DIT_TRUSTEE_6 |    11 |       |     2   (0)| 00:00:01 |
              -------------------------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(UPPER("DIT_TRUSTEE"."RDN")=:1)
                 3 - access("DIT_TRUSTEE"."WORKSPACE_RSID"=:SYS_B_0)

              Note
              -----
                 - outline "SYS_OUTLINE_13100417531513606" used for this statement

              SQL_ID  ah8nzqhyu7xh4, child number 2

              SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS,
              imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN,
              imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRUSTEE.HOME_LIBRARY,
              imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED,
              imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011,
              imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME,
              imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015,
              imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME,
              imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID,
              imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME,
              imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024,
              imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID,
              imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED,
              imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DI

              NOTE: cannot fetch plan for SQL_ID: ah8nzqhyu7xh4, CHILD_NUMBER: 2
                    Please verify value of SQL_ID and CHILD_NUMBER;
                    It could also be that the plan is no longer in cursor cache (check v$sql_plan)


              59 rows selected.

               

              The NOTE at the end is interesting.

              • 4. Re: Stored Outlines & Restore Table Stats
                Jonathan Lewis

                Stored outlines are not as accurate as SQL Baselines, and it is possible to generate a stored outline which is NOT a complete outline for a plan, and still find that the query uses the desired execution plan for some time.  Off the top of my head the PUSH_SUBQ hint doesn't get echoed into a stored outline until 10.2.0.5 - so if your plan uses subquery pushing when it was working well you could find that a change of statistics could still result in a change of plan even when the outline was used.

                 

                Can you show us three things:

                a) the old plan - including the Notes line from dbms_xplan.display_cursor() that tells us that the outline was being used, and the outline section

                b) the new plan - including the Notes line from dbms_xplan.display_cursor() that tells us whether or not the outline was being used, and the outline section

                c) the set of hints stored as the outline.  (select hint from user_outline_hints where name = {the name of the outline you find in user_outlines}

                 

                To get the outline section from dbms_xplan.display, use the call: dbms_explan.display_cursor(sql_id, child_number, 'outline'));

                 

                Regards

                Jonathan Lewis

                • 5. Re: Stored Outlines & Restore Table Stats
                  Jonathan Lewis


                  You supplied some of the information before I finished typing my reply.

                   

                  The index hint in the outline is incomplete - can you "set long 2000" and rerun your query against dba_outline_hints so we can get the full text of that hint.  Am I right in assuming the index IX_DIT_TRUSTEE_6 is the one you don't want Oracle to use and that there is a more appropriate index that should be used ?  Can you give use the full definition that index, and the index that you want Oracle to use.

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: Stored Outlines & Restore Table Stats
                    AnjumShehzad

                    COLUMN hint FORMAT A70
                    set long 2000
                    SELECT node, stage, join_pos, hint  FROM dba_outline_hints WHERE name = 'SYS_OUTLINE_13100417531513606';

                          NODE      STAGE   JOIN_POS HINT
                    ---------- ---------- ---------- ------------------------------------------------------------------------------------------
                             1          1          1 INDEX_RS_ASC(@"SEL$1" "DIT_TRUSTEE"@"SEL$1" ("DIT_TRUSTEE"."WORKSPACE_RSID" "DIT_TRUSTEE".
                                                     "DIT_CLASS" "DIT_TRUSTEE"."SID"))

                             1          1          0 OUTLINE_LEAF(@"SEL$1")
                             1          1          0 FIRST_ROWS(10)
                             1          1          0 OPT_PARAM('optimizer_index_cost_adj' 65)
                             1          1          0 OPT_PARAM('optimizer_dynamic_sampling' 0)
                             1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
                             1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

                    7 rows selected.

                     

                    I want Oracle to use IX_DIT_TRUSTEE_6 and is being used by outline. I need to look at the logs of Friday to understand the index it started using despite the fact that outline was in place. FYI, at the moment it is using correct index i.e. IX_DIT_TRUSTEE_6 because I restored stats of "04-OCT-13 09.55.46.076290 +01:00" while outline was created on "04-OCT-2013 17:53:15"

                    • 7. Re: Stored Outlines & Restore Table Stats
                      Jonathan Lewis

                      And what are the definitions of the various indexes on the table ?

                       

                      There's a bit of an inconsistency between your description and the plan shown, by the way.

                      In your SQL you have:

                       

                      WHERE ((imanage.DIT_TRUSTEE.WORKSPACE_RSID = 1665798)

                      AND (UPPER(imanage.DIT_TRUSTEE.RDN) = 'POLICY ADMINISTRATORS'))

                       

                      but the predicate section of the good plan shows

                       

                        2 - filter(UPPER("DIT_TRUSTEE"."RDN")=:1)

                        3 - access("DIT_TRUSTEE"."WORKSPACE_RSID"=:SYS_B_0)

                       

                      The predicate section says that the RDN was supplied as a bind variable, while the RSID was supplied as a literal value that was converted to a bind variable because of your setting for cursor_sharing. Which option matches the content of dba_stored_outlines ?

                       

                      (I didn't mention it earlier, by the way, but your example looks far too simple for it to be a case of a missing hint in the outline.)

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Stored Outlines & Restore Table Stats
                        AnjumShehzad

                        Hi,

                         

                        The query against SQL_ID=ah8nzqhyu7xh4 is:

                         

                        SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS, imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN, imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRUSTEE.HOME_LIBRARY, imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED, imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011, imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME, imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015, imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME, imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID, imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME, imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024, imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID, imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED, imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DIT_TRUSTEE.S_CCSSBL_LBRRY_LST_NHRT_F60326, imanage.DIT_TRUSTEE.NAME_FIRST, imanage.DIT_TRUSTEE.NAME_MIDDLE, imanage.DIT_TRUSTEE.NAME_LAST, imanage.DIT_TRUSTEE.NAME_FULL, imanage.DIT_TRUSTEE.NAME_SUFFIX, imanage.DIT_TRUSTEE.NAME_SALUTATION, imanage.DIT_TRUSTEE.GENDER, imanage.DIT_TRUSTEE.JOB_TITLE, imanage.DIT_TRUSTEE.ANNIVERSARY, imanage.DIT_TRUSTEE.BIRTHDATE, imanage.DIT_TRUSTEE.ASSISTANT_RSID, imanage.DIT_TRUSTEE.LOCALE_LANGUAGE, imanage.DIT_TRUSTEE.LOCALE_COUNTRY, imanage.DIT_TRUSTEE.LOCALE_TIMEZONE, imanage.DIT_TRUSTEE.EMAIL_1, imanage.DIT_TRUSTEE.EMAIL_2, imanage.DIT_TRUSTEE.EMAIL_3, imanage.DIT_TRUSTEE.EMAIL_FORMAT_1, imanage.DIT_TRUSTEE.EMAIL_FORMAT_2, imanage.DIT_TRUSTEE.EMAIL_FORMAT_3, imanage.DIT_TRUSTEE.IM_ID, imanage.DIT_TRUSTEE.PHONE_HOME, imanage.DIT_TRUSTEE.PHONE_BUSINESS, imanage.DIT_TRUSTEE.PHONE_MOBILE, imanage.DIT_TRUSTEE.PHONE_PAGER, imanage.DIT_TRUSTEE.PHONE_OTHER, imanage.DIT_TRUSTEE.FAX_HOME, imanage.DIT_TRUSTEE.FAX_BUSINESS, imanage.DIT_TRUSTEE.FAX_OTHER, imanage.DIT_TRUSTEE.MAILING_ADDRESS, imanage.DIT_TRUSTEE.BA_DEPARTMENT, imanage.DIT_TRUSTEE.BA_LOCATION, imanage.DIT_TRUSTEE.BA_COMPANY, imanage.DIT_TRUSTEE.BA_STREET_1, imanage.DIT_TRUSTEE.BA_STREET_2, imanage.DIT_TRUSTEE.BA_POBOX, imanage.DIT_TRUSTEE.BA_CITY, imanage.DIT_TRUSTEE.BA_STATE, imanage.DIT_TRUSTEE.BA_COUNTRY, imanage.DIT_TRUSTEE.BA_POSTAL_CODE, imanage.DIT_TRUSTEE.HA_STREET_1, imanage.DIT_TRUSTEE.HA_STREET_2, imanage.DIT_TRUSTEE.HA_POBOX, imanage.DIT_TRUSTEE.HA_CITY, imanage.DIT_TRUSTEE.HA_STATE, imanage.DIT_TRUSTEE.HA_COUNTRY, imanage.DIT_TRUSTEE.HA_POSTAL_CODE, imanage.DIT_TRUSTEE.WP_HOME, imanage.DIT_TRUSTEE.WP_BUSINESS, imanage.DIT_TRUSTEE.WP_OTHER, imanage.DIT_TRUSTEE.NE_DELIVERY_MODE_IMMEDIATE, imanage.DIT_TRUSTEE.NE_DELIVERY_MODE_SUMMARY, imanage.DIT_TRUSTEE.NE_USER_PREFERENCE, imanage.DIT_TRUSTEE.NE_USER_SUMMARY_TIME, imanage.DIT_TRUSTEE.NE_COPY_TO_ASSISTANT, imanage.DIT_TRUSTEE.IS_EXTERNAL, imanage.DIT_TRUSTEE.IS_IS_EXTERNAL_INHERITED, imanage.DIT_TRUSTEE.NOTES, imanage.DIT_TRUSTEE.LOCATION_RSID, imanage.DIT_TRUSTEE.IS_ALL_IP_VALID, imanage.DIT_TRUSTEE.LAST_LOGON_TIME, imanage.DIT_TRUSTEE.S_HM_PG_WRKSPC_NHR_F4294966291, imanage.DIT_TRUSTEE.S_PRFRNCS_FCLTY_NH_F4294966292, imanage.DIT_TRUSTEE.HOME_PAGE_WORKSPACE, imanage.DIT_TRUSTEE.PREFERENCES_FACILITY, imanage.DIT_TRUSTEE.P_NEW_ITEM_INDICATOR, imanage.DIT_TRUSTEE.P_PWD_QUESTION, imanage.DIT_TRUSTEE.P_PWD_ANSWER, imanage.DIT_TRUSTEE.X_ECF_USER, imanage.DIT_TRUSTEE.X_DEPARTMENTS, imanage.DIT_TRUSTEE.X_TP_CLASSIFICATION, imanage.DIT_TRUSTEE.X_IMR_ENABLED_TIME, imanage.DIT_TRUSTEE.X_CAN_MANAGE_COI, imanage.DIT_TRUSTEE.X_CAN_ADD_THIRD_PARTY, imanage.DIT_TRUSTEE.X_IS_SECURITY_ADMINISTRATOR, imanage.DIT_TRUSTEE.X_IS_A_AND_S_DIRECT_LOAD, imanage.DIT_TRUSTEE.X_IS_ECF_DIRECT_LOAD, imanage.DIT_TRUSTEE.X_IS_NATIVE_REPOSITORY, imanage.DIT_TRUSTEE.X_CLIENT_CATEGORY, imanage.DIT_TRUSTEE.X_IS_ECF2_USER, imanage.DIT_TRUSTEE.X_IS_ECF2_ADMIN, imanage.DIT_TRUSTEE.X_CWS_UNIT, imanage.DIT_TRUSTEE.X_XCHANGING_ID, imanage.DIT_TRUSTEE.X_IS_DOCUMENT_FILE_VIEWER, imanage.DIT_TRUSTEE.X_IS_CWS_USER

                        FROM imanage.DIT_TRUSTEE

                        WHERE ((imanage.DIT_TRUSTEE.WORKSPACE_RSID = :"SYS_B_0") AND (UPPER(imanage.DIT_TRUSTEE.RDN) = :1)) ORDER BY imanage.DIT_TRUSTEE.NAME_FIRST ASC, imanage.DIT_TRUSTEE.NAME_LAST ASC, imanage.DIT_TRUSTEE.UID_F21024 ASC, imanage.DIT_TRUSTEE.RDN ASC

                         

                        which shows the bind vars in place.

                         

                        while definition of index IX_DIT_TRUSTEE_6 is:

                        CREATE INDEX "IMANAGE"."IX_DIT_TRUSTEE_6" ON "IMANAGE"."DIT_TRUSTEE" ("WORKSPACE_RSID", "DIT_CLASS", "SID") TABLESPACE "REPOS_INDX" PCTFREE 10INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 512K BUFFER_POOL DEFAULT) LOGGING LOCAL

                         

                        Now, I below details explaining the index it started using despite outline in place:

                         

                        SQL> select sql_id, HASH_VALUE, CHILD_NUMBER, users_executing from v$sql where PLAN_HASH_VALUE=2563299851;

                        SQL_ID        HASH_VALUE CHILD_NUMBER USERS_EXECUTING
                        ------------- ---------- ------------ ---------------
                        ah8nzqhyu7xh4 1034155524            1               0

                        SQL> SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_id='ah8nzqhyu7xh4';

                        HASH_VALUE CHILD_NUMBER
                        ---------- ------------
                        SQL_TEXT
                        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        1034155524            1
                        SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS, imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN, imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRU
                        STEE.HOME_LIBRARY, imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED, imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011, imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.D
                        IT_TRUSTEE.MODIFIED_TIME, imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015, imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME, imanage.DIT
                        _TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID, imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME, imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUS
                        TEE.UID_F21024, imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID, imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED, imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DI


                        SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ah8nzqhyu7xh4',1));

                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        SQL_ID  ah8nzqhyu7xh4, child number 1
                        -------------------------------------
                        SELECT imanage.DIT_TRUSTEE.CREATED_TIME, imanage.DIT_TRUSTEE.DIT_CLASS,
                        imanage.DIT_TRUSTEE.DOES_PASSWORD_EXPIRE, imanage.DIT_TRUSTEE.DOMAIN,
                        imanage.DIT_TRUSTEE.FORCE_PASSWORD_CHANGE, imanage.DIT_TRUSTEE.HOME_LIBRARY,
                        imanage.DIT_TRUSTEE.IS_DELETED, imanage.DIT_TRUSTEE.IS_ENABLED,
                        imanage.DIT_TRUSTEE.IS_LEAF_NODE, imanage.DIT_TRUSTEE.LABEL_F21011,
                        imanage.DIT_TRUSTEE.LOGIN_LOCKOUT_TIME, imanage.DIT_TRUSTEE.MODIFIED_TIME,
                        imanage.DIT_TRUSTEE.PARENT_TRUSTEE_RSID, imanage.DIT_TRUSTEE.PASSWORD_F21015,
                        imanage.DIT_TRUSTEE.PASSWORD_FAIL_COUNT, imanage.DIT_TRUSTEE.PASSWORD_MODIFIED_TIME,
                        imanage.DIT_TRUSTEE.RDN, imanage.DIT_TRUSTEE.REPLICATION_ID, imanage.DIT_TRUSTEE.SID,
                        imanage.DIT_TRUSTEE.SYNCHRONIZE_ID, imanage.DIT_TRUSTEE.SYNCHRONIZED_TIME,
                        imanage.DIT_TRUSTEE.TRUSTEE_TYPE, imanage.DIT_TRUSTEE.UID_F21024,
                        imanage.DIT_TRUSTEE.WORKSPACE_LIBRARY, imanage.DIT_TRUSTEE.WORKSPACE_RSID,
                        imanage.DIT_TRUSTEE.IS_WORKSPACE_LIBRARY_INHERITED,
                        imanage.DIT_TRUSTEE.IS_HOME_LIBRARY_INHERITED, imanage.DI

                        Plan hash value: 2563299851

                        -----------------------------------------------------------------------------------------------------
                        | Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                        -----------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT             |                      |       |       |     4 (100)|          |
                        |   1 |  SORT ORDER BY               |                      |     1 |   248 |     4  (25)| 00:00:01 |
                        |*  2 |   TABLE ACCESS BY INDEX ROWID| DIT_TRUSTEE          |     1 |   248 |     3   (0)| 00:00:01 |
                        |*  3 |    INDEX RANGE SCAN          | DIT_TRUSTEE_TST1_IDX |     1 |       |     2   (0)| 00:00:01 |
                        -----------------------------------------------------------------------------------------------------

                        Predicate Information (identified by operation id):
                        ---------------------------------------------------

                           2 - filter("DIT_TRUSTEE"."WORKSPACE_RSID"=:SYS_B_0)
                           3 - access("DIT_TRUSTEE"."SYS_NC00096$"=:1)

                        Note
                        -----
                           - outline "SYS_OUTLINE_13100417531513606" used for this statement


                        38 rows selected.

                         

                        Definition of index DIT_TRUSTEE_TST1_IDX is:

                         

                        CREATE INDEX "IMANAGE"."DIT_TRUSTEE_TST1_IDX" ON "IMANAGE"."DIT_TRUSTEE" (UPPER("RDN")) TABLESPACE "REPOS_INDX" PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE ( INITIAL 512K BUFFER_POOL DEFAULT) LOGGING LOCAL

                         

                        I didnt want the query to use index DIT_TRUSTEE_TST1_IDX as it performs bad for the customer as compare to index IX_DIT_TRUSTEE_6

                         

                        The question arises here is that can we force an index to be used as part of stored outline because when we created outline, it was using index IX_DIT_TRUSTEE_6.

                        • 9. Re: Stored Outlines & Restore Table Stats
                          Jonathan Lewis

                          I think you've found a bug.

                           

                          I've just created a test with a query like this:

                          select * from t1 where n1 = :b1 and n1 + n2 = 30;

                           

                          I've created an index on n1 and generated an outline and shown that it will be used (as reported by the notes from v$sql_plan)

                          Then I've dropped the index on n1 and created an index on (n1 + n2) and re-run the query.

                          According to dbms_xplan.display_cursor() the outline was used - even though the index didn't exist and the new index was used instead.

                           

                          This may simply mean that someone had temporarily dropped (or rearranged the order of the columns) in the index you wanted Oracle to use.

                           

                          I ran the test on 10.2.0.5.

                          Regards

                          Jonathan Lewis

                          • 10. Re: Stored Outlines & Restore Table Stats
                            AnjumShehzad

                            I rebuilt online all the indexes on DIT_TRUSTEE table. Could that be the reason that optimizer said it is using the stored outline but using another index?

                            • 11. Re: Stored Outlines & Restore Table Stats
                              Jonathan Lewis

                              AnjumShehzad wrote:

                               

                              I rebuilt online all the indexes on DIT_TRUSTEE table. Could that be the reason that optimizer said it is using the stored outline but using another index?

                              I don't think so - at least not if you were simply running; "alter index XXX rebuild".  An index that is referenced in an outline when it is unusable will cause an Oracle error (ORA-xxxxx index or partition thereof is unusable ... I forget the exact details).

                               

                              If you had run scripts to drop and recreate the indexes then perhaps there was a missing index that you recreated without realising it had temporarily gone missing.

                               

                              It has crossed my mind that this isn't a bug, though - it may be expected behaviour.  You can drop all the objects associated with an outline, and the outline doesn't drop. In fact the same outline may be used by two different schemas to have an effect even if the table and index definitions are different (because that was one of the ways you hacked stored outlines in the past).

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Stored Outlines & Restore Table Stats
                                AnjumShehzad

                                I rebuilt all indexes for table using rebuild online:

                                 

                                loop

                                    case c.index_type

                                      when 'NORMAL' then

                                        dbms_output.put_line('About to Rebuild ' || c.owner || '.' || c.index_name);

                                         execute immediate 'alter index '|| c.owner || '.' || c.index_name || ' rebuild online';

                                      when 'DOMAIN' then

                                        dbms_output.put_line('About to Rebuild ' || c.index_name);

                                        execute immediate 'alter index '|| c.owner || '.' || c.index_name || ' rebuild online parameters(''' || c.parameters || ''')';

                                    end case;

                                  end loop;

                                 

                                At no point, they were droppped.