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.