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:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:20 AM To: [EMAIL PROTECTED] 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@ucdav| | | is.edu | | | | | | 05/09/2002 | | | 02:01 PM | | | | |--------+-----------------------> >----------------------------------------------------| | | | To: [EMAIL PROTECTED] | | cc: Rachel Carmichael@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:[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).
