Oracle vs SQL

[Deborah_Hack]

(Currently upgrading from OnBase 13 to OnBase 14)

Our OnBase database is an Oracle db. When we upgrade the db it takes a really long time (something like 10 hours) even though we don't have a lot of data in there yet (compared to other companies). I"ve heard that upgrades are slower for Oracle than SQL. Is 10ish hours typical for Oracle? If not, any ideas what could make the process so slow? It has been slow every time we do an upgrade, not just this time.

On a related note, what are the pros and cons of the two platforms? Is OnBase more suited to SQL?

-Deborah

[AdamShaneHyland]

Hi Deborah,

Thanks for your post.

In general I would say that 10 hours is a long time for an upgrade on any platform.  I recently worked on an upgrade of a large healthcare customer on Oracle and the upgrade took about 45 minutes from OnBase 12 to 13 (I believe the database is in the 300 GB range).  Previously they had mentioned that the Production upgrade from OnBase 11 to 12 took approximately 4 hours. This time around I worked with them to verify that all optimization steps (see Database Maintenance Guides - link) were completely right before the upgrade.  Your Oracle DBA should be able to verify that all optimizations are in place and being run regularly to improve performance on your system.

Take care.

[Jay_MacVean]

Oracle DB upgrades can be slow but there has been a modification available that greatly speeds them up. Your first line support should be able to look up the procedure. I utilized a small script or utility years ago when I upgraded Oracle for a customer and it was like night and day difference.

[Jay_MacVean]

BTW - I did find the OnBase zip file that was sent to me in 2008 that was used to modify the Oracle tables and make them run faster. The original file was predicated on the Oracle version being 10 or later. The file was password protected but I still had the password. I'll paste in the comments from the top of the query so you can see where this was going. I will not provide the entire script however since it probably is version specific. -- Script to fix slow performance when upgrading OnBase or create a new database in an Oracle 10.2 environment-- Reference Metalink Note 364822.1: “Poor Performance On Certain Dictionary Queries After Upgrade To 10g” for more details-- Last reviewed ELS 04/29/2008-- builds the 10g all_objects and all_synonyms views to look like it used to in 9i-- view is named sys.all_objects_92-- synonym for the view is hsi.ALL_SYNONYMS-- after the OnBase upgrade is complete, the synonym and view can be dropped with the following statements,-- the statements MUST BE RUN AS SYS:-- drop synonym hsi.ALL_OBJECTS-- drop view view sys.ALL_OBJECTS_92-- NOTE: THIS SCRIPT MUST BE RUN AS SYS!create or replace view sys.ALL_OBJECTS_92

[AdamShaneHyland]

Hi Jay,

From my initial research the script was written for a specific version of Oracle.  Often times scripts are created for specific purposes and/or specific versions of the RDBMS.  Running a script against a system could potentially have unknown consequences.  My recommendation is to always follow up with your first line of support and engage the assistance of Hyland Database Support prior to running a script, especially one which was written a while ago.

Take care.