How long will your upgrade take?

It seems to be a simple question but to predict the upgrade duration is really hard to do.

stopwatch.jpg

Due to the fact that an Oracle database upgrade doesn’t touch or change data the duration is totally independent of the size of the database. It won’t make any difference if the database is 2 GB or 28 TB of size.

The main factors determine the upgrade duration in descending order are:

    • Number of installed database components and options – the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it’ll take
    • Valid and non-stale dictionary statistics – even though creating dictionary statistics in some older releases of Oracle wasn’t a brilliant idea since desupport of the rule based optimizer the data dictionary should be analyzed. Especially right before an upgrade. Otherwise this will happen during the upgrade while the database is started in a restriceted upgrade mode causing addtional downtime.
    • Number of rows in AUD$ if audit_trail is set to DB
    • Number of synonyms when upgrading from Oracle 9i – synonyms will be touched and get a new dependency in the dictionary in DEPENDENCY$ – if there’s a high number (such as 100,000) this can eat up some time
    • Number of objects in XDB
    • At a very low rate if COMPATIBLE will be increased: Number of datafiles and the size of the redologs

 

4 thoughts on “How long will your upgrade take?

  1. Hi Weimin,
    would be interested how you do this – I can give an estimate, too – but no exact duration without having tested it – so feel free to let us know 🙂
    Kind regards
    Mike

  2. Hi Mike,
    You mention that ,”it would be a good practice to analyze the data dictionary right before an upgrade.”
    I am curious to find out if this can adversly impact the performance of some the query in the database, by changing the execution plan etc.
    Is that something I should be worried about, or analyzing the data dictionary won’t impact the execution plans of the SQL queries ?
    Regards
    Ashish

  3. Analyzing the Data Dictionary is not a great idea in Oracle 9i – but doing it right before the upgrade will just save upgrade time as otherwise it will be done during the upgrade and this adds downtime to the upgrade itselves. It won’t effect execution plans.
    Kind regards – Mike

Leave a Reply

Your email address will not be published. Required fields are marked *