Oracle大师Roger Cornejo的推荐：使用ASH诊断Oracle解析故障

我的这篇文章的英文版被Oracle大师Roger Cornejo在X平台上推荐（见下图），英文原文在：

https://byte-way.com/2024/07/25/diagnosing-parsing-issue-with-ash/

，或点击文章末尾的“原文链接”查看

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

01

—

模拟解析故障

首先，我们使用以下PL/SQL来模拟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执行的经典示例。

02

—

检查过度解析

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
。

03

—

解决方案

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

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

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内部用绑定变量替换文字来减少硬解析。但这应被视为会话级别的临时解决方案，而不是实例级别的永久解决方案。

关于号主，姚远：

• Oracle ACE（Oracle和MySQL数据库方向）

• 华为云最有价值专家

• 《MySQL 8.0运维与优化》的作者

• 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证

• 曾任IBM公司数据库部门经理

• 20+年DBA经验，服务2万+客户

• 精通C和Java，发明两项计算机专利

欢迎关注我的公众号，一起学习数据库技术👇

欢迎加我的微信，拉你进数据库微信群👇

推荐文章👇

国外的程序员没有35岁魔咒吗？

试看号主的拙作《MySQL 8.0运维与优化》（清华大学出版社）

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

又考了一个Oracle优化的OCP，交一下作业

一个计算机工程师如何达到英语专业八级的水平

学好Oracle只需要看一本书就够了？