On google group on Oracle server (http://groups.google.com/group/comp.databases.oracle.server) i’ve seen this:
“By accident, I found the solution for this problem on MetaLink :
Bug 4652274 – Explain Plan Differs With Different Users
It has to do with the init parameter secure_view_merging, which is new since
10gR2. Setting it to FALSE in the spfile and boucing the instance resolved
all my problems … Now queries on view from another schema have the same
exection plans when executed by the view owner compared to another user.
Matthias”
I’ve noticed this question because last person to write was Jonathan Lewis. The problem arised seems bad, Oracle in his note says that’s not a bug but an expected behaviour. Since my memory is not so good i’ve decided immediatly to write this here.
P.S. (20/08/2007)
See comments for correct parameter name, thanks to David Jeffrey
August 17, 2007 at 2:42 pm
The parameter name is optimizer_secure_view_merge set to FALSE
Fixes same issue in 10.2.0.3 also.
August 20, 2007 at 7:02 am
Thank you david for your note.
December 17, 2008 at 2:11 am
[...] variable called optimizer_secure_view_merging and when I searched on Google, I came across http://oracledb.wordpress.com/2007/04/10/execution-plans-differents-with-different-users/, which talks about something similar. Now, interestingly, if you run the bde_chk_cbo.sql script [...]