Support Article
SQLGeneratorOracle puts excessive load on DB on Oracle 12.1.0.2
SA-75353
Summary
When an SQL is generated in the SQLGeneratorOracle.getQueryForForeignKeyDependencies method, the system performance degrades.
Below is the generated SQL:
WITH given_table AS (SELECT ? AS schema_name, ? AS table_name FROM dual)
SELECT c.r_owner, c_pk.table_name
FROM given_table, all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.owner = given_table.schema_name
AND a.table_name = given_table.table_name
UNION
SELECT a.owner, a.table_name
FROM given_table, all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND c.r_owner = given_table.schema_name
AND c_pk.table_name = given_table.table_name
Error Messages
Not Applicable
Steps to Reproduce
Perform any action that fetches the structure of the database table.
Root Cause
This issue was related to a change in behavior in Oracle 12.1.0.2, which queried for ALL_CONSTRAINTS (Oracle DOC_ID 2266016.1).
Resolution
Apply HFix-51179..
Published August 15, 2019 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.