oracle不同用户执行相同sql,不同用户同样环境同样的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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-772489/，如需转载，请注明出处，否则将追究法律责任。