Execution of parameterized query is very slow (Doc ID 1943515.1)

Last updated on FEBRUARY 27, 2016

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.2.6.7 and later
Information in this document applies to any platform.

Symptoms

The following query would take a long time(more than 5 minutes) to execute when the parameters are parameterized:

 

SELECT sum(count1) FROM (SELECT count(*) AS count1 FROM
owner.ALLOCATION A, Owner.table1 TST, Owner.table1 SST, Owner.table2  TM, Owner.ACCOUNT CA
  WHERE TM.ID = TST.ID AND TM.ID = SST.ID AND
  SST.ID = A.ID AND A.ALLOCATIONSTATUS IN ('N', 'M', 'X')
  AND NVL(A.ISPENDING, '0') = '0' AND A.ID = CA.ID AND CA.ACCOUNTTYPE =
  'B' AND CA.ACCOUNTCLASSIFICATION = 'Mb' AND TST.ID = ? UNION
  SELECT COUNT(*) AS count1 FROM Owner.ALLOCATION AL, Owner.table1T, Owner.ACCOUNT CA
  WHERE AL.ID = T.ID AND T.STATUS IN ('X',
  'M', 'N') AND AL.STATUS IN ('M', 'N', 'X') AND NVL(AL.ISPENDING,
  '0')='0' AND AL.ID = CA.ID AND CA.ACCOUNTTYPE = 'B' AND
  CA.ACCOUNTCLASSIFICATION = 'MB' AND T.ID = ?);

 

The same query with the constant value didn't have the same slow performance.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms