Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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.
  1. Create or replace TRG_PRPC trigger.
  2. After logging in to the database, when user='YOURDBUSERNAME' begin to execute the below,

    'ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE'; end;
Suggest Edit

Published February 19, 2019 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us