Frequent query looking for constraints is consuming database CPU in Oracle
book
Article ID: 125402
calendar_today
Updated On:
Products
CA Mobile Device Management
Issue/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 ”
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
Resolution
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.