RE: Overhead using a role

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 27 Apr 2004 14:31:03 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AAE9@usahm018.exmi01.exch.eds.com>

There should be no difference in performance when running the query as the owner and as any user with the necessary privileges on the query objects from a role. Make sure that the different users are accessing the same objects.

Joe: select * from table_a sees larry.table_a via private synonym Bob: select * from table_a sees bob.table_a due to ownership

Session level setting of optimizer_mode or other parameters such as sort_area_size could affect the plan and runtimes.

But why are you using the obsolete RBO?

Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Larry Hahn Sent: Tuesday, April 27, 2004 1:32 PM
To: Oracle-L_at_freelists.org
Subject: Overhead using a role

Is there much overhead involved in using roles?

I have a query that runs about 4 minutes using the schema owner account. The same query runs about 50% longer using a userid that is attached to a role that has read any table rights.

I did an Explain Plan on both, but they are exactly the same. We are running under RBO. The database is 8.1.7.3 running on Sun Solaris.

Thanks for any light anyone can shed on this.

Larry

Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Apr 27 2004 - 14:09:57 CDT