Why my execution plan has not been shared - Part V

    Jun 13, 2017 10:33:00 AM by Mohamed Houri

    In this fifth installment of this series of articles about the non-sharing reasons that prevent Oracle from using an existing execution plan, we are going to dig into two new supplementary reasons: language_mismatch and auto_check_mismatch. The same investigation strategy used in the previous articles will be applied here again: Engineer a model, provoke a hard parsed execution plan, and decipher a clear explanation of the corresponding non-sharing reason.

     

    LANGUAGE_MISMATCH

    Oracle defines this reason as follows:

    (Y|N) The language handle does not match the existing child cursor

    As we've been accustomed to imprecise definitions of Oracle non-sharing reasons, we are going, here again, to create a model in a 12.1.0.1.0 database release with which we'll force the occurrence of the language_mismatch reason, as the following shows:

    SQL> show parameter nls_language
    PARAMETER_NAME    VALUE
    ----------------- --------
    nls_language      AMERICAN
    SQL> create table t1 as select rownum n1, lpad('x',5) v1
         from dual connect by level <=5;
     
    SQL> select * from t1 order by v1;
     
            N1 V1
    ---------- -----
             1     x
             2     x
             5     x
             4     x
             3     x
     
    SQL> select * from table(dbms_xplan.display_cursor);
     
    SQL_ID  9j1v15t7131av, child number 0
    -------------------------------------
     
    Plan hash value: 2148421099
    -------------------------------------------
    | Id  | Operation          | Name | Rows  |
    -------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |
    |   1 |  SORT ORDER BY     |      |     5 |
    |   2 |   TABLE ACCESS FULL| T1   |     5 |
    -------------------------------------------

    Let's now change the AMERICAN language to FRENCH and have a second call to the same query to see what that does:

    SQL> alter session set nls_language=FRENCH;
     
    SQL> select * from t1 order by v1;
     
            N1 V1
    ---------- -----
             1     x
             2     x
             5     x
             4     x
             3     x
     
    SQL> select * from table(dbms_xplan.display_cursor);
     
    SQL_ID  9j1v15t7131av, child number 1
    -------------------------------------
    -------------------------------------------
    | Id  | Operation          | Name | Rows  |
    -------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |
    |   1 |  SORT ORDER BY     |      |     5 |
    |   2 |   TABLE ACCESS FULL| T1   |     5 |
    -------------------------------------------

    As you can see from the occurrence of cursor child number 1, a new execution plan has been compiled following the language change. If we want to know why this is so, the nonshared script will be of a great help, as shown below:

    SQL> @nonshared 9j1v15t7131av

    Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

    SQL_ID                        : 9j1v15t7131av
    ADDRESS                       : 00007FFA8C4A8DF0
    CHILD_ADDRESS                 : 00007FFA8C4A7940
    CHILD_NUMBER                  : 0
    REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID>
                                    <reason>NLS Settings(0)</reason><size>2x4</size>
                                    <SessionLengthSemantics>0</SessionLengthSemantics>
                                    <CursorLengthSemantics>0</CursorLengthSemantics>
                                    </ChildNode>
    CON_ID                        : 1
    -----------------
    SQL_ID                        : 9j1v15t7131av
    ADDRESS                       : 00007FFA8C4A8DF0
    CHILD_ADDRESS                 : 00007FFA8C4BDAD0
    CHILD_NUMBER                  : 1
    LANGUAGE_MISMATCH             : Y
    REASON                        :
    CON_ID                        : 1
    -----------------

    Notice again how we have effectively succeeded in provoking the language_mismatch reason which, in this particular case, manifests itself because of the AMERICAN-FRENCH language switch we've performed between the first and the second execution of the query.

    It is important as well to note that Oracle is clever enough to recognize situations where this kind of language switch has no chance to impact the query results. This means that an NLS language modification does not always indicate a new execution plan compilation. For example, had we skipped the “order by v1” clause in the above query, or had we ordered the result using the n1 number column, Oracle would not have forced a new execution plan, as the following proves:

    SQL> show parameter nls_language
     
    PARAMETER_NAME    VALUE
    ----------------- --------
    nls_language      FRENCH
     
    SQL> select * from t1 order by n1;
     
            N1 V1
    ---------- -----
             1     x
             2     x
             3     x
             4     x
             5     x
    SQL> select * from table(dbms_xplan.display_cursor);
     
    SQL_ID  1dy3uuak6zxa9, child number 0
    -------------------------------------
    select * from t1 order by n1
     
    Plan hash value: 2148421099
    -------------------------------------------
    | Id  | Operation          | Name | Rows  |
    -------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |
    |   1 |  SORT ORDER BY     |      |     5 |
    |   2 |   TABLE ACCESS FULL| T1   |     5 |
    -------------------------------------------
     
    SQL> alter session set nls_language=AMERICAN;
    SQL> select * from t1 order by n1;
            N1 V1
    ---------- -----
             1     x
             2     x
             3     x
             4     x
             5     x
    SQL> select * from table(dbms_xplan.display_cursor);
    SQL_ID  1dy3uuak6zxa9, child number 0
    -------------------------------------
    select * from t1 order by n1
    Plan hash value: 2148421099
    -------------------------------------------
    | Id  | Operation          | Name | Rows  |
    -------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |
    |   1 |  SORT ORDER BY     |      |     5 |
    |   2 |   TABLE ACCESS FULL| T1   |     5 |
    -------------------------------------------

    Since we are now ordering by an n1, a numerical value, Oracle knows in advance that the language switch we've done will not affect the result of the query. This is why the two query executions shared the same execution plan child number 0.

    Moreover, despite its name, the language_mismatch reason can be due not only to a language change. In fact, it kicks in whenever one of the following NLS parameters values experiences an update that will no longer guarantee the reliability of the result without a new hard-parsed execution plan:

    SQL> show parameter nls
    PARAMETER_NAME           TYPE        VALUE
    ------------------------ ----------- --------------------------
    nls_calendar             string      GREGORIAN
    nls_comp                 string      BINARY
    nls_currency             string      ú
    nls_date_format          string      dd-MON-yy
    nls_date_language        string      ENGLISH
    nls_dual_currency        string      Ç
    nls_iso_currency         string      UNITED KINGDOM
    nls_language             string      AMERICAN
    nls_length_semantics     string      BYTE
    nls_nchar_conv_excp      string      FALSE
    nls_numeric_characters   string      .,
    nls_sort                 string      BINARY
    nls_territory            string      UNITED KINGDOM
    nls_time_format          string      HH24.MI.SSXFF
    nls_time_tz_format       string      HH24.MI.SSXFF TZR
    nls_timestamp_format     string      DD-MON-RR HH24.MI.SSXFF
    nls_timestamp_tz_format  string      DD-MON-RR HH24.MI.SSXFF TZR

     

    AUTH_CHECK_MISMATCH

    Oracle defines this reason as follows:

    (Y|N) Authorization/translation check failed for the existing child cursor

    Oracle is clearly having a great deal of difficulty in giving meaningful definitions for its optimizer non-sharing reasons. How could we get a clue about what this reason means simply through its official definition? But, after all, should this reason have been defined clearly, there would have been no need for this article.

    One of the situations where this non-sharing reason kicks in is when a Row Level Security (see this article for more details about this feature). Let's put it in action, using the above table and the following Row Level security policy (RLS):

    create or replace function

    f_t1_policy(piv_schema in varchar2
               ,piv_object in varchar2)
    return varchar2
    is
      lv_return_value varchar2(4000);
    begin
      if sys_context('USERENV','LANG') = 'US'
      then
        lv_return_value := '1=1';
      else
        lv_return_value := '1=0';
      end if;
       return lv_return_value;
    end f_t1_policy;
    / 
    begin
     dbms_rls.add_policy
       (object_schema    => user,
        object_name      => 'T1',
           policy_name      => 'F_T1_POLICY',
           function_schema    => user,
           policy_function  => 'F_T1_POLICY',
           statement_types  => 'SELECT'
        );
    end;
    /

    The above PL/SQL code creates a function and a row level security policy. The function checks the user session language and applies the RLS policy for non-US users so that they are prevented from selecting data from t1 table. Let's then run a query on table t1 and see what that does:

    SQL> select sys_context('USERENV','LANG') lang from dual;
    LANG
    ----
    US
    SQL> select * from t1;
            N1 V1
    ---------- -----
             1     x
             2     x
             3     x
             4     x
             5     x
    SQL> select * from table(dbms_xplan.display_cursor);
    SQL_ID  27uhu2q2xuu7r, child number 0
    -------------------------------------
    select * from t1 
    Plan hash value: 3617692013
    ------------------------------------------
    | Id  | Operation         | Name | Rows  |
    ------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |
    |   1 |  TABLE ACCESS FULL| T1   |     5 |
    ------------------------------------------

    As you can see, US users can see data coming from table t1. What about non-US users? Let's change the current user language and see if the application is well policed:

    SQL> alter session set nls_language=FRENCH;
    SQL> select * from t1;
    no rows selected

    Spot how the same query with the same sql_id ( 27uhu2q2xuu7r) generates two different outputs depending on the session language of the user executing the query : 5 rows for US users and 0 rows for non-US users. That's exactly how we wanted our RLS policy to work. If the same query returns different results then it certainly means that its SQL text underwent a modification (translation) done by the RLS policy under the hood. Let's see the new execution plan when this policy is applied:

    SQL> select * from table(dbms_xplan.display_cursor);
    SQL_ID  27uhu2q2xuu7r, child number 1
    -------------------------------------
    select * from t1
    -------------------------------------------
    | Id  | Operation          | Name | Rows  |
    -------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |
    |*  1 |  FILTER            |      |       |
    |   2 |   TABLE ACCESS FULL| T1   |     5 |
    -------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(NULL IS NOT NULL)

    Two points are to be emphasized here:

    • The appearance of a predicate n°1 that doesn't exist in the query and which has been injected by the RLS policy
    • Oracle has created a new execution plan ( child number 1) as a result of the RLS policy application

    Applying the nonshared script to the sql_id of the original query confirms that the reason for execution plan’s non-sharing is due to a Row Level Security, which Oracle identifies via the name AUTH_CHECK_MISMATCH.

    SQL> @nonshared 27uhu2q2xuu7r

    Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

    SQL_ID                        : 27uhu2q2xuu7r
    ADDRESS                       : 00007FFA8C3C03F8
    CHILD_ADDRESS                 : 00007FFA8C3BEF48
    CHILD_NUMBER                  : 0
    REASON                        : <ChildNode><ChildNumber>0</ChildNumber><ID>37</ID>
                                    <reason>Authorization Check failed(9)</reason>
                                    <size>0x0</size>
                                    <details>row_level_access_control_mismatch
                                    </details></ChildNode>
    CON_ID                        : 1
    -----------------
    SQL_ID                        : 27uhu2q2xuu7r
    ADDRESS                       : 00007FFA8C3C03F8
    CHILD_ADDRESS                 : 00007FFA8C3A3FA8
    CHILD_NUMBER                  : 1
    AUTH_CHECK_MISMATCH           : Y
    REASON                        :
    CON_ID                        : 1
    -----------------

     

    SUMMARY

    In this article we examined two new reasons from gv$sql_shared_cursor view: language_mismatch and auto_check_mismatch. The first occurs when one of the NLS parameters stored into the existing child cursor metadata undergoes a modification that might affect the execution of the query if run with the existing execution plan. We showed that one of the situations where the second reason can be seen is when a Row Level Security policy is used. For example, Oracle can preempt certain users from reading sensible data by injecting a restriction to the query as dictated by the RLS policy. This is why a new execution plan becomes necessary.

    Tags: Oracle

    Mohamed Houri

    Written by Mohamed Houri