Support Article
System hangs on connection to external schema
SA-57417
Summary
When testing the connection to an external schema (used for Business Intelligence Exchange (BIX) and data migration), JVM freezes outbound queries for about three minutes.
Error Messages
Database query took more than the threshold of 500 ms: 277,165 ms
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
Steps to Reproduce
Test the database connection to any class mapped to the non Pega schemas. The JVM hangs.
Root Cause
A defect or configuration issue in the operating environment.
The issue with the metadata query is a known issue with Oracle 12.1.0.2.
Queries for ALL_CONSTRAINTS take longer due to changes in the View Definition of ALL_CONSTRAINTS in Oracle 12.1.0.2 – DOC_ID 2266016.1.
Resolution
Make the following change to the operating environment:
Create a trigger for the database user that is used to make the connection to set _optimizer_push_pred_cost_based to false in the session.
The trigger replaces YOURDBUSERNAME with the actual name of the database user that is specified in the data source.
- Create or replace TRG_PRPC trigger.
- After logging in to the database, when user='YOURDBUSERNAME' begin to execute the below,
'ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE'; end;
Published February 19, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.