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_mismatchreason which, in this particular case, manifests itself because of the AMERICAN-FRENCHlanguage 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.
Start the discussion at forums.toadworld.com