Oracle RAC环境下获取Top10用户SQL查询的需求及问题

问题背景 #

需要一条可在Oracle RAC所有节点执行的SQL语句，筛选出基于CPU执行时间和执行次数的Top10用户查询，但当前运行的PL/SQL代码仅返回系统查询，无法获取目标用户查询。

原代码如下：

SET SERVEROUTPUT ON
SET LINESIZE 200
SET PAGESIZE 1000
SET FEEDBACK OFF

-- 基于执行耗时的Top10查询
DECLARE
  CURSOR query_cursor IS
    SELECT
      ROWNUM AS rank,
      v.sql_id,
      SUBSTR(v.sql_text, 1, 120) AS truncated_sql,
      TO_CHAR(v.elapsed_time / 1000000, '999999.9') AS elapsed_seconds,
      v.executions
    FROM (
      SELECT
        sql_id,
        sql_text,
        elapsed_time,
        executions
      FROM
        gv$sqlstats
      ORDER BY
        elapsed_time DESC
    ) v
    WHERE ROWNUM <= 10;

  v_rank NUMBER;
  v_sql_id VARCHAR2(13);
  v_truncated_sql VARCHAR2(120);
  v_elapsed_seconds VARCHAR2(10);
  v_executions NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Rank| SQL_ID    |                                        Truncated SQL                                                                    | Elapsed(S)| NbExec');
  DBMS_OUTPUT.PUT_LINE('--|-------------|-------------------------------------------------------------------------------------------------------------------------|-----------|--------|');

  FOR result IN query_cursor LOOP
    v_rank := result.rank;
    v_sql_id := result.sql_id;
    v_truncated_sql := result.truncated_sql;
    v_elapsed_seconds := result.elapsed_seconds;
    v_executions := result.executions;
    DBMS_OUTPUT.PUT_LINE(
      RPAD(TO_CHAR(v_rank),2) || '|' ||
      RPAD(v_sql_id, 13) || '|' ||
      RPAD(v_truncated_sql, 121) || '|' ||
      RPAD(v_elapsed_seconds, 10) || ' |' ||
      RPAD(TO_CHAR(v_executions), 5)|| '   |'
    );
  END LOOP;
END;
/

原执行结果：

解决方案 #

修改后的PL/SQL代码 #

针对原代码的问题，调整后可精准筛选用户查询，适配RAC全节点统计：

SET SERVEROUTPUT ON
SET LINESIZE 200
SET PAGESIZE 1000
SET FEEDBACK OFF

-- 基于CPU执行时间与执行次数的Top10用户查询（RAC全节点）
DECLARE
  CURSOR query_cursor IS
    SELECT
      ROWNUM AS rank,
      v.sql_id,
      SUBSTR(v.sql_text, 1, 120) AS truncated_sql,
      TO_CHAR(v.total_cpu_time / 1000000, '999999.9') AS cpu_seconds,
      v.total_executions
    FROM (
      SELECT
        sql_id,
        sql_text,
        SUM(cpu_time) AS total_cpu_time,
        SUM(executions) AS total_executions
      FROM
        gv$sqlstats
      WHERE
        -- 排除系统SQL
        is_system_sql = 'N'
        -- 排除空SQL或仅注释的语句
        AND sql_text IS NOT NULL
        AND TRIM(sql_text) NOT LIKE '--%'
        AND TRIM(sql_text) NOT LIKE '/*%'
      GROUP BY
        sql_id, sql_text
      ORDER BY
        total_cpu_time DESC
    ) v
    WHERE ROWNUM <= 10;

  v_rank NUMBER;
  v_sql_id VARCHAR2(13);
  v_truncated_sql VARCHAR2(120);
  v_cpu_seconds VARCHAR2(10);
  v_total_executions NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Rank| SQL_ID    |                                        Truncated SQL                                                                    | CPU耗时(S)| 总执行次数');
  DBMS_OUTPUT.PUT_LINE('--|-------------|-------------------------------------------------------------------------------------------------------------------------|-----------|------------|');

  FOR result IN query_cursor LOOP
    v_rank := result.rank;
    v_sql_id := result.sql_id;
    v_truncated_sql := result.truncated_sql;
    v_cpu_seconds := result.cpu_seconds;
    v_total_executions := result.total_executions;
    DBMS_OUTPUT.PUT_LINE(
      RPAD(TO_CHAR(v_rank),2) || '|' ||
      RPAD(v_sql_id, 13) || '|' ||
      RPAD(v_truncated_sql, 121) || '|' ||
      RPAD(v_cpu_seconds, 10) || ' |' ||
      RPAD(TO_CHAR(v_total_executions), 10)|| '   |'
    );
  END LOOP;
END;
/

关键修改说明 #

• 过滤系统SQL：通过is_system_sql = 'N'直接排除Oracle内部执行的系统语句
• RAC节点聚合：使用SUM(cpu_time)和SUM(executions)汇总所有节点的统计数据，避免单节点数据偏差
• 排序逻辑调整：将排序字段从总耗时elapsed_time改为CPU执行时间total_cpu_time，匹配需求
• 过滤无效SQL：排除空SQL和仅包含注释的语句，避免无效条目干扰结果

内容的提问来源于stack exchange，提问作者smail Yaakoubi