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 15, 2016 - 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.