Execution Plans differents with different Users (?)

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

About these ads

3 Responses to “Execution Plans differents with different Users (?)”

  1. David Jeffrey Says:

    The parameter name is optimizer_secure_view_merge set to FALSE

    Fixes same issue in 10.2.0.3 also.

  2. oracledb Says:

    Thank you david for your note.

  3. 10g optimizer case study: Runtime Execution issues with View merging (Experiments from the Field..Based on True Stories) Says:

    [...] 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 [...]


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: