SUMMARY:
This Technical Note describes an issue with slow performance that occur in Strategy after upgrading the metadata database server to Oracle 12c. A resolution is provided below for use in current releases.
SYMPTOM:
After upgrading the metadata database server from an older Oracle version to Oracle 12c, users experience a significantly slower performance when attempting any large action within the metadata such as updating schema, adding a table to the warehouse catalog and doing project comparison/duplication.
CAUSE:
This is caused by changes in Oracles internal execution engine for processing SQL. Specifically Oracle created a new adaptive optimizer for processing SQL which handles SQL differently to the older optimizer.
ACTION:
Change the database settings for cursor sharing to forced and set the new optimizer adaptive features to false. In all reported cases so far this has improved performance back to the levels seen with earlier versions of Oracle.
315399
