1 2 Previous Next 22 Replies Latest reply on Mar 28, 2018 2:33 PM by 1342554

    Performance instability resulting from changing predicates.

    1342554

      Hello All,

       

      We are running a :

       

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for Solaris: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      

       

      and this morning we had a SQL statement that performed very badly. The statement is very simple, just a count on one table :

       

      SELECT COUNT(DISTINCT(T2.ACT_UID)) FROM T_ACTIVITY_SRC T2 WHERE T2.EP_UID = :B3 AND T2.NAME = :B2 AND T2.ACT_UID != :B1
      

       

      We did a bit of analysis and found out that 2 differents plans existed. At first, we thought that the plan had changed from good to bad and we were about to create a SQL_Profile to force the good plan but we noticed the Plan_Hash_Value of the 2 plans was exactly the same... So no SQL_Profile was possible...

       

      After a bit of searching, the only difference we saw was in the predicate section.

       

      Predicate of the good SQL :

       

        4 - filter("T2"."ACT_UID"<>:B1)
         4 - filter("T2"."ACT_UID"<>:B1)
         4 - filter("T2"."ACT_UID"<>:B1)
         4 - filter("T2"."ACT_UID"<>:B1)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
      

       

      Predicate of the bad SQL :

       

         4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
         4 - filter("T2"."ACT_UID"<>:B1)
         4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
         4 - filter("T2"."ACT_UID"<>:B1)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
         5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
      

       

      After reading a few blogs (like the very good Calors Sierra's blog), we learnt that differences in the predicates section can indeed change a plan (but still keep the the same Plan Hash Value). But unfortunately, we did not find how to fix the issues and our sql is still poorly behaving...

       

      So we have 2 questions :

      1 - Why the predicates change ? As it is exactly the same statement ?

      2 - How could we fix this problem ?

       

      Many thanks...

        • 1. Re: Performance instability resulting from changing predicates.
          AndrewSayer

          Can you please paste the full execution plans using dbms_xplan. The predicates sections you posted don’t make much sense on their own.

           

          The optimizer is free to apply predicates wherever it feels best, it may be that cardinality/statistics feedback kicked in and told it that the index isn’t as useful as it originaly thought but didnt correctly make the adjustments consistently so now thinks that it is cheaper to go to the table and then filter rather than access your index.

           

          If you shared the full plan, with predicate sections and notes we can make some concrete statements.

          1 person found this helpful
          • 3. Re: Performance instability resulting from changing predicates.
            1342554

            Hi Andrew,

             

            Thanks for your answer...

             

            Here is the plan for the good one :

             

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            ------------------------------------------------------------------------------------------------------
            | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
            ------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
            |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
            |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
            |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            ------------------------------------------------------------------------------------------------------
            
            
            Peeked Binds (identified by position):
            --------------------------------------
            
            
               1 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_PUB-176b0b63:160d25adfa1:4b2'
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
               2 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP'
               3 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP-176b0b63:160d25adfa1:4b3'
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
            
               4 - filter("T2"."ACT_UID"<>:B1)
               4 - filter("T2"."ACT_UID"<>:B1)
               4 - filter("T2"."ACT_UID"<>:B1)
               4 - filter("T2"."ACT_UID"<>:B1)
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
            
            
            Note
            -----
               - Warning: basic plan statistics not available. These are only collected when:
                   * hint 'gather_plan_statistics' is used for the statement or
                   * parameter 'statistics_level' is set to 'ALL', at session or system level
            

             

            And the plan for the bad one :

             

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            SQL_ID  5wkwbxy315wcv, child number 2
            -------------------------------------
            An uncaught error happened in prepare_sql_statement : ORA-01422: l'extraction exacte ram▒ne plus que le nombre de lignes demand▒
            
            
            Plan hash value: 729219313
            
            
            ------------------------------------------------------------------------------------------------------
            | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
            ------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   0 | SELECT STATEMENT               |                         |        |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
            |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
            |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
            |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
            |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
            |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
            |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
            ------------------------------------------------------------------------------------------------------
            
            
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            Peeked Binds (identified by position):
            --------------------------------------
            
            
               1 - (VARCHAR2(30), CSID=178): 'null-3a7fb934:160daa8e2d5:6ec9'
               2 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_JDBCA'
               3 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_COMP-3a7fb934:160daa8e2d5:6ec7'
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
            
               4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
            
            
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
               4 - filter("T2"."ACT_UID"<>:B1)
               4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
               4 - filter("T2"."ACT_UID"<>:B1)
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
               5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
            
            
            Note
            -----
               - Warning: basic plan statistics not available. These are only collected when:
                   * hint 'gather_plan_statistics' is used for the statement or
                   * parameter 'statistics_level' is set to 'ALL', at session or system level
            
            • 4. Re: Performance instability resulting from changing predicates.
              AndrewSayer

              Are you sure all you did was

              select * from table(dbms_xllan.display_cursor(sql_id=>’The sqid you care about’));

               

              The output doesnt make sense, its many plans mashed together. Ive seen this happen before but only when multiple plans shared the same child cursor number (it used to loop at 99) but you only have two childcursors from What you’ve said.

              • 5. Re: Performance instability resulting from changing predicates.
                Jonathan Lewis

                It looks like you must be generating (and losing) lots of child cursors for the same statement thanks to the number of different input values for your character columns. The plans you've produced are actually superimposed plans from multiple (possibly "obsoleted") cursors.  Basically, though, you seem to have two execution plans, one with a full tablescan and one with an index range scan.

                 

                It's possible that your problem is due to having character values that match to a significant number of leading characters - Oracle can easily get confused when character strings match up to the first 6 (slightly more in 12c).

                 

                I'd guess there's are histograms on the ep_uid and name columns, and you probably need to get rid of them, or construct and lock histograms that will safely model the data. Oracle takes a small small in 11g for histograms and this can lead to terrible instability/

                 

                Regards

                Jonathan Lewis

                • 6. Re: Performance instability resulting from changing predicates.
                  1342554

                  Yes, sorry Andrew... I messed up ;-)

                   

                  Actually there are multiples plans, here they are but I think there are too long so they are being cut from the window...

                   

                  Good Plan :

                   

                   

                  eXplain the execution plan for sqlid 5wkwbxy315wcv child 1...
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  SQL_ID  5wkwbxy315wcv, child number 1
                  -------------------------------------
                  An uncaught error happened in prepare_sql_statement : ORA-01422: l'extraction exacte ram▒ne plus que le nombre de lignes demand▒
                  
                  
                  Plan hash value: 729219313
                  
                  
                  ------------------------------------------------------------------------------------------------------
                  | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
                  ------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  ------------------------------------------------------------------------------------------------------
                  
                  
                  Peeked Binds (identified by position):
                  --------------------------------------
                  
                  
                     1 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_PUB-176b0b63:160d25adfa1:4b2'
                     2 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP'
                     3 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP-176b0b63:160d25adfa1:4b3'
                  
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  
                  
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                  
                  
                  Note
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  -----
                     - Warning: basic plan statistics not available. These are only collected when:
                         * hint 'gather_plan_statistics' is used for the statement or
                         * parameter 'statistics_level' is set to 'ALL', at session or system level
                  
                  
                  SQL_ID  5wkwbxy315wcv, child number 1
                  -------------------------------------
                  An uncaught error happened in prepare_sql_statement : ORA-01422: l'extraction exacte ram▒ne plus que le nombre de lignes demand▒
                  
                  
                  Plan hash value: 729219313
                  
                  
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  ------------------------------------------------------------------------------------------------------
                  | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
                  ------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   828K|   828K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   833K|   833K|          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  ------------------------------------------------------------------------------------------------------
                  
                  
                  Peeked Binds (identified by position):
                  --------------------------------------
                  
                  
                     1 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_PUB-176b0b63:160d25adfa1:4b2'
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     2 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP'
                     3 - (VARCHAR2(30), CSID=178): 'PREVCONSO_PREV_CONSO_RECUP-176b0b63:160d25adfa1:4b3'
                  
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                  
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter("T2"."ACT_UID"<>:B1)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                  
                  
                  Note
                  -----
                     - Warning: basic plan statistics not available. These are only collected when:
                         * hint 'gather_plan_statistics' is used for the statement or
                         * parameter 'statistics_level' is set to 'ALL', at session or system level
                  
                  
                  
                  
                  120 rows selected.
                  

                   

                  Bad Plan :

                   

                  SQL>@xi 5wkwbxy315wcv 2
                  eXplain the execution plan for sqlid 5wkwbxy315wcv child 2...
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  SQL_ID  5wkwbxy315wcv, child number 2
                  -------------------------------------
                  An uncaught error happened in prepare_sql_statement : ORA-01422: l'extraction exacte ram▒ne plus que le nombre de lignes demand▒
                  
                  
                  Plan hash value: 729219313
                  
                  
                  ------------------------------------------------------------------------------------------------------
                  | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
                  ------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
                  |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
                  |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  ------------------------------------------------------------------------------------------------------
                  
                  
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  Peeked Binds (identified by position):
                  --------------------------------------
                  
                  
                     1 - (VARCHAR2(30), CSID=178): 'null-3a7fb934:160daa8e2d5:6ec9'
                     2 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_JDBCA'
                     3 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_COMP-3a7fb934:160daa8e2d5:6ec7'
                  
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                  
                     4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
                     4 - filter("T2"."ACT_UID"<>:B1)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                  
                  
                  Note
                  -----
                     - Warning: basic plan statistics not available. These are only collected when:
                         * hint 'gather_plan_statistics' is used for the statement or
                         * parameter 'statistics_level' is set to 'ALL', at session or system level
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  
                  
                  SQL_ID  5wkwbxy315wcv, child number 2
                  -------------------------------------
                  An uncaught error happened in prepare_sql_statement : ORA-01422: l'extraction exacte ram▒ne plus que le nombre de lignes demand▒
                  
                  
                  Plan hash value: 729219313
                  
                  
                  ------------------------------------------------------------------------------------------------------
                  | Id  | Operation                      | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
                  ------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   0 | SELECT STATEMENT               |                         |        |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   1 |  SORT AGGREGATE                |                         |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |    121K|       |       |          |
                  |   2 |   VIEW                         | VW_DAG_0                |      1 |       |       |          |
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
                  |   3 |    HASH GROUP BY               |                         |    121K|   748K|   748K|          |
                  |   3 |    HASH GROUP BY               |                         |      1 |   757K|   757K|          |
                  |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  4 |     TABLE ACCESS FULL          | T_ACTIVITY_SRC          |    121K|       |       |          |
                  |*  4 |     TABLE ACCESS BY INDEX ROWID| T_ACTIVITY_SRC          |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  |*  5 |      INDEX RANGE SCAN          | I0_ACTIVITY_SRC_EP_UID2 |      1 |       |       |          |
                  ------------------------------------------------------------------------------------------------------
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  
                  
                  Peeked Binds (identified by position):
                  --------------------------------------
                  
                  
                     1 - (VARCHAR2(30), CSID=178): 'null-3a7fb934:160daa8e2d5:6ec9'
                     2 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_JDBCA'
                     3 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_COMP-3a7fb934:160daa8e2d5:6ec7'
                  
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                  
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
                     4 - filter("T2"."ACT_UID"<>:B1)
                     4 - filter(("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2 AND "T2"."ACT_UID"<>:B1))
                     4 - filter("T2"."ACT_UID"<>:B1)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                     5 - access("T2"."EP_UID"=:B3 AND "T2"."NAME"=:B2)
                  
                  
                  Note
                  -----
                     - Warning: basic plan statistics not available. These are only collected when:
                         * hint 'gather_plan_statistics' is used for the statement or
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                         * parameter 'statistics_level' is set to 'ALL', at session or system level
                  
                  
                  
                  
                  112 rows selected.
                  
                  
                  
                  
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  Peeked Binds (identified by position):
                  --------------------------------------
                  
                  
                     1 - (VARCHAR2(30), CSID=178): 'null-3a7fb934:160daa8e2d5:6ec9'
                     2 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_JDBCA'
                     3 - (VARCHAR2(30), CSID=178): 'SUPERVISION_CHECK_COMP-3a7fb934:160daa8e2d5:6ec7'
                  
                  
                  Predicate Information (identified by operation id):
                  
                  
                  • 7. Re: Performance instability resulting from changing predicates.
                    1342554

                    Thanks John. I did not think about Bind Variables Peeking...

                    I looked it up and I failing to see right now how I can solve the problem with my actual statement that is running badly... Because I do see if I can change the plan dynamically...

                     

                    Can you give me a hand ?

                     

                    Thanks,

                    • 8. Re: Performance instability resulting from changing predicates.
                      1342554

                      Thanks Jonathan for you input...

                       

                      What I do not get is that since this morning, It appears the be exactly the same SQL statement (with the same bind values) that shows up... Do you think we should kill the sessions related to this query ?

                       

                      Also, we I run this query in SQL PLus with the same bind values, it runs super fast with a good plan...

                       

                      Thanks.

                      • 9. Re: Performance instability resulting from changing predicates.
                        Jonathan Lewis

                        Something is broken in your library cache and you're running 11.2.0.3 which is an out of date version of Oracle, which means it's hard to predict the side effects of any action I might suggest.

                         

                        First - do you have any histograms on the table, if so which columns and when were the stats on that table last collected ?

                        Second - for any combination of ep_uid and name, do you expect to get roughly the same number of rows (before doing the count(distinct)), or can the number of rows vary enormously. If the latter.

                         

                        Killing the session may have no effect because the cursors will still be in memory and could simply be picked up again by the next sessions to start. You might be able to purge the child cursors, though, using dbms_shared_pool.purge(). If this works then the statement will be re-optimised and may pick the plan you want.  My original question about histograms is relevant, though - Oracle has various "self-tuning" strategies including adaptive cursor sharing in your version, but that wouldn't be triggered if there were no histograms on any of the columns in your where clause.

                         

                        Regards

                        Jonathan Lewis

                         

                        P.S.  When you say the statement runs "super fast" from SQL*Plus, are you using literal values to run the statement or are you still running the statement using bind variables that get set by SQL*Plus ? Bear in mind that since the contents of the library cache are messed up  you can't really guarantee which values you're reporting are the ones that give the bad plan (or even that you're reporting values that do give the bad plan.)

                        • 10. Re: Performance instability resulting from changing predicates.
                          Mohamed Houri

                          Hello,

                           

                          Quelque chose me dit que vous êtes dans un pays où la langue de Molière s’y pratique

                           

                          First of all it is not because two execution plans show the same plan_hash_value that they are identical. What you need to check is their phv2

                          SELECT

                                 p.sql_id

                                ,p.plan_hash_value

                                ,p.child_number

                                ,t.phv2

                              FROM

                                 v$sql_plan p

                                ,xmltable('for $i in /other_xml/info

                                            where $i/@type eq "plan_hash_2"

                                           return $i'

                                           passing xmltype(p.other_xml)

                                           columns phv2 number path '/') t

                             WHERE p.sql_id = '&sql_id'

                             AND p.other_xml is not null;

                           

                          You can fix one of them using SQL Plan Management as this technique is based on phv2 (which include the predicate part) rather than on the plan_hash_value.

                           

                          That’s said the ORA-01422 error you have at the top of your execution plan is a symptom of the presence of a high number of execution plans for your query. At your place I would have first checked this:

                           

                          select

                            child_number

                          ,is_bind_sensitive S

                          ,is_bind_aware    A

                          ,is_shareable      C

                          ,executions

                          from gv$sql

                          where sql_id = '&sql_id'

                          and is_shareable = 'Y'

                          order by child_number asc;

                           

                          In order to check whether your cursor is bind aware or not.

                           

                          If your cursor is bind aware and subject to a high number of child cursosr then I would have checked the reason for which those cursors are compiled using Tanel Poder nonshared.sql script

                           

                          If you found that the reason is BIND_EQUIVALENT_FAILURE I would then suggest you either to get rid of the histogram from the columns in your predicate or simply cancelling the ACS (Adaptive Cursor Sharing) either at local level (using /*+ no_bind_aware */) or at global level (using _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = "none")

                           

                          Best regards

                          Mohamed Houri

                          • 11. Re: Performance instability resulting from changing predicates.
                            1342554

                            Bonjour Mohamed,

                             

                            Thanks for your reply.

                             

                            I executed the 2 queries you gave me...

                             

                            Here are the results for the first one :

                             

                            SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
                            ------------- --------------- ------------ ----------
                            5wkwbxy315wcv       729219313           11  132514878
                            5wkwbxy315wcv       729219313           10  132514878
                            5wkwbxy315wcv       729219313            9  132514878
                            5wkwbxy315wcv       729219313            7  132514878
                            5wkwbxy315wcv       729219313            6  132514878
                            5wkwbxy315wcv       729219313            3  132514878
                            5wkwbxy315wcv      2338245028            2  966499171
                            5wkwbxy315wcv       729219313            1  132514878
                            5wkwbxy315wcv       729219313            0  132514878
                            ...
                            5wkwbxy315wcv       729219313            9  132514878
                            5wkwbxy315wcv       729219313            8  132514878
                            5wkwbxy315wcv       729219313            7  132514878
                            5wkwbxy315wcv       729219313            6  132514878
                            5wkwbxy315wcv       729219313            5  132514878
                            5wkwbxy315wcv       729219313            4  132514878
                            5wkwbxy315wcv       729219313            3  132514878
                            5wkwbxy315wcv       729219313            2  132514878
                            5wkwbxy315wcv       729219313            1  132514878
                            5wkwbxy315wcv       729219313            0  132514878
                            

                             

                            The PH2 are identical for almost all the SQL_Id, so does that mean SPM would not be useful in this case ?

                             

                            Results of the second statement :

                             

                            CHILD_NUMBER S A C EXECUTIONS
                            ------------ - - - ----------
                                       0 Y N Y       2544
                                       0 Y N Y      62141
                                       0 Y N Y      62141
                                       1 Y N Y       2711
                                       1 Y N Y    1108086
                                       1 Y N Y    1108086
                                       2 Y N Y     267215
                                       2 Y N Y       6226
                                       2 Y N Y     267215
                                       3 Y N Y       4830
                                       3 Y N Y       5603
                                       3 Y N Y       5603
                                       4 Y N Y       5182
                                       4 Y N Y       5954
                                       5 Y N Y       6246
                                       5 Y N Y       3510
                                       6 Y N Y     119047
                                       6 Y N Y       6421
                                       6 Y N Y       6421
                                       7 Y N Y       6704
                                       7 Y N Y       6704
                                       7 Y N Y        971
                                       8 Y N Y       5000
                                       8 Y N Y       3136
                                       9 Y N Y     172974
                                       9 Y N Y     172974
                                      10 Y N Y       1248
                                      10 Y N Y       1248
                                      11 Y N Y       5988
                                      11 Y N Y       5988
                                      12 Y N Y       3276
                                      13 Y N Y      68964
                                      15 Y N Y       2951
                                      16 Y N Y    3214306
                                      17 Y N Y       3375
                                      19 Y N Y       1069
                                      20 Y N Y        636
                                      22 Y N Y     133904
                                      22 Y N Y      68416
                                      23 Y N Y    1110124
                                      23 Y N Y      78875
                                      27 Y N Y       2928
                                      28 Y N Y      85224
                                      29 Y N Y        684
                                      31 Y N Y       2203
                                      35 Y N Y       3933
                                      36 Y N Y      87644
                                      39 Y N Y       1156
                                      41 Y N Y        659
                                      43 Y N Y       2926
                                      45 Y N Y      47920
                                      46 Y N Y      43003
                                      48 Y N Y       2674
                                      49 Y N Y    3264125
                                      51 Y N Y       2839
                                      52 Y N Y      71655
                                      57 Y N Y       2835
                                      58 Y N Y       3024
                                      61 Y N Y       1654
                                      62 Y N Y       1813
                                      63 Y N Y       1159
                                      64 Y N Y       3344
                                      65 Y N Y       4582
                                      68 Y N Y       4694
                                      75 Y N Y    3745784
                                      77 Y N Y     100633
                                      80 Y N Y       3017
                                      88 Y N Y       4146
                                      89 Y N Y       3640
                                      90 Y N Y       3133
                                      91 Y N Y       3014
                                      93 Y N Y       4902
                                      94 Y N Y       4437
                                      97 Y N Y       4405
                                      99 Y N Y      13477
                            

                             

                            So my cursor is not "Bind Aware" (column A)... Would it be my problem ? How could I make it "Bind Aware" ?

                             

                            Thanks,

                            • 12. Re: Performance instability resulting from changing predicates.
                              Mohamed Houri

                              You still need to find the reason for which Oracle is not sharing those 100 child cursors (0,99). There is a big chance that it is due to Adaptive Cursor sharing

                               

                              You can use Tanel Poder nonshared.sql script for that or :

                               

                              select * from v$sql_shared_cursor where sql_id = '5wkwbxy315wcv';

                               

                              and look for the reason marked with 'Y'

                               

                              Best regards

                              Mohamed

                              1 person found this helpful
                              • 13. Re: Performance instability resulting from changing predicates.
                                jgarry

                                It may also be worth your while to search MOS for terms like: 11.2.0.3 excessive cursors.

                                You may be able to fix the histograms or stabilize the plans as Jonathan, Tanel, Mohamed et al describe, or it may be appropriate to turn off adaptive cursor sharing.  Of course, that is an underscore parameter and should only be done under the guidance of support.

                                1 person found this helpful
                                • 14. Re: Performance instability resulting from changing predicates.
                                  1342554

                                  Mohamed,

                                   

                                  I used Tanel's script but can you elaborate what I need to look for ? What do you mean by the reason marked "Y". Here is some results :

                                   

                                  SQL> @nonshared 5wkwbxy315wcv
                                  Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
                                  
                                  
                                  SQL_ID                        : 5wkwbxy315wcv
                                  ADDRESS                       : 00000006EF87A8E8
                                  CHILD_ADDRESS                 : 00000006EF8796C8
                                  CHILD_NUMBER                  : 0
                                  AUTH_CHECK_MISMATCH           : Y
                                  TRANSLATION_MISMATCH          : Y
                                  ROLL_INVALID_MISMATCH         : Y
                                  REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1517665513</invalidation_window><ksugctm>1517665559</ksugctm></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>99</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check
                                  failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>115</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1515548538</invalidation_window><ksugctm>1515548571</ksugctm></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check
                                  failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>99</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>161</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</reason><size>4x4</size><bind_position>1</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind
                                  mismatch(22)</reason><size>4x4</size><bind_position>0</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</reason><size>4x4</size><bind_position>1</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode>
                                  -----------------
                                  SQL_ID                        : 5wkwbxy315wcv
                                  ADDRESS                       : 00000006EF87A8E8
                                  CHILD_ADDRESS                 : 00000006EF7438F8
                                  CHILD_NUMBER                  : 1
                                  AUTH_CHECK_MISMATCH           : Y
                                  TRANSLATION_MISMATCH          : Y
                                  ROLL_INVALID_MISMATCH         : Y
                                  REASON                        : <ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1516058708</invalidation_window><ksugctm>1516058759</ksugctm></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>115</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind
                                  mismatch(22)</reason><size>4x4</size><bind_position>1</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>128</original_oacmxl><upgradeable_new_oacmxl>32</upgradeable_new_oacmxl></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>115</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1513216758</invalidation_window><ksugctm>1513216787</ksugctm></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode>
                                  -----------------
                                  SQL_ID                        : 5wkwbxy315wcv
                                  ADDRESS                       : 00000006EF87A8E8
                                  CHILD_ADDRESS                 : 00000006EF841870
                                  CHILD_NUMBER                  : 2
                                  AUTH_CHECK_MISMATCH           : Y
                                  TRANSLATION_MISMATCH          : Y
                                  ROLL_INVALID_MISMATCH         : Y
                                  REASON                        : <ChildNode><ChildNumber>2</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1515628479</invalidation_window><ksugctm>1515628499</ksugctm></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>115</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>37</ID><reason>Authorization Check
                                  failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>161</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</reason><size>4x4</size><bind_position>1</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>37</ID><reason>Authorization Check failed(4)</reason><size>5x4</size><translation_table_position>0</translation_table_position><original_handle>6</original_handle><temp_handle>6</temp_handle><schema>99</schema><synonym_object_number>0</synonym_object_number></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind
                                  mismatch(22)</reason><size>4x4</size><bind_position>1</bind_position><original_oacflg>3</original_oacflg><original_oacmxl>32</original_oacmxl><upgradeable_new_oacmxl>128</upgradeable_new_oacmxl></ChildNode>
                                  

                                   

                                  Merci.

                                  1 2 Previous Next