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.

We found BUG 12660900

PROBLEM:
——–
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
Total Versions:193

DIAGNOSTIC ANALYSIS:
——————–
– 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.

WORKAROUND:
———–
set optimizer_secure_view_merging=false

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s