Support Article
A large number of SELECT NULL statements generated by PRPC
SA-8481
Summary
During performance testing, when the following queries are run at a rate of 2.5 million executions per hour, it further causes a heavy load on the database.
The query is a function of java.sql.DatabaseMetaData.getPrimaryKeys(), which is being run by PRPC and also the query does not show up in any PRPC logs such as alert, rules, or dbtrace.
SELECT NULL AS table_cat,
c.owner AS table_schem,
c.table_name,
c.column_name,
c.position AS key_seq,
c.constraint_name AS pk_name
FROM all_cons_columns c, all_constraints k
WHERE k.constraint_type = 'P'
AND k.table_name = :1
AND k.owner like :2 escape '/'
AND k.constraint_name = c.constraint_name
AND k.table_name = c.table_name
AND k.owner = c.owner
ORDER BY column_name;
SELECT NULL AS table_cat,
owner AS table_schem,
table_name,
0 AS NON_UNIQUE,
NULL AS index_qualifier,
NULL AS index_name,
0 AS TYPE,
0 AS ordinal_position,
NULL AS column_name,
NULL AS asc_or_desc,
num_rows AS CARDINALITY,
blocks AS pages,
NULL AS filter_condition
FROM all_tables
WHERE table_name = 'PR_DATA' AND owner = 'PEGA'
UNION
SELECT NULL AS table_cat,
i.owner AS table_schem,
i.table_name,
DECODE (i.uniqueness, 'UNIQUE', 0, 1),
NULL AS index_qualifier,
i.index_name,
1 AS TYPE,
c.column_position AS ordinal_position,
c.column_name,
NULL AS asc_or_desc,
i.distinct_keys AS CARDINALITY,
i.leaf_blocks AS pages,
NULL AS filter_condition
FROM all_indexes i, all_ind_columns c
WHERE i.table_name = 'PR_DATA'
AND i.owner = 'PEGA'
AND i.index_name = c.index_name
AND i.table_owner = c.table_owner
AND i.table_name = c.table_name
AND i.owner = c.index_owner
ORDER BY non_unique,
TYPE,
index_name,
ordinal_position;
Error Messages
No errors are generated. Additional query affected.
Steps to Reproduce
The above mentioned queries are run whenever PRPC server is running. When PRPC server is down, these queries do not run.
Root Cause
The root cause of this problem is a defect in Pegasystems’ code or rules.
The details of the tables are not cached when using the Obj-Browse functionality.
Resolution
This issue is resolved by hotfix item HFix-21558.
Published June 12, 2015 - 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.