Thanks for all those who helped me in fixing the problem.
I deleted the SYS schema statistics using
dbms_stats.delete_schema_stats('SYS') and I need to restart the database.
Iam changing my analyze script from database analyze to schema analyze.
Thanks,
--Babu
-----Original Message-----
Sent: Thursday, May 09, 2002 2:06 PM
To: Multiple recipients of list ORACLE-L
Try this: dbms_stats.delete_database_stats();
Janardhana Babu Donga wrote:
>
> I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy
> and made sure that they were gone by issuing the query:
>
> select table_name,owner
> from dba_tables
> where owner in ('SYS','SYSTEM')
> and last_analyzed is not null;
>
> It returned no rows this time. But why is my query: select count(*) from
> dba_extents is still sitting there? Is there anything else I should do?
> Restart the DB/something??
>
> Thanks for any response.
> -- Babu
>
> -----Original Message-----
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 1:19 PM
> To: Multiple recipients of list ORACLE-L
>
> I knew someone on the list would have it :) thanks Suzy.
>
> actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through
to
> fix
> the problem and then change the dbms_stats command to
> dbms_stats.gather_schema_stats instead of database
>
> |--------+----------------------->
> | | |
> | | |
> | | lvordos@qwest|
> | | .com |
> | | |
> | | 05/09/2002 |
> | | 03:44 PM |
> | | Please |
> | | respond to |
> | | ORACLE-L |
> | | |
> |--------+----------------------->
> >----------------------------------------------------|
> | |
> | To: [EMAIL PROTECTED] |
> | cc: (bcc: Rachel Carmichael) |
> | Subject: Re: DBA_EXTENTS problem |
> >----------------------------------------------------|
>
> dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.
>
> From Metalink:
> Bug:1422285 is a severity 3 bug that was logged for the
> dbms_stats.gather_database_stats collecting statistics on the data
> dictionary objects (FET$ was the example). This bug is fixed in 9i. A
> workaround is to execute dbms_stats.gather_database_statistics +
> dbms_stats.delete_schema_stats('SYS').
>
> Janardhana Babu Donga 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:[EMAIL PROTECTED]]
> > 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@y|
> > | | ahoo.com |
> > | | |
> > | | 05/09/2002 |
> > | | 02:18 PM |
> > | | Please |
> > | | respond to |
> > | | ORACLE-L |
> > | | |
> > |--------+----------------------->
> > >----------------------------------------------------|
> > | |
> > | To: [EMAIL PROTECTED] |
> > | 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 <[EMAIL PROTECTED]> 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
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Janardhana Babu Donga
> > > INET: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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).
> >
> > __________________________________________________
> > 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Janardhana Babu Donga
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Suzy Vordos
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janardhana Babu Donga
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
