Pissed off at 9iR2

[slimdave]

Well, we "upgraded" to 9iR2 a few months ago, and we're all fckd up.

Firstly, we lost the ability to do CTAS on a 32K tablespace. Rather inconvenient.

Secondly, we implemented data segment compression, and now we have to keep running mods against the sys.obj$ table to prevent "block too fragmented to build bitmap index" errors.

Thirdly, after implementing data segment compression we found that you can neither ADD nor DROP columns from the table -- you have to uncompress the whole damn thing. Needless to say, this was an unpleasant surprise.

Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.

On a personal note, these problems have cost me about 200 hours with my family over the past few months, although as an hourly-rate consultant it has paid for my landscaping.

Anyhoo, at this rate our upgrade path will skip 10i and go directly to Teradata.

* sigh *

[marist89]

200hrs@$125/hr...I can live with that...

[slimdave]

Originally posted by marist89
200hrs@$125/hr...I can live with that...

So could i, if that's what it was.

[Tarry]

Originally posted by slimdave
Well, we "upgraded" to 9iR2 a few months ago, and we're all fckd up.

Firstly, we lost the ability to do CTAS on a 32K tablespace. Rather inconvenient.

Secondly, we implemented data segment compression, and now we have to keep running mods against the sys.obj$ table to prevent "block too fragmented to build bitmap index" errors.

Thirdly, after implementing data segment compression we found that you can neither ADD nor DROP columns from the table -- you have to uncompress the whole damn thing. Needless to say, this was an unpleasant surprise.

Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.

at this rate our upgrade path will skip 10i and go directly to Teradata.

* sigh *

Dave don't get me wrong here, but didn't you try all this out in your dev/test environemnt before upgrading your production databases. Either way you've learnt something...(I guess)

although as an hourly-rate consultant it has paid for my landscaping.

No complaining there though...

[slimdave]

Originally posted by Tarry
Dave don't get me wrong here, but didn't you try all this out in your dev/test environemnt before upgrading your production databases. Either way you've learnt something...(I guess)

We tried a lot of things, but evidently not enough, and our test box is close-but-not-the-same as prod.

For example, it's only certain of our fact tables that we can't bitmap index-- no-one seems to know why, and it worked fine under test.

We didn't try adding/dropping columns from a compressed table, 'cos it's not part of our normal operations to do so. It's was a new development that raised the issue.

Well, you know how it is, you have to draw the line somewhere. Whatever you test, you'll have missed something like a bug in applying a bitmap index to a non-foreign keyed nullable date type column having more than 10,00 rows and more than 4 distinct values, or some crap like that.

My real problem is with lack of responsiveness from Oracle on some of these issues -- having been outstanding for more than a month, with fixes backported to other platforms, you'd think it fairly straightforward to backport to a fairly common O/S like HPUX 64bit. Bugs i can handle, but the wait for fixes is driving me crazy.

[pando]

Anyone worked with Teradata? I have always been told Teradata owns Oracle in DWH.

It's really a shame you cannot add or drop column with compressed tables

What mod do you run against obj$ to prevent the error ypou mentioned :-?

[slimdave]

I think that theoretically (meaning "if the feature sets work") then the gap is much closer in 9iR2 than in prior releases.

The obj$ update sets the expected maximum number of rows to a higher number, detailed in metalink note 194734.1 ...

Error: ORA-28606 (ORA-28606)
Text: block too fragmented to build bitmap index (%s,%s)
---------------------------------------------------------------------------
Cause: The block(s) exceed the maximum number of rows expected when
creating a bitmap index. This is probably due to maximum slot
allowed set too low. The values in the message are: (slot number
found, maximum slot allowed)
Action: alter system flush shared_pool; update tab$ set spare1 = 8192
where obj# = (select obj# from obj$ where NAME= AND
owner# = ; commit;

.

I believe there is a similar workaround for a problem with "minimize rows per block" and partition exchanges, where spare1 is higher for the non-partitioned table than for the partitioned table.

I forgot to mention another problem. DBMS_STATS.GATHER_TABLE_STATS() with 'cascade=>true' won't work for function-based indexes. Or was that 8i? I forget now.

[abhaysk]

Originally posted by slimdave
Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.
* sigh *

?????????

[slimdave]

Originally posted by abhaysk
?????????

Indeed.

Bug No. 2093670

[abhaysk]

Originally posted by slimdave
Indeed.

Bug No. 2093670

Dave :

In our DW type DB (9.2.0.3 now), we have many BIT maps, but never encountered any problems, so just amused when u said "NO LONGER USE BITMAP INDEXES"...

Indeed I need to check once more now.

Abhay.