DBA必备脚本：比SQLHC好用100倍的SQL性能优化脚本版本迭代v1.0版本

huangtingzhong 发布于 2025-09-28 22:45SQL, 工具抢沙发

我们的文章会在微信公众号IT民工的龙马人生和博客网站 ( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

双节将近，最近几天都在忙于给客户做数据库节前巡检的工作。巡检过程中，性能调试是巡检最核心的内容，每次利用巡检也帮助客户优化一些TOP SQL语句。对我自己来说，每次看到有TOP SQL语句，心里面就有冲动想去做调优。

今天在调优过程中采用sql10.sql的脚本收集信息后，在分析sql性能时，有一个时间列的范围过滤，根据表的统计信息的时间和执行计划中预估的行数，可以明显的发现统计信息不准，使得SQL走错了执行计划，但是sql10.sql中并未收集相关的信息，这就是列的最小值和最大值的统计信息。这个值常常用于谓词越界的场景中，比如经典的按月建分区的情况，或者医院行业中常常根据时间来查询最近的信息等等。于是在脚本立马把这部分的信息添加上。

脚本通过引入了一个新的参数_TABLE_COL_VALUE来控制是否收集列的统计信息的最小值和最大值，默认参数的值为–，也就不收集这部分信息，原因如下：

会涉及到现实列的真实值，关系到业务数据，所以安全是第一需要考虑的。
SQL10的执行性能，采集MIN和VALUE的最大值会调用dbms_stats存储过程中的函数，效率相对来说比较慢，可能需要几秒的时间，具体时间根据列多少和计算能力有关系。

如果想收集，在脚本开头参数定义部分将_TABLE_COL_VALUE的参数为=’ ‘即可。

注意：

目前只覆盖列类型为：date、varchar2、char、number4种类型的字段，其它字段会返回ERROR，同时返回的值也做了截断，只显示前30个字符，考虑到命令行模式的列的宽度有限。

关注公众号，后台回复“脚本”二字加群，已经加群的直接下载脚本即可。

对应代码如下：

define _TABLE_COL_VALUE    = "--"
DECLARE
     CURSOR c_stats IS
         WITH t AS
         (SELECT /*+ materialize */DISTINCT OBJECT_OWNER, OBJECT_NAME
                   FROM (SELECT OBJECT_OWNER, OBJECT_NAME
                           FROM V$SQL_PLAN
                          WHERE SQL_ID = :sql_id_bind
                            AND OBJECT_NAME IS NOT NULL
                         UNION ALL
                         SELECT OBJECT_OWNER, OBJECT_NAME
                           FROM DBA_HIST_SQL_PLAN
                          WHERE SQL_ID = :sql_id_bind
                            AND OBJECT_NAME IS NOT NULL))
         SELECT tb.OWNER,
                tb.TABLE_NAME,
                tb.COLUMN_NAME,
                tb.data_type || '(' || tb.data_length || ')' d_type,
                tb.data_type,
                s.low_value,
                s.high_value
           FROM DBA_TAB_COLS tb
           LEFT JOIN dba_tab_col_statistics s
             ON tb.owner = s.owner
            AND tb.table_name = s.table_name
            AND tb.column_name = s.column_name
          WHERE (tb.OWNER, tb.TABLE_NAME) IN
                (SELECT table_owner,table_name FROM dba_indexes
                  WHERE (owner,index_name) IN (SELECT * FROM t)
                 UNION ALL SELECT * FROM t)
          ORDER BY tb.owner, tb.table_name, tb.COLUMN_ID;

     v_number NUMBER;
     v_date DATE;
     v_varchar VARCHAR2(4000);
     v_min_readable VARCHAR2(30);
     v_max_readable VARCHAR2(30);
     v_output_line VARCHAR2(200);
BEGIN
     DBMS_OUTPUT.PUT_LINE(RPAD('OWNER', 15) || ' ' ||
                         RPAD('TABLE_NAME', 30) || ' ' ||
                         RPAD('COLUMN_NAME', 20) || ' ' ||
                         RPAD('COLUMN_TYPE', 15) || ' ' ||
                         RPAD('MIN_VALUE', 30) || ' ' ||
                         RPAD('MAX_VALUE', 30));
     DBMS_OUTPUT.PUT_LINE(RPAD('-', 15, '-') || ' ' ||
                         RPAD('-', 30, '-') || ' ' ||
                         RPAD('-', 20, '-') || ' ' ||
                         RPAD('-', 15, '-') || ' ' ||
                         RPAD('-', 30, '-') || ' ' ||
                         RPAD('-', 30, '-'));

&_TABLE_COL_VALUE     FOR rec IN c_stats LOOP
&_TABLE_COL_VALUE         BEGIN
&_TABLE_COL_VALUE             IF rec.low_value IS NULL THEN
&_TABLE_COL_VALUE                 v_min_readable := 'NULL';
&_TABLE_COL_VALUE             ELSE
&_TABLE_COL_VALUE                 CASE rec.data_type
&_TABLE_COL_VALUE                     WHEN 'NUMBER' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.low_value, v_number);
&_TABLE_COL_VALUE                         v_min_readable := TO_CHAR(v_number);
&_TABLE_COL_VALUE                     WHEN 'DATE' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.low_value, v_date);
&_TABLE_COL_VALUE                         v_min_readable := TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS');
&_TABLE_COL_VALUE                     WHEN 'VARCHAR2' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.low_value, v_varchar);
&_TABLE_COL_VALUE                         v_min_readable := v_varchar;
&_TABLE_COL_VALUE                     WHEN 'CHAR' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.low_value, v_varchar);
&_TABLE_COL_VALUE                         v_min_readable := v_varchar;
&_TABLE_COL_VALUE                     ELSE
&_TABLE_COL_VALUE                         v_min_readable := 'N/A';
&_TABLE_COL_VALUE                 END CASE;
&_TABLE_COL_VALUE             END IF;
&_TABLE_COL_VALUE         EXCEPTION
&_TABLE_COL_VALUE             WHEN OTHERS THEN
&_TABLE_COL_VALUE                 v_min_readable := 'ERROR';
&_TABLE_COL_VALUE         END;
&_TABLE_COL_VALUE
&_TABLE_COL_VALUE         -- 转换 MAX_VALUE
&_TABLE_COL_VALUE         BEGIN
&_TABLE_COL_VALUE             IF rec.high_value IS NULL THEN
&_TABLE_COL_VALUE                 v_max_readable := 'NULL';
&_TABLE_COL_VALUE             ELSE
&_TABLE_COL_VALUE                 CASE rec.data_type
&_TABLE_COL_VALUE                     WHEN 'NUMBER' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.high_value, v_number);
&_TABLE_COL_VALUE                         v_max_readable := TO_CHAR(v_number);
&_TABLE_COL_VALUE                     WHEN 'DATE' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.high_value, v_date);
&_TABLE_COL_VALUE                         v_max_readable := TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS');
&_TABLE_COL_VALUE                     WHEN 'VARCHAR2' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.high_value, v_varchar);
&_TABLE_COL_VALUE                         v_max_readable := v_varchar;
&_TABLE_COL_VALUE                     WHEN 'CHAR' THEN
&_TABLE_COL_VALUE                         DBMS_STATS.CONVERT_RAW_VALUE(rec.high_value, v_varchar);
&_TABLE_COL_VALUE                         v_max_readable := v_varchar;
&_TABLE_COL_VALUE                     ELSE
&_TABLE_COL_VALUE                         v_max_readable := 'N/A';
&_TABLE_COL_VALUE                 END CASE;
&_TABLE_COL_VALUE             END IF;
&_TABLE_COL_VALUE         EXCEPTION
&_TABLE_COL_VALUE             WHEN OTHERS THEN
&_TABLE_COL_VALUE                 v_max_readable := 'ERROR';
&_TABLE_COL_VALUE         END;
&_TABLE_COL_VALUE         IF LENGTH(v_min_readable) > 30 THEN
&_TABLE_COL_VALUE             v_min_readable := SUBSTR(v_min_readable, 1, 27) || '...';
&_TABLE_COL_VALUE         END IF;
&_TABLE_COL_VALUE
&_TABLE_COL_VALUE         IF LENGTH(v_max_readable) > 30 THEN
&_TABLE_COL_VALUE             v_max_readable := SUBSTR(v_max_readable, 1, 27) || '...';
&_TABLE_COL_VALUE         END IF;
&_TABLE_COL_VALUE         DBMS_OUTPUT.PUT_LINE(RPAD(rec.OWNER, 15) || ' ' ||
&_TABLE_COL_VALUE                             RPAD(rec.TABLE_NAME, 30) || ' ' ||
&_TABLE_COL_VALUE                             RPAD(rec.COLUMN_NAME, 20) || ' ' ||
&_TABLE_COL_VALUE                             RPAD(rec.d_type, 15) || ' ' ||
&_TABLE_COL_VALUE                             RPAD(v_min_readable, 30) || ' ' ||
&_TABLE_COL_VALUE                             RPAD(v_max_readable, 30));
&_TABLE_COL_VALUE     END LOOP;
 END;
/

——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)