| 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
I thought using DBMS_STATS is much more efficient than the old method. I have recently read an article about it in Oracle magazine, Jan/Feb 2002 Page 32, then I changed the analyze script.
I would appreciate if anyone sends me a script to delete the SYS/SYSTEM statistics.
Thanks,
-- Babu
-----Original Message-----
[mailto:Rachel_Carmichael_at_Sonymusic.com]
Sent: Thursday, May 09, 2002 11:20 AM
To: jbdonga_at_ucdavis.edu
I *think* there was a bug where it would also analyze SYS when you gathered
database stats... why did you go from analyzing at the schema level to
analyzing
the entire database?
You will need to delete the stats if they are there for any object owned by
SYS.
since I've never done this, I don't have a script to delete the stats... I
know
I've seen it posted to the list.. anyone have a copy?
Rachel
|--------+----------------------->
| | |
| | |
| | jbdonga_at_ucdav|
| | is.edu |
| | |
| | 05/09/2002 |
| | 02:01 PM |
| | |
|--------+-----------------------> >----------------------------------------------------| | | | To: ORACLE-L_at_fatcity.com | | cc: Rachel Carmichael_at_Sony_Music | | Subject: RE: DBA_EXTENTS problem | >----------------------------------------------------|
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). -- 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).Received on Thu May 09 2002 - 14:41:24 CDT
 |
 |