My colleague Peter Kramsu came to me and wanted to discuss an interesting problem. This is a summary of his findings.
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 220.127.116.11.0 and it is running on Redhat Linux.
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
This problem is introduced in 10.2.0.5 and 18.104.22.168 .
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
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 22.214.171.124.0 that we are running.
So we continued to search on Oracle support.
We found BUG 12660900
Some statements show high version counts
Reasons for high version counts ( Note 438755.1):
– AUTH_CHECK_MISMATCH :193
– INSUFF_PRIVS :193
– ROLL_INVALID_MISMATCH :191
– Each execution is causing a new child cursor.
– Cursor is not invalidated
– optimizer environment is identical for each execution
– optimizer_secure_view_merging is set to TRUE
– Fix for bug 11930680 is installed.
RELATED BUGS: ------------- 11930680 This was interesting, it referenced the previous bug that we first found interesting at and it also had more solutions to the problem. The suggested solution in the bug (except disable secure_view_merging) was Grant MERGE ANY VIEW to SODSCSWEB the cursor is getting shared even when optimizer_secure_view_merging is set to true. Peter tried to grant MERGE ANY VIEW to the user, flushed the shared pool and reran the query. Voilà... it worked nicely. So why did this work, this is my conclusion: What does SECURE_VIEW_MERGING do ? The Oracle Documentation says: OPTIMIZER_SECURE_VIEW_MERGING enables the optimizer to use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of the view creator. Values: false Oracle does not perform security checks that may prevent view merging and predicate move-around. true Oracle performs checks to ensure that view merging and predicate move-around do not violate any security intentions of the view creator. The owner could perform some kind of merge without violating security since it owns the object (view). The "other" user is violating security if it should perform the same merge as the owner (INSUFF_PRIVS) What about AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor The user does not have permission to access the object in any previous version of the cursor. A typical example would be where each user has it's own copy of a table In this case I believe that the merge done by the owner is not accessible for the "other" user since it hasn't the privileges to create the object (merged view) created by the owner. Therefore we saw the combination of the two reasons INSUFF_PRIVS and AUTH_CHECK_MISMATCH. With the MERGE ANY VIEW privileges it was able to reuse the cursor. Solution to the problem: Grant MERGE ANY VIEW to the user with poor plan References: BUG 12660900, BUG 11930680, Oracle® Database Reference