Oracle 使用dbms_stats.gather_table_stats来进行表analyse，收集表统计信息

---

一. 介绍

DBMS_STATS.GATHER_TABLE_STATS 用于收集 表 级别的统计信息。这些统计信息有助于查询优化器优化查询计划，影响与表本身相关的查询性能。
Oracle 查询优化器会根据表的统计信息来选择最优的执行计划。当运行 DBMS_STATS.GATHER_TABLE_STATS 时，它会收集表的统计信息（例如：表的行数、列的基数、空值数目、数据分布等），这些信息会被存储在数据字典中。优化器使用这些统计信息来估算查询的成本，从而决定使用哪种执行计划。

⭐可以提优化查询计划，提升表的查询速度。

⏹注意事项

• 如果表的数据量较大且数据分布发生了变化（如添加了很多新数据或删除了大量数据），则执行 DBMS_STATS.GATHER_TABLE_STATS 是必要的，才能保证优化器使用最新的表信息。
• 频繁的统计信息更新并不总是有益的：如果你有稳定的数据负载并且查询性能已经稳定，频繁地收集统计信息可能不会显著改善性能，反而可能会增加系统负担。

---

二. 参数说明

• ownname
  • 数据类型：VARCHAR2
  • schema名称，表的所有者
• tabname
  • 数据类型：VARCHAR2
  • 要收集信息的表的名称
• estimate_percent：
  • 数据类型：NUMBER
  • 用于估算样本的百分比（0 到 100），用于估算索引的统计信息。较低的百分比会减少收集时间，但可能会影响统计信息的准确性。
  • 默认值：DBMS_STATS.AUTO_SAMPLE_SIZE （自动选择样本大小）。
• degree
  • 数据类型：NUMBER
  • 表示收集统计信息时要使用的并行度。如果设置为 0 或者省略，则不使用并行度。
  • 默认值：DBMS_STATS.DEFAULT_DEGREE（即通常不使用并行度）。
• cascade
  • 数据类型：BOOLEAN
  • 是否收集索引表的统计信息。如果设置为 TRUE，则会在收集索引统计信息时，也收集该索引所在表的统计信息。
  • 默认值：FALSE
• no_invalidate
  • 数据类型：BOOLEAN
  • 是否在收集统计信息后不使所有依赖此索引的查询计划失效。如果设置为 TRUE，则不使查询计划失效。
    • 当设置为 FALSE 时，收集统计信息后不会使现有的执行计划失效。换句话说，如果索引统计信息有变化，优化器不会重新计算查询的执行计划。
    • 如果设置为 TRUE，则会使相关的执行计划失效，并重新生成新的执行计划。
    • 通常，设置为 FALSE 是比较安全的做法，避免不必要的性能波动。
  • 默认值：FALSE（使查询计划失效）

DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 参数值,
    tabname => 参数值,
    estimate_percent => 参数值,
    degree => 参数值,
    cascade => 参数值,
    no_invalidate => 参数值
);

---

三. 简易封装

⏹新建一个table_stats_analyse.sql的sql脚本，封装dbms_stats.gather_table_stats方法

• 通过传参的方式指定要收集信息的表名
• 可以将要执行的sql脚本打印在控制台上
• 参数
  • &&1：oracle数据库用户名
  • &&2：oracle数据库密码
  • &&3：tnsnames.ora文件中配置的service名称
  • &&4：表的所有者
  • &&5：表名

set echo on
set pagesize 1000
set linesize 1000
-- 启用 DBMS_OUTPUT ,打印指定内容到控制台
set serveroutput on 

-- 定义错误code
define ERR_CD = 2

-- 设置回滚条件
whenever sqlerror exit &ERR_CD rollback;
whenever oserror exit &ERR_CD rollback;

-- 连接数据库
connect &&1/&&2@&&3

DECLARE
    v_sql_text         VARCHAR2(4000);
    v_ownname          VARCHAR2(15) := '&&4';
    v_tabname          VARCHAR2(50) := '&&5';
    v_estimate_percent NUMBER := NVL(TO_NUMBER('&&6'), DBMS_STATS.AUTO_SAMPLE_SIZE);
    v_degree           NUMBER := NVL(TO_NUMBER('&&7'), DBMS_STATS.DEFAULT_DEGREE);
BEGIN
    -- 拼接动态内容
    v_sql_text := 'DBMS_STATS.GATHER_TABLE_STATS(' || CHR(10) ||
                  '    ownname => ''' || v_ownname || ''',' || CHR(10) ||
                  '    tabname => ''' || v_tabname || ''',' || CHR(10) ||
                  '    estimate_percent => ' || v_estimate_percent || ',' || CHR(10) ||
                  '    degree => ' || v_degree || ',' || CHR(10) ||
                  '    cascade => TRUE,' || CHR(10) ||
                  '    no_invalidate => FALSE' || CHR(10) ||
                  ');';
    
    -- 打印到控制台
DBMS_OUTPUT.PUT_LINE('=========================================');
    DBMS_OUTPUT.PUT_LINE('Executing SQL:');
    DBMS_OUTPUT.PUT_LINE('=========================================');
    DBMS_OUTPUT.PUT_LINE(v_sql_text);
    DBMS_OUTPUT.PUT_LINE('=========================================');

    -- 取得统计情报
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => v_ownname,
        tabname => v_tabname,
        estimate_percent => v_estimate_percent,
        degree => v_degree,
        cascade => TRUE,
        no_invalidate => FALSE
    );
END;
/

-- 退出
exit 0

---

四. 效果

⏹sqlplus -S /nolog @/home/apluser/work/table_stats_analyse.sql db_user oracle SERVICE_XEPDB1_CLIENT db_user CHARGE_FIXED_WORK 10 16

⏹sqlplus -S /nolog @/home/apluser/work/table_stats_analyse.sql db_user oracle SERVICE_XEPDB1_CLIENT db_user CHARGE_FIXED_WORK

⏹在表analyse完之后，可以通过下面的SQL查看index索引的LAST_ANALYZED时间
👉因为我们设置了 cascade => TRUE，所以在分析表信息的时候，索引的信息一会一并被分析。

SELECT
    INDEX_NAME
    , INDEX_TYPE
    , TABLE_OWNER
    , TABLE_NAME
    , TABLE_TYPE
    , TO_CHAR(last_analyzed, 'YYYY/MM/DD HH24:MI:SS') AS LAST_ANALYZED
FROM
    USER_INDEXES 
WHERE
    INDEX_NAME = 'CHARGEFIXEDWORK_DB1';

原文地址：https://blog.csdn.net/feyehong/article/details/145099157

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！