Data Dictionary – user_constraints/user_cons_columns is slow in Oracle 12cR1

Recently one of our client after upgrade, complaint about sluggish performance for one of the module comprise of getting table information in Oracle 12c R1.
Impacted SQL, comprise of Data Dictionary tables getting Table metadata.

SELECT
UTC.TABLE_NAME,
UTC.COLUMN_NAME,
UTC.COLUMN_ID AS COLUMN_ORDER,
UTC.DATA_TYPE,
UTC.DATA_TYPE || ' ' ||
DECODE(DECODE(UTC.DATA_TYPE, 'VARCHAR2', 'C', 'NVARCHAR2', 'C', 'CHAR', 'C', ''), 'C', '(' || UTC.CHAR_LENGTH || ') ', ' ') ||
DECODE(UTC.NULLABLE, 'Y', 'NULL ', 'N', 'NOT NULL ') AS TYPE_EXTENDED,
DECODE(UCC.POSITION, NULL, 'FALSE', 'TRUE') AS IS_PK,
UCC.POSITION AS PK_ORDER,
DECODE(UTC.NULLABLE, 'Y', 'TRUE', 'N', 'FALSE', 'TRUE') AS IS_NULLABLE,
DECODE(US.SEQUENCE_NAME, NULL, 'FALSE', 'TRUE') AS IS_IDENTITY,
DECODE(DECODE(UTC.DATA_TYPE, 'VARCHAR2', 'C', 'NVARCHAR2', 'C', 'CHAR', 'C', ''), 'C', UTC.CHAR_LENGTH, UTC.DATA_PRECISION) AS DATA_PRECISION,
UTC.DATA_SCALE,
UTC.DATA_DEFAULT AS DEFAULT_VALUE
FROM USER_TAB_COLUMNS UTC
LEFT OUTER JOIN USER_CONSTRAINTS UC
ON UC.CONSTRAINT_TYPE = 'P'
AND UC.TABLE_NAME = UTC.TABLE_NAME
LEFT OUTER JOIN USER_CONS_COLUMNS UCC
ON UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC.TABLE_NAME = UTC.TABLE_NAME
AND UCC.COLUMN_NAME = UTC.COLUMN_NAME
LEFT OUTER JOIN USER_SEQUENCES US
ON US.SEQUENCE_NAME = UTC.TABLE_NAME || '_' || UTC.COLUMN_NAME
WHERE
UTC.TABLE_NAME = $$TableName
AND UTC.TABLE_NAME NOT IN ('PLAN_TABLE')
ORDER BY UTC.TABLE_NAME, UTC.COLUMN_ID ;

Based on initial investigation, identified bugs already exists for Oracle 12c R1 release impacting Data Dictionary for “USER_CONSTRAINTS” and “USER_CONS_COLUMNS”
Data Dictionary Select Taking A Very Long Time in 12c (Doc ID 2251730.1)

Instead of Altering Session as solution, we suggested Client to include below hint.

SELECT /*+ OPT_PARAM('_OPTIMIZER_SQU_BOTTOMUP','FALSE')*/
UTC.TABLE_NAME,
UTC.COLUMN_NAME,
UTC.COLUMN_ID AS COLUMN_ORDER,
UTC.DATA_TYPE,
UTC.DATA_TYPE || ' ' ||
DECODE(DECODE(UTC.DATA_TYPE, 'VARCHAR2', 'C', 'NVARCHAR2', 'C', 'CHAR', 'C', ''), 'C', '(' || UTC.CHAR_LENGTH || ') ', ' ') ||
DECODE(UTC.NULLABLE, 'Y', 'NULL ', 'N', 'NOT NULL ') AS TYPE_EXTENDED,
DECODE(UCC.POSITION, NULL, 'FALSE', 'TRUE') AS IS_PK,
UCC.POSITION AS PK_ORDER,
DECODE(UTC.NULLABLE, 'Y', 'TRUE', 'N', 'FALSE', 'TRUE') AS IS_NULLABLE,
DECODE(US.SEQUENCE_NAME, NULL, 'FALSE', 'TRUE') AS IS_IDENTITY,
DECODE(DECODE(UTC.DATA_TYPE, 'VARCHAR2', 'C', 'NVARCHAR2', 'C', 'CHAR', 'C', ''), 'C', UTC.CHAR_LENGTH, UTC.DATA_PRECISION) AS DATA_PRECISION,
UTC.DATA_SCALE,
UTC.DATA_DEFAULT AS DEFAULT_VALUE
FROM USER_TAB_COLUMNS UTC
LEFT OUTER JOIN USER_CONSTRAINTS UC
ON UC.CONSTRAINT_TYPE = 'P'
AND UC.TABLE_NAME = UTC.TABLE_NAME
LEFT OUTER JOIN USER_CONS_COLUMNS UCC
ON UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC.TABLE_NAME = UTC.TABLE_NAME
AND UCC.COLUMN_NAME = UTC.COLUMN_NAME
LEFT OUTER JOIN USER_SEQUENCES US
ON US.SEQUENCE_NAME = UTC.TABLE_NAME || '_' || UTC.COLUMN_NAME
WHERE
UTC.TABLE_NAME = $$TableName
AND UTC.TABLE_NAME NOT IN ('PLAN_TABLE')
ORDER BY UTC.TABLE_NAME, UTC.COLUMN_ID ;

Post changes Performance improve from 7 Sec to 1 Sec for Data Dictionary SQL.

Advertisements

About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features, Oracle Internal and tagged , , , . Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s