不同使用者同樣環境同樣的SQL執行計劃不共享問題(AUTH_CHECK_MISMATCH)

Cursor not shared for different users

My colleague Peter Kramsu came to me and wanted to discuss an interesting problem. This is a summary of his findings.
 
Problem Details:
 
Same query executed by two different users where the query for the owner/creator of the data ran quick and for the other users the query was slow.
 We saw two different execution plans, the owner of the tables/views had the better plan and the other user had the poor plan.
 
The other user accessed the views using synonyms.
 
The version of the database is 11.2.0.3.0 and it is running on Redhat Linux.
 


Analyze:
 
We checked the view V$SQL_SHARED_CURSOR to see if we could get some information why the cursor wasn’t shared
 select * from v$sql_shared_cursor where sql_id=’fbryy8xmxc51z’;
 
We could see that there were some columns that differed:
 AUTH_CHECK_MISMATCH  (Y|N) Authorization/translation check failed for the existing child cursor
 INSUFF_PRIVS                     (Y|N) Insufficient privileges on objects referenced by the existing child cursor
 
We searched My Oracle Support and found BUG 11930680
 
Description
 
This problem is introduced in 10.2.0.5 and 11.2.0.2 .
 
If optimizer_secure_view_merging is enabled then some SQL statements may
 not be shared due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS even if the
 SQL is issued repeatedly by the same user. This can cause excess shared
 pool memory use and other contention issues due to the high child cursor
 count.
 
Workaround
 The only workaround is to set optimizer_secure_view_merging=false
 which may not be acceptable in many cases
 
This bug talked about the same user and we had different users and also it was listed as fixed for 11.2.0.3.0 that we are running.
 So we continued to search on Oracle support.