Oracle8i vs. Oracle 9i

[Michael42]

Hello,

I will be migrating an 8.17 database to 9i. I have been reading the Oracle "sales-hyped" verbiage on what the advantages are to migrating from 8.17 to Oracle 9i.

Other than Oracle 8.x not being supported, what advantages have you gained by migrating to 9i?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

There are some wonderful enhancements to Oracle 9i over Oracle 8i. Among the most significant is the functionality of CLOBs: In Oracle 8i, if you had a LONG column, there just wasn't very much you could do with it...you couldn't concatenate it to anything, you couldn't compare it to anything, you couldn't perform any functions on it. But with CLOB (Character Large Objects), you can do anything to a 2GB CLOB that you can do to a varchar2. For many, that is enough reason to migrate to Oracle 9i right there !

There are other nice upgraded features in 9i, but I'm too tired to enumerate them here. I have nothing but nice things to say about 9i. Use it in good health.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:03 (05Oct04) UTC (aka "GMT" and "Zulu"), 23:03 (04Oct04) Mountain Time)

 

[stefanhei]

Hi Michael,
The advantages 9i vs 8i I've seen were:
performance:
- by using spfile instead of pfile: initialisation parameters are available in a compiled form. Also you now issue changes to parameters by ALTER DATABASE or ALTER SYSTEM
- pl/sql compiles to native code now
- usage of undo tablespace instead of rbs
flexibility:
- more flexible memory management

Disadvantages:
- needs more diskspace for binaries
- if you didn't change to CLOB when migrating from 7 to 8, LONGs are desupported now - but as you can read in Dave's post this is seen as advantage too

This are mostly DBA-advantages, developers will have other advantages too.

Stefan

 

[Michael42]

Thanks all for the great comments. They are VERY useful.


Can anyone comment on these two features:

1. 8i's Undo vs. 9i 'sRedo architecture (I might have this backwards)?

2. New interfaces you have found useful (iSQLPlus etc.)


Thanks again for your comments,

Michael42

 

[stefanhei]

Referencing my last post: LONGs still are available in 9i, just should not be used.
About your qustions:
1) Undo tablespaces replace rollback segments. Instead of rollback segments you now have a tablespace which holds your changes. You specify a time how long undo to be retained before data is overwritten. Within this period of time you have the FLASHBACK-option available: you can select (or whatever) AS OF TIMESTAMP...
2) never used iSQLPlus, but the interface in general now supports ANSI-SQL.

Stefan

 

[ZBean]

Has anyone found severe performance degradation after upgrading from LONGs to CLOBs in 9i? I have one user (out of many) who is complaining of queries taking much longer in our Oracle application after running our LONG_TO_CLOB script. Specifically, I'm wondering if it has to do with Stefan's comment about "needs more diskspace for binaries"?

Anyone's help is appreciated.

Warren

 

[Michael42]

Hello ZBean,

In our shop we have a database that processes 120 Terabytes (yes Terabytes) of data per day. When we moved it to used BLOBS\CLOBS one process went from 8-10 hours to 3 hours. In short, there are many advantages and enhancement options when using BLOBS\CLOBS that are not available with Raw\Long etc.

I hope this helps,

Michael

 

[ZBean]

Thanks Michael. That's the type of information I needed to know. I was just reading another post that discussed how CLOBS are stored, and it sounds like if the data is stored in the row it can significantly reduce performance, especially where there are full table scans. I'll see if that's the case here.

 

[Michael42]

>> if the data is stored in the row it can significantly reduce performance

True. I have found that if the size is less than 2k or so (not too big) performance is acceptable if storing in same row.

-Michael42