12c Upgrade – It can be slow!!!!

This post is for some of you who are planning to upgrade to 12c.I worked on upgrading single instance database from to, so thought to share my experience.

From 12c catupgrade.sql has been deprecated and Oracle has introduced catctl.pl script for upgrade. With catctl.pl script Oracle tries to perform as much work as possible in parallel, therefore minimizing downtime for upgrade.

With my experience, below are the points to consider for reducing the overall downtime.

3. HISTGRM$ add column

Lets take each point in more detail :-


WRI$_OPTSTAT_HISTGRM_HISTORY table was one of the main culprit during my first upgrade test. The table was ~35Gb with 2 indexes, each of ~70GB. As part of upgrade (catctl.pl — Phase 1) the table is altered and index is dropped and recreated without any parallel and nologging clause, which can take lots of time.

To reduce the downtime, make sure WRI$_OPTSTAT_HISTGRM_HISTORY table is purged. As in my case, the table was not getting automatically purged and it had 6mnts of data even though the retention was set to 28days.

You can follow steps mentioned in https://jhdba.wordpress.com/2011/08/23/939/ to purge the table. I tried the same, stopping the automatic mmon purging , dropping the indexes and running dbms_stats.purge_stats. The purge was quick (compared to with indexes and table size dropped to 6.5Gb from 35Gb), and then I ran create index command in parallel. The session was waiting on buffer busy wait for quite long and final_blocking_session showed DBWR sid. I killed my session after an hour and since it was test db, started the database in upgrade mode and created both the index which completed within 7mins.

On production, as no downtime was available, I ran dbms_stats.purge_stats (sysdate – 28) without dropping the indexes and it took ~42hrs. Basically behind the scene, oracle runs “alter table WRI$_OPTSTAT_HISTGRM_HISTORY drop partition partition_name update global indexes” to purge the data. During the upgrade catctl.pl run, index creation took ~14mins.


WRH$_ACTIVE_SESSION_HISTORY is table containing ASH data and has 1 index on it. In most of the databases, if automatic purging is not working, its one of the biggest segment in SYSAUX tablespace. To purge you can check :-


While upgrade, the index is dropped and created using PARALLEL clause.

3. HISTGRM$ add column

During the upgrade, the below sql can take lot of time. In my first test, it took 40mins.

alter table histgrm$ add ep_repeat_count number default 0 not null; 
03:10:36 SQL> Rem Add columns to accommodate endpoint repeat count information
03:10:36 SQL> alter table histgrm$ add ep_repeat_count number default 0 not null;

Table altered.

Elapsed: 00:38:47.83
03:49:24 SQL> alter table finalhist$ add eprepcnt number default 0 not null; 

As per Oracle Support

In 12.1, optimizer group added a column to histgrm$ which is a clustered table. For upgrade from 11.2 to 12.1, this column ep_repeat_count is added as a default value column with a not null constraint. Since histgrm$ table on a database could have millions of rows, this addition of a default value column could take a long time since we need to update all existing rows with a default value. This in turn slows down the upgrade significantly

This Upgrade slowness while executing the sql alter table histgrm$ add ep_repeat_count number default 0 not null; is related to bug 19333670 and you need to apply the patch 19333670 on Oracle Home before upgrading.

Incase you are using NFS, make sure to enable dNFS in 12c Oracle Home which is an optimized NFS (Network File System) client to provide faster and more scalable access to NFS storage located on NAS storage device.

Also, make sure to follow https://blogs.oracle.com/UPGRADE/ , if you are planning to upgrade to 12c🙂

Some article related to upgrade –

Things to Consider Before Upgrading to to Avoid Poor Performance or Wrong Results (Doc ID 2034610.1)

Hope it helps🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s