Frequent query looking for constraints is consuming database CPU in Oracle
Document ID : KB000125402
Last Modified Date : 28/01/2019
 Show Technical Document Details
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
Instructions:
All indications is it may be coming from the OLE DB Driver (reference here) the application is using.  It is entirely possible it is submitting the query
on the applications behalf.  There is a reference to the DBSCHEMA_PRIMARY_KEYS you see in the query.  
CA Development is looking at the possibility of switching to a different ODBC driver to avoid this issue.