Oracle-L: Re: DBA_EXTENTS problem

From: <Rachel_Carmichael_at_Sonymusic.com>
Date: Thu, 09 May 2002 10:36:15 -0800
Message-ID: <F001.0045D197.20020509103615@fatcity.com>

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 > > > -- > 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).

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).




-- 
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).

Received on Thu May 09 2002 - 13:36:15 CDT