Babu,
Your query is using cost algorithms to access the data
dictionary, and that is why it is so slow. The RULE
hint makes it use rule-based optimization, which is
the way that the data dictionary is supposed to be
accessed.
If you remove all stats from the data dictionary, your
query should be fast again.
If it doesn't, just use the RULE hint in all your data
dictionary queries. Do make sure that ALL of the stats
are gone from your data dictionary tables.
hth,
Jack
- Janardhana Babu Donga <jbdonga_at_ucdavis.edu> wrote:
> Jack,
>
> Output from show parameter optim:
>
> Optimizer_cache_optimal_size 102400
> optimizer_features_enable 8.1.7
> optimizer_index_caching 0
> optimizer_index_cost_adj 100
> optimizer_max_permutations 80000
> optimizer_mode choose
> optimizer_percent_parallel 0
>
> Immediate response for select /*+RULE*/ count(1)
> from dba_extents
> Count(1)
> ---------
> 4855
>
> select count(1) from dba_extents is still sitting
> there and has not yet
> responded.
>
> Thanks,
> -- Babu
>
>
> -----Original Message-----
> Sent: Thursday, May 09, 2002 12:41 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Babu,
>
> issue this in your sqlplus session:
>
> show parameter optim
>
> and then this:
>
> alter session set optimizer_mode=choose;
>
> and then this:
>
> select /*+ RULE */ count(1) from dba_extents;
>
> and then this:
>
> select count(1) from dba_extents;
>
> and let us know what happens.
>
>
>
> Rachel: we were running all_rows in our dwh and it
> was
> slowing down dd access. not sure why, choose and
> all_rows are kissing cousins. true, dd is supposed
> to
> be rule and with no stats. Have heard of cases where
> dbms_statistics analyzed the sys schema while doing
> a
> analyze_database, which is one reason I stopped
> using
> it. Might be the problem here? We will find out.
>
> hth,
>
> jack
>
>
>
> --- Janardhana Babu Donga <jbdonga_at_ucdavis.edu>
> wrote:
> > I have recently changed the analyze script.
> Earliar
> > it was
> > dbms_utility.analyze_schema(...) statement, It is
> > now changed to
> > dbms_stats.gather_database_stats();
> >
> > Is this a problem?
> >
> > The response time for select count(*) from
> > dba_extents is also 30 minutes.
> > It is not specific to any table.
> > Thanks,
> > -- Babu
> >
> > -----Original Message-----
> > [mailto:Rachel_Carmichael_at_Sonymusic.com]
> > Sent: Thursday, May 09, 2002 11:36 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> >
> > that's interesting... since the data dictionary is
> > NOT analyzed, setting
> > optimizer_mode=choose would force the query
> against
> > dba_extents to RULE
> > which it
> > what it is supposed to be doing anyway, Hm. The
> > question now is, what is the
> > optimizer_mode set to when the problem happens?
> Did
> > any of the data
> > dictionary
> > tables get accidentally analyzed? And -- how many
> > extents are in use in the
> > database? Could it just be a symptom of missized
> > tables and indexes so that
> > the
> > number of extents is way high?
> >
> > Although Oracle is *supposed* to allow unlimited
> > extents, in practice
> > anything
> > higher than 4096 extents in an object (at least in
> > 8i) tends to slow things
> > down.
> >
> >
> >
> >
> > |--------+----------------------->
> > | | |
> > | | |
> > | | jack_silvey_at_y|
> > | | ahoo.com |
> > | | |
> > | | 05/09/2002 |
> > | | 02:18 PM |
> > | | Please |
> > | | respond to |
> > | | ORACLE-L |
> > | | |
> > |--------+----------------------->
> >
> >
>
>----------------------------------------------------|
> > |
>
> > |
> > | To: ORACLE-L_at_fatcity.com
>
> > |
> > | cc: (bcc: Rachel Carmichael)
>
> > |
> > | Subject: Re: DBA_EXTENTS problem
>
> > |
> >
> >
>
>----------------------------------------------------|
> >
> >
> >
> >
> > Babu,
> >
> > We had a similiar problem, and setting
> > optimizer_mode
> > = choose in our session solved it. Something to do
> > with optimizer and DD access. Give that a try. I
> had
> > the same problem with DBA_INDEXES and that fixed
> it.
> >
> > hth,
> >
> > Jack
> >
> >
> > --- Janardhana Babu Donga <jbdonga_at_ucdavis.edu>
> > wrote:
> > > Dear List,
> > >
> > > When I query dba_extents , Iam getting response
> > > after half an hour to 40
> > > minutes, but when I query any other dictionary
> > view
> > > it is spontaneous.
> > > Everything else is fine in the database and
> there
> > > are no problems, except
> > > the above problem. Iam not getting any clue how
> to
> > > fix this. Iam thinking of
> > > running catalog.sql and catproc.sql as a last
> > > resort. Iam not sure it would
> > > fix the problem. Please let me know if there is
> > any
> > > other way to fix this
> > > problem.
> > >
> > > Thanks,
> > > Babu
> > >
> > >
>
=== message truncated ===
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu May 09 2002 - 16:24:40 CDT
