Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow query against ALL_OBJECTS view
Hi!
One common cause for slow DD queries problem is, that you have analyzed your data dictionary?
Tanel.
"Shujda" <nlalovic_at_comcast.net> wrote in message
news:D6udndLlqNAsWcyjXTWcoQ_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
>
>
>
>
Received on Tue Feb 18 2003 - 05:52:44 CST