Upgrading to 11g Slow Performance When Compiling Tables With Created Without an Owner Prefix For Column Type For Example Sdo_Geometry (Doc ID 1313743.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Symptoms

When upgrading a database from 10.2.x to 11.2.0.2 there is a performance problem caused by tables created with column types which do not specify the owner of the object datatype.

The problem was found with tables that had columns of the SDO_GEOMETRY datatype, but the column type was not prefixed with its owner MDSYS.  The problem was not specific to SDO_GEOMETRY, but could occur for any object data type used without prefixing with the owner data type.  Use of a public synonym for the object type could lead to the same problem.

Using the example with SDO_GEOMETRY:

create table roads (geom sdo_geometry);

For this table we have a slowed performance on the upgrade because an update is issued to every row in the table.

If the same table was created using the MDSYS prefix,

create table roads (geom MDSYS.sdo_geometry);

The performance problem is not encountered during the upgrade.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.