Upgrade From 11.2.0.4: Slow Performance Adding Nullable Columns With Default Values To AWR Tables

Hi all,

I was in a project where the customer upgraded a large production database from 11g to 19c.

Phase 0 of the upgrade process took almost 4 hours and the DDLs responsible for all that time were related to new columns on AWR tables (WRH$ tables).

I was talking to Rodrigo Jorge (PM for upgrades and migrations) and he pointed me to this patch: 30387640

For example, these 2 DDLs took about 2 hours to run:

alter table WRH$_SQLSTAT add (obsolete_count number default 0);

alter table WRH$_SEG_STAT add (im_membytes number default 0);

I remember since 11g Oracle should only update the data dictionary when you are adding a new column with a default value, but what I didn't remember was that it works only for NOT NULL columns.

I found this after doing some research and here you have a great blog post about it:

https://chandlerdba.com/2014/10/30/adding-not-null-columns-with-default-values/

And another good thing, this restriction no longer exists in 12c+.

https://chandlerdba.com/2014/12/01/adding-a-default-column-in-12c/

If you are upgrading from 11g to 19c and you have a large AWR repository, consider applying patch 30387640 before the upgrade.

Thanks
Alex