Query executing 90 times
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)
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, ...............
There was no issues with the SQL this is standard and is called initially quite exensively until the column names are cached.
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.
0% found this useful