使用ASH诊断Oracle解析故障

英文原文在：Diagnosing Parsing Issue with ASH

解析，尤其是硬解析，是非生产性操作，会消耗大量系统资源，导致库缓存争用。ASH（Active Session History）可以通过其采样机制来诊断和分析过度的解析。本文探讨了如何有效地使用ASH来识别解析问题。

模拟解析问题

首先，我们使用以下PL/SQL匿名块来模拟解析问题：

SQL> set timing on
SQL> create table yuan_obj as select * from dba_objects;

Table created.

Elapsed: 00:00:00.746
SQL>
SQL> declare
  2    i number;
  3    sql_text varchar2(256);
  4  begin
  5    for i in 1..100000 loop
  6       sql_text := 'select object_name from yuan_obj where object_id = '||i;
  7       execute immediate sql_text;
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:05:44.904

此场景是由于缺乏绑定变量而导致的低效SQL执行的经典示例。

检测解析问题

v$active_session_history和DBA_HIST_ACTIVE_SESS_HISTORY视图中的in_parse和in_hard_parse列可用于检测解析问题：

1. in_parse：此列指示会话当前是否正在执行解析操作。
2. in_hard_parse：此列指示会话是否正在执行硬解析。

以下SQL查询在过去10分钟内分组并统计解析活动的发生次数：

col in_parse form a10
col in_hard_parse form a10
select in_parse,
       in_hard_parse,
       count(*) as cnt,
       100 * trunc(ratio_to_report(count(*)) over (), 4) as "PERCENT"
  from v$active_session_history
 where sample_time > current_timestamp - interval '10' minute
 group by in_parse, in_hard_parse
 order by count(*) desc;

当运行低效的PL/SQL块时，我们得到如下输出：

IN_PARSE   IN_HARD_PARSE     CNT   PERCENT
Y          Y                 229     91.23
Y          N                  17      6.77
N          N                   5      1.99

此输出表明解析，尤其是硬解析，占据了大多数数据库活动，只有1.99%的数据库资源真正用于查询执行。

让我们通过分析数据库正在经历的等待事件来深入了解：

col event form a30
set linesize 200
SELECT
    NVL(event, 'ON CPU') AS event,
    COUNT(*) AS total_wait_time,
    TO_CHAR(100 * TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER (), 6), 'FM990.9999') || '%' AS "%"
FROM
    v$active_session_history
WHERE
    sample_time > SYSDATE - 10 / 60 / 24
GROUP BY
    event
ORDER BY
    total_wait_time DESC;

输出：

EVENT                          TOTAL_WAIT_TIME %
------------------------------ --------------- ----------
ON CPU                                     503 92.1245%
latch: shared pool                          38 6.9597%
library cache: mutex X                       4 0.7326%
latch: row cache objects                     1 0.1831%

此输出表明解析大量使用了CPU，并且溢出到相关的Oracle等待事件中。

识别有问题的SQL语句

要识别有问题的SQL语句，可以利用v$sqlstats中的force_matching_signature列。此列包含一个哈希值，用于标识在文本上相似但可能具有不同文字值的SQL语句。此哈希值用于基于相似但不完全相同的SQL语句进行光标共享。

以下查询将v$active_session_history与v$sqlstats连接，以检索与最频繁执行的SQL语句共享相同force_matching_signature的SQL语句。

SELECT a.sample_time,
       a.sql_id,
       NVL(a.event, 'CPU') AS event,
       a.in_parse,
       a.in_hard_parse,
       a.force_matching_signature,
       t.exact_matching_signature,
       t.sql_text
FROM v$active_session_history a
LEFT JOIN v$sqlstats t ON a.sql_id = t.sql_id
WHERE t.force_matching_signature = (
    SELECT force_matching_signature
    FROM (
        SELECT s.force_matching_signature, COUNT(*) AS cnt
        FROM v$sqlstats s
        GROUP BY s.force_matching_signature
        ORDER BY COUNT(*) DESC
    )
    WHERE ROWNUM = 1
);

另一种发现相似SQL语句的方法是比较编辑后的SQL文本：

SELECT a.sample_time,
       a.sql_id,
       NVL(a.event, 'CPU') AS event,
       a.in_parse,
       a.in_hard_parse,
       a.force_matching_signature,
       t.exact_matching_signature,
       t.sql_text
FROM v$active_session_history a
LEFT JOIN v$sqlstats t ON a.sql_id = t.sql_id
WHERE SUBSTR(t.sql_text, 1, 40)= (
    SELECT truncated_sql_text
    FROM (
        SELECT SUBSTR(s.sql_text, 1, 40) AS truncated_sql_text,  COUNT(*) AS cnt
        FROM v$sqlstats s
        GROUP BY SUBSTR(s.sql_text, 1, 40)
        ORDER BY COUNT(*) DESC
    )
    WHERE ROWNUM = 1
);

虽然两个查询的目的是相同的，但使用force_matching_signature的查询通常更准确。在这种情况下，两个查询产生相同的输出，如下所示（为简洁起见进行了截断）：

输出显示了文本上相似的SQL语句（具有相同的FORCE_MATCHING_SIGNATURE，即4786214959369239152）可以具有不同的字面值，从而具有不同的EXACT_MATCHING_SIGNATURE和不同的SQL_ID。

解决方案建议

在识别出有问题的SQL语句后，我们应与应用程序供应商合作修改应用程序代码。目标是尽可能用绑定变量替换文字。这种修改允许Oracle重用执行计划，从而减少与解析相关的开销。

可以将之前低效的PL/SQL优化如下：

DECLARE
  i NUMBER;
BEGIN
  FOR i IN 1..100000 LOOP
    EXECUTE IMMEDIATE 'select object_name from yuan_obj where object_id = :1' USING i;
  END LOOP;
END;
/

SQL> alter system flush shared_pool;

System altered.

SQL> set timing on
SQL> DECLARE
  2    i NUMBER;
  3  BEGIN
  4    FOR i IN 1..100000 LOOP
  5      EXECUTE IMMEDIATE 'select object_name from yuan_obj where object_id = :1' USING i;
  6    END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.298

优化后的块显著更快，将执行时间从近6分钟减少到仅3秒多。

待优化后的块运行结束后，从v$active_session_history和v$sqlstats视图中检索先前SQL执行的信息：

SELECT a.sample_time,
       a.sql_id,
       NVL(a.event, 'CPU') AS event,
       a.in_parse,
       a.in_hard_parse,
       a.force_matching_signature,
       t.exact_matching_signature,
       t.sql_text
FROM v$active_session_history a
LEFT JOIN v$sqlstats t ON a.sql_id = t.sql_id
WHERE SUBSTR(t.sql_text, 1, 40)= substr('select object_name from yuan_obj where object_id =',1,40);

输出只包含两条已执行SQL的条目。相同的SQL_ID、FORCE_MATCHING_SIGNATURE、EXACT_MATCHING_SIGNATURE和SQL_TEXT的值表明SQL只解析了一次，并且其执行计划被重用了，这大大减少了在Library Cache中的占用。

另一种缓解Library Cache争用的方法是增加Shared Pool的大小。此调整可以提高频繁执行的SQL语句及其执行计划保留在Library Cache中的可能性，从而减少重复解析的需求。

在某些情况下，将CURSOR_SHARING参数设置为FORCE可以通过强制Oracle内部用绑定变量替换文字来减少硬解析。然而，这应被视为会话级别的临时解决方案，而不是实例级别的永久解决方案。