Support Article
Query executing 90 times
SA-20404
Summary
User found out that an OOTB query is running many times and consuming a lot of database resources while they use bundle negotiator.
The user was performing these actions:
- Login to NBA (custom stored proc is triggered)
- Move to BN (load bundles tab)
- Click and load discounts tab (custom activities are triggered to calculate product compatibility)
- Click and load addons tab. (custom activities are triggered calculate product compatibility)
Error Messages
Not Applicable
Steps to Reproduce
the query is the following
SELECT NULL AS table_cat, DECODE(s.owner, NULL, t.owner, s.owner) AS table_schem, DECODE(s.synonym_name, NULL, t.table_name, s.synonym_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), 'OBJECT', 2002, 'COLLECTION', 2003, DECODE(substr(t.data_type, 1, 9), 'TIMESTAMP', DECODE(substr(t.data_type, 10, 1), '(', DECODE(substr(t.data_type, 19, 5), 'LOCAL', -102, 'TIME ', -101, 93), DECODE(substr(t.data_type, 16, 5), 'LOCAL', -102, 'TIME ', -101, 93)), 'INTERVAL ', DECODE(substr(t.data_type, 10, 3), 'DAY', -104, 'YEA', -103), DECODE(t.data_type, 'BINARY_DOUBLE', 101, 'BINARY_FLOAT', 100, 'BFILE', -13, 'BLOB', 2004, 'CHAR', 1, 'CLOB', 2005, 'COLLECTIO N', 2003, ...............
Root Cause
There was no issues with the SQL this is standard and is called initially quite exensively until the column names are cached.
Resolution
There was no real issue, the SQL reported is called to get information on the columns the first time a table is called and then is cached, the first AWR report was run for a short period of time and the SQL appeared. The second AWR report was run for 6 hours which showed that the query stopped being called after the information had been cached.
Customer confirmed that the query is no longer appearing in AWR reports.
Published March 4, 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.