Support Article

Oracle Data Dictionary Query Constantly Executing

SA-27720

Summary



The following query is executed 600 times a minute:
SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, DECODE( (
SELECT a.typecode
FROM ALL_TYPES A WHERE a.type_name = t.data_type), ?, ?, ?, ?, DECODE(substr(t.data_type, ?, ?), ?, DECODE(substr(t.data_type, ?, ?), ?,
DECODE(substr(t.data_type, ?, ?), ?, ?, ?, ?, ?), DECODE(substr(t.data_type, ?, ?), ?, ?, ?, ?, ?)), ?, DECODE(substr(t.data_type, ?, ?), ?, ?, ?, ?),
DECODE(t.data_type, ?, ))) AS data_type, t.data_type AS type_name, DECODE (t.data_precision, null, DECODE(t.data_type, ?,
DECODE(t.data_scale, null, ? , ?), DECODE (t.data_type, ?, t.char_length, ?, t.char_length, ?, t.char_length, ?, t.char_length, ?, t.char_length, ?, ?,
t.data_length) ), t.data_precision) AS column_size, ? AS buffer_length, DECODE (t.data_type, ?, DECODE(t.data_precision, null,
DECODE(t.data_scale, null, ? , t.data_scale), t.data_scale), t.data_scale) AS decimal_digits, ? AS num_prec_radix, DECODE (t.nullable, ?, ?, ?) AS
nullable, NULL AS remarks, t.data_default AS column_def, ? AS sql_data_type, ? AS sql_datetime_sub, t.data_length AS char_octet_length,
t.column_id AS ordinal_position, DECODE (t.nullable, ?, ?, ?) AS is_nullable, null as SCOPE_CATALOG, null as SCOPE_SCHEMA, null as
SCOPE_TABLE, null as SOURCE_DATA_TYPE, ? as IS_AUTOINCREMENT
FROM all_tab_columns t WHERE t.owner LIKE :? ESCAPE ?
AND t.table_name LIKE :? ESCAPE ?
AND t.column_name LIKE :? ESCAPE ?
ORDER BY table_schem, table_name, ordinal_position

This is triggered when some or all obj-browse rules are executed in order to build the select statement for the obj-browse. The results of this query should be cached and the query must not be run constantly.

Error Messages



Not Applicable

Steps to Reproduce



Unknown

Root Cause



Due to a bug in the PRPC core code, the results for the query provided above are not cached properly. So instead of getting the result from the cache, it has to be retrieved from the database repeatedly. This results in increased execution time on the PRPC server, RDBMS, and network load.

Resolution



Apply HFix-29228.

Published September 8, 2016 - Updated September 15, 2016

Have a question? Get answers now.

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