Re: Slow query against ALL_OBJECTS view

"Shujda" <nlalovic_at_comcast.net> wrote in message news:<riednUmBnL_cIsyjXTWcrg_at_comcast.com>...
> Hello everyone,
> I have about 700 users in database and about 300,000 rows in all_objects
> table/view.
> More then 90% of the objects in the database are the private synonyms. The
> average user has about 450 private synonyms.
> When I logon to database, and execute the query:
> SELECT username FROM all_users WHERE EXISTS(SELECT 'X' FROM all_objects
> WHERE owner = username), I get the results within 3-4 seconds. When I logon
> as someone else, the same query takes forever to execute (more then 30
> minutes). The difference between me and the other user is that I have
> connect, resource and dba roles and he has connect and resource only. When I
> grant him dba role, the same query gets executed very fast. When I revoke
> it, it becomes slow again. I know that all_users is a view and that it
> returns different recordset depending on who is logged on, but I didn't know
> that it can be such difference in performance...
> The biggest problem is that this query gets generated by SQL Navigator when
> you click on "Schemas" node, and I can't tune it up.
> What is the trick? How can I make it run faster.
> Did I hit some limits?
> Thanks.
> Shujda

It's not quite clear what exactly you want to receive by your query. User objects? Names of users who have some objects? Depending on that query may vary considerably Received on Thu Feb 20 2003 - 17:36:50 CET