Support Article
Huge Query involves poor performance
SA-56624
Summary
A slow query has been detected when reviewing database performances.
A similar issue was previously addressed by HFix-32642. However the FIX is already installed but the issue still occurs.
Error Messages
Long execution times (2-3 seconds) for the below query:
( SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, decode (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93, 'RAW', 3, 'LONG RAW', 4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', 13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', 101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', 102, 'INTERVAL YEAR(2) TO MONTH', 103, 'INTERVAL DAY(2) TO SECOND(6)', 104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, t.data_type AS type_name, decode (t.data_precision, NULL, decode (t.data_type, 'CHAR', t.char_length, 'VARCHAR', t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2', t.char_length, t.data_length), t.data_precision) AS column_size, 0 AS buffer_length, t.data_scale AS decimal_digits, 10 AS num_prec_radix, decode (t.nullable, 'N', 0, 1) AS nullable, NULL AS remarks, t.data_default AS column_def, 0 AS sql_data _type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, decode (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t WHERE t.table_name = :1 AND t.owner = :2 ) UNION ALL ( SELECT NULL AS table_cat, s.owner AS table_schem, s.synonym_name AS table_name, t.column_name AS column_name, decode (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93, 'RAW', 3, 'LONG RAW', 4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', 13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', 101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', 102, 'INTERVAL YEAR(2) TO MONTH', 103, 'INTERVAL DAY(2) TO SECOND(6)', 104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, t.data_type AS type_name, decode (t.data_precision, NULL, decode (t.data_type, 'CHAR', t.char_length, 'VARCHAR', t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2', t.char_length, t.dat a_length), t.data_precision) AS column_size, 0 AS buffer_length, t.data_scale AS decimal_digits, 10 AS num_prec_radix, decode (t.nullable, 'N', 0, 1) AS nullable, NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, decode (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t INNER JOIN all_synonyms s ON (s.table_name = t.table_name AND s.table_owner = t.owner) WHERE s.synonym_name = :3 AND s.owner = :4 ) ORDER BY table_schem, table_name, ordinal_position
Steps to Reproduce
Monitor database queries causing performance issues.
Root Cause
A third-party configuration issue. The "includeSynonyms = true" property was assigned in the Connection Pool properties of the PegaRULES Data source.
This caused the expensive join with the all_synonyms Oracle view while executing JDBC driver metadata queries that may occurs for the operations in which table mapping is required.
Resolution
Make the following change to the operating environment:
Remove the "includeSynonyms = true" property from the Connection Pool properties for the PegaRULES Data source.
Published July 26, 2018 - 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.