我的这篇文章的英文版被Oracle大师Roger Cornejo在X平台上推荐(见下图),英文原文在:
,或点击文章末尾的“原文链接”查看
解析,尤其是硬解析,是非生产性操作,会消耗大量系统资源,导致库缓存争用。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
列可用于检测解析问题:
-
in_parse
:此列指示会话当前是否正在执行解析操作。 -
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只需要看一本书就够了?