Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
comp.databases.oracle.server
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion CBO & different execution plans
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Matthias Hoys  
View profile  
 More options Apr 7 2007, 10:33 am
Newsgroups: comp.databases.oracle.server
From: "Matthias Hoys" <a...@spam.com>
Date: Sat, 7 Apr 2007 16:33:29 +0200
Local: Sat, Apr 7 2007 10:33 am
Subject: Re: CBO & different execution plans
Print | View thread | Show original | Report this message | Find messages by this author

"Matthias Hoys" <a...@spam.com> wrote in message

news:45ef1abf$0$2940$ba620e4c@news.skynet.be...

> Hello,

> Oracle 10.2.0.3.0 64-bit on RHEL 4
> optimizer_index_caching = 80
> optimizer_index_cost_adj = 20
> optimizer_mode = FIRST_ROWS, statistics have been gathered (with
> histograms).

> Today I noticed the following strange CBO behaviour :

> User A is the owner of a number of objects of a third-party application.
> User B has access to the objects of user A through role grants and public
> synonyms.

> The problem is that certain queries run fast with user A (the schema
> owner), while they run slow with user B (and all other users with access
> to the application). However, this only happens when the system
> optimizer_mode = FIRST_ROWS ! When I change it to CHOOSE, there are no
> performance differences. It looks like, with an optimizer_mode of
> FIRST_ROWS,  the CBO chooses different execution plans based on the user
> who is executing the queries ??
> There are no logon triggers, different user profiles or objects with the
> same name as the public synonyms ...

> Has anyone else seen this behaviour ? I haven't found the time yet to do a
> 10053 trace, the application is rather complex and generates a large
> amount of queries. For now, I changed the optimizer_mode to CHOOSE, but I
> want to keep FIRST_ROWS for a number of other applications in the same
> database ...

> Matthias

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2012 Google