delayed responses from Oracle caused by SQL parsing

Question

I have a Java web service method getCardInformation which gets data from Oracle database using JDBC. About 300 calls are made per second.

Java calls this pl/sql function:

PROCEDURE GET_CARDS_BY_ID(p_card_id IN NUMBER
                         ,o_result  OUT VARCHAR2) IS
BEGIN

  SELECT 'some data'
    INTO o_result
    FROM 'my complicated SQL' c
   WHERE c.card_id = p_card_id;

END GET_CARDS_BY_ID;

Usually SQL Select statement/procedure returns result in 30 ms but sometimes there are cases when it needs more then 20 seconds and all other threads(calls) wait for this delayed call. Although these calls are not connected to each other, they run the same SQL Select statement with different input parameters. All parameters are set from Java using bind variables.

During the delay we selected active sessions and got lots of cursor: pin S wait on X

    SELECT DISTINCT a.*, s.*
  FROM V$ACTIVE_SESSION_HISTORY a
      ,v$sql                    s
 WHERE a.sql_id = s.sql_id
   AND blocking_session IS NOT NULL
   AND a.sample_time > sysdate - 1

We solved the problem using adding hint to our Select. We think that, during this delay, Oracle is trying to Parse this Select Statement and recalculate the plan for it which needs some time. All other calls which uses the same statement wait for Oracle to end the calculation. This is the reason of delay. When we set the hint we told to oracle not to make any calculation, so there was no delay.

But, why is Oracle trying to recalculate the plan?

Can I solve this problem without hard coding the index?

When the volume of my data changes in the database I don't want to change my code because of this hint.

SQL statement:

    SELECT (nvl((SELECT SUM(amount)
          FROM locks l
         WHERE l.account = a.account),
        0) - nvl((SELECT SUM(sl.amount)
                    FROM locks   sl
                        ,locks_2 m
                   WHERE sl.id = m.id
                     AND sl.account = a.account
                     AND m.text = '...'),
                  0)) / 100
  ,a.credit_limit / 100
  ,nvl((SELECT SUM(decode(ia.account,
                         ra.id,
                         ia.bal,
                         ceil(CASE
                                WHEN cc.ccy_num = '100' THEN
                                 (SELECT ia.bal / r.ccy_rate
                                    FROM my_ccy_rate r
                                   WHERE r.ccy_num = a.account_ccy)
                                WHEN a.account_ccy = '100' THEN
                                 (SELECT ia.bal * r.ccy_rate
                                    FROM my_ccy_rates r
                                   WHERE r.ccy_num = cc.ccy_num)
                                ELSE
                                 (SELECT ia.bal * r.ccy_rate / c.ccy_rate + 15000
                                    FROM my_ccy_rates r
                                        ,my_ccy_rates c
                                   WHERE r.ccy_num = cc.ccy_num
                                     AND c.ccy_num = a.account_ccy)
                              END)))
         FROM my_v           ia
             ,currency_codes cc
             ,my_table       pe
        WHERE ia.account = a.account_id
          AND cc.ccy_alpha = ia.ccy),
       0) / 100
  ,a.initial_amount / 100
  ,nvl((SELECT a.bonus_amount - nvl((SELECT SUM(sl.amount)
                                     FROM locks   sl
                                         ,locks_2 m
                                    WHERE sl.id = m.id
                                      AND sl.account_id = a.account_id
                                      AND m.text = '...'),
                                   0)
         FROM my_table_1 ra
             ,my_accounts  a
        WHERE ra.centre_id = o_centre_id
          AND ra.card_number = o_card
          AND a.centre_id = ra.centre_id
          AND a.account_id = ra.account_id),
       0) / 100
INTO o_amt_1
  ,o_amt_2
  ,o_amt_3
  ,o_amt_4
  ,o_amt_5
FROM my_table_1 ra
  ,my_table_2 a
  ,my_table_3 p
WHERE ra.card_number = &card_number_input_parameter
  AND a.account_id = ra.account_id;

Answer 1

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.