Upgrade From 126.96.36.199: Slow Performance Adding Nullable Columns With Default Values To AWR Tables
By Alex Zaballa
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:
And another good thing, this restriction no longer exists in 12c+.
If you are upgrading from 11g to 19c and you have a large AWR repository, consider applying patch 30387640 before the upgrade.
Fonte: Alex Zaballa