| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBA_EXTENTS problem
Yes, that's bad. You have analyzed the data dictionary tables. You need to delete the statistics for them.
-----Original Message-----
Sent: Thursday, May 09, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L
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
-- 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 - 14:24:14 CDT
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: INET: Rachel_Carmichael_at_Sonymusic.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga INET: jbdonga_at_ucdavis.edu 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth INET: Beth.Seefelt_at_TetleyUSA.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).
 |
 |