delayed responses from Oracle caused by SQL parsing

There might be a parsing bug because the SQL statement uses a correlated subquery that references a variable more than two levels deep.

This part of the code may be causing problems:

SELECT ... (SELECT ... (SELECT ... = a.account_ccy ... ) ... )...FROM ...  ,my_table_2 a

Supposedly the ANSI SQL standard limits table references to only one level deep. (I say supposedly because the standard is obnoxiously not freely available so I can't verify that for myself.) Oracle has had a bad history of occasionally enforcing that rule. Queries like the one above failed in most versions of 9, 10, and 11. But those queries worked in at least one version of 10 (and then failed when I upgraded to a later version of 10), and there was a patch to make them work in 11, and they worked again in 12.

I've only seen this issue cause errors like "column could not be found", but it wouldn't surprise me if it also caused parsing performance problems. Try re-writing the query to only reference the outer table one-level deep.

You should use bind variables if you want consistent results. Query plan does not change in this way:

PROCEDURE GET_CARDS_BY_ID(p_card_id IN NUMBER ,o_result  OUT VARCHAR2) ISBEGIN  v_sql := 'SELECT 'some data'  INTO o_result  FROM 'my complicated SQL' c     WHERE c.card_id = :1';  execute immediate v_sql into o_result using p_card;END GET_CARDS_BY_ID;