Re: very poor SYS view performance (8i) / fragmented data dictionary

gg.3.ukuechle_at_spamgourmet.com (Uwe Kuechler) wrote in message news:<beab228e.0312100801.7f069977_at_posting.google.com>...

> Thanks for your suggestions, but as I stated earlier, the said queries
> are generated by third-party software. Modification of those queries
> is therefore unfortunately NOT an option.

Bummer. How about complaining to said 3rd party that their SQL with missing join conditions is cactus (to put it mildly)?

> What do you exactly mean with "fragmented dictionary"? Physically
> fragmented? I already thought about re-creating the whole DB, but is
> this really the only option I've got? Can be quite annoying with a
> terabyte-size DB...

It can be physically fragmented if it's that large a DB. Depends on how many tables, columns, indexes, synonyms, constraints, etc (dictionary objects) you got. The default space allocation for dictionary tables and their clusters assumes very small databases in terms of dictionary objects. It's a left-over from earlier releases, I guess. There are a few places on the net where you can read about what to do to fix the problem, but it involves indeed a rebuild.

Having said that, I believe your case is simpler. It's a combination of truly bad SQL and the version you're running. Vanilla 8.1.7.0 has some very nasty bugs in its optimiser. There are even portions of its feature set that will simply not work due to bugs in the standard optimiser. These have been fixed in later patchset releases. 8.1.7.4 seems to be the most stable I've ever played with. And most of the optimiser bugs in this one have been ironed out. It might be simpler to fix it this way than re-building.

Unfortunately, given the sizes you have it might still be a major exercise anyway. Any chance of ditching that 3rd party software that is causing the problem? What's it got to do with the dictionary anyway and why is it looking at constraints?

> > to_char(null), "what the heck is this for?"
>
> Nice sense of humour ;-)
> Well, that's what you often get to see when tools generate their SQL
> dynamically.

I was trying to be polite. Can't even fathom why any1 would write something like that! I guess it goes hand in hand with the incomplete join condition...

Cheers and good luck
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Dec 10 2003 - 19:32:35 CST