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

2024年 7月 31日 70.3k 0

我的这篇文章的英文版被Oracle大师Roger Cornejo在X平台上推荐(见下图),英文原文在:

Diagnosing Parsing Issue with ASH

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

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

解析,尤其是硬解析,是非生产性操作,会消耗大量系统资源,导致库缓存争用。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的查询通常更准确。在这种情况下,两个查询产生相同的输出,如下所示(为简洁起见进行了截断):

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

                输出显示了文本上相似的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);

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

                    输出只包含两条已执行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,发明两项计算机专利

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

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

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

                    推荐文章👇

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

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

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

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

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

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

                    相关文章

                    Oracle如何使用授予和撤销权限的语法和示例
                    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
                    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
                    社区版oceanbase安装
                    Oracle 导出CSV工具-sqluldr2
                    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

                    发布评论