sql - 由 SQL 解析引起的 Oracle 响应延迟

Question

我有一个 Java 网络服务方法 getCardInformation从 Oracle 获取数据数据库使用 JDBC .每秒发出大约 300 个调用。

Java 调用这个 pl/sql 函数:

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;

通常 SQL Select statement/procedure在 30 毫秒内返回结果，但有时会出现需要 20 秒以上且所有其他线程(调用)等待此延迟调用的情况。
尽管这些调用没有相互连接，但它们运行相同的 SQL Select statement具有不同的输入参数。
所有参数均使用 bind variables 从 Java 设置.

在延迟期间，我们选择了事件 session 并获得了很多 光标:引脚 S 等待 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

我们通过添加 hint 解决了这个问题到我们的 Select .
我们认为，在此延迟期间，Oracle 正在尝试解析此 Select Statement和 回复 计算它需要一些时间的计划。所有其他使用相同语句的调用都等待 Oracle 结束计算。这就是延迟的原因。当我们设置提示时，我们告诉 oracle 不要进行任何计算，因此没有延迟。

但是，甲骨文为什么要重新计算计划？

我可以在不硬编码索引的情况下解决这个问题吗？

当数据库中的数据量发生变化时，我不想因为这个提示而更改我的代码。

SQL语句:

    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

可能存在解析错误，因为 SQL 语句使用相关子查询，该子查询引用深度超过两级的变量。

这部分代码可能会导致问题:

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

据说 ANSI SQL 标准将表引用限制为仅一层深。 (我这么说是因为该标准令人讨厌地无法免费获得，所以我无法亲自验证这一点。)Oracle 有过偶尔强制执行的糟糕历史
那个规则。上面的查询在 9、10 和 11 的大多数版本中都失败了。但是这些查询在 10 的至少一个版本中有效(然后在我升级到 10 的更高版本时失败了)，并且有一个补丁要制作他们在 11 个工作，他们工作
再次在 12。

我只看到这个问题会导致诸如“找不到列”之类的错误，但如果它也导致解析性能问题，我也不会感到惊讶。尝试重新编写查询以仅引用一级深度的外部表。