Introduction:
A query is repeated millions of times throughout the day coming from the CA MDM application looking for the primary keys on the table. This is consuming CPU on the database server.
“SELECT *
FROM
(
SELECT NULL TABLE_CATALOG ,
ac.owner TABLE_SCHEMA ,
ac.table_name TABLE_NAME ,
acc.column_name COLUMN_NAME,
NULL COLUMN_GUID ,
NULL COLUMN_PROPID ,
acc.position ORDINAL
FROM all_constraints ac,
all_cons_columns acc
WHERE ac.owner = acc.owner
AND ac.table_name = acc.table_name
AND ac.constraint_type = 'P'
AND ac.constraint_name = acc.constraint_name
)
DBSCHEMA_PRIMARY_KEYS
WHERE TABLE_SCHEMA = 'MDM_SCHEMA'
AND TABLE_NAME = 'A_GROUP_CONTAINER_EDGE'
ORDER BY 3,
4
”
Background:
The application MDM has no references to this query in its code.
Environment:
Oracle 11.2.04
MDM 18.1 using OLE DB Driver for Oracle