IV90434: LONG RUNNING QUERY ORIGINATING FROM ORACLE, NOT TRIRIGA WAS CAUSING TRIRIGA PERFORMANCE ISSUES.

APAR status

• Closed as unreproducible.

Error description

• The following SQL was running on customer installation and was
  not originating from Tririga itself.  This SQL was long running
  SQL running often and it resulted in Tririga performance
  problems:
  
  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),
  '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, 'COLLECTION', 2003,
  'DATE', 93, 'FLOAT', 6, 'LONG', -1, 'LONG RAW', -4, 'NCHA R',
  -15, 'NCLOB', 2011, 'NUMBER', 3, 'NVARCHAR', -9, 'NVARCHAR2',
  -9, 'OBJECT', 2002, 'OPAQUE/XMLTYPE', 2009, 'RAW', -3, 'REF',
  2006, 'ROWID', -8, 'SQLXML', 2009, 'UROWID', -8, 'VARCHAR2',
  12, 'VARRAY', 2003, 'XMLTYPE', 2009, 1111))) AS data_type,
  t.data_type AS type_name, DECODE (t.data_precision, null,
  DECODE(t.data_type, 'NUMBER', DECODE(t.data_scale, null, 0 ,
  38), DECODE (t.data_type, 'CHAR', t.char_length, 'VARCHAR',
  t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2',
  t.char_length, 'NCHAR', t. char_length, 'NUMBER', 0,
  t.data_length) ), t.data_precision) AS column_size, 0 AS
  buffer_length, DECODE (t.data_type, 'NUMBER', DECODE
  (t.data_precision, null, DECODE(t.data_scale, null, -127 , t.
  data_scale), t.data_scale), t.data_scale) AS decimal_digits, 10
  AS num_prec_radix, DECODE (t.nullable, 'N', 0, 1) AS nullable,
  NULL AS remarks, t.data_default AS column_def, 0 AS
  sql_data_type, 0 AS sql_datetime_sub, t.data_length AS
  char_octet_length, t.column_id AS ordinal_position, DECODE
  (t.nullable, 'N', 'NO', 'YES') AS is_nullable, null as
  SCOPE_CATALOG, null as SCOPE_SCHEMA, null as SCOPE_TABLE, null
  as SOURCE_DATA_TYPE, 'NO' as IS_AUTOINCREMENT FROM
  all_tab_columns t WHERE t.owner LIKE :1 ESCAPE '/' AND
  t.table_na me LIKE :2 ESCAPE '/' AND t.column_name LIKE :3
  ESCAPE '/' ORDER BY table_schem, table_name, ordinal_position "
  

Local fix

• Replaced the Oracle JDBC ojdbc7.jar with ojdbc6.jar, and the
  issue went away.
  

Problem summary

• This has been determined to be an Oracle Daatabase issue with
  version 12c. A SR has been opened with Oracle: 3-13811220321 :
  ojdbc7 with 12c Database, call to getMetaData can have
  performance impact on DB Server vs ojdbc6
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• 999
  

Fix information

Applicable component levels

• R351 PSY

     UP