Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow query against ALL_OBJECTS view

Re: Slow query against ALL_OBJECTS view

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 18 Feb 2003 08:47:01 -0800
Message-ID: <336da121.0302180847.62406899@posting.google.com>


"Tanel Poder" <tanel@@peldik.com> wrote in message news:<3e521df1$1_1_at_news.estpak.ee>...
> Hi!
>
> One common cause for slow DD queries problem is, that you have analyzed your
> data dictionary?
>

Never ever do that! Read Oracle documentation first! Data dictionary is never to be analyzed.

Use hints to force rule-based optimizer.

> 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 - 10:47:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US