Oracle-高频业务表的性能检查

说明：

大家都知道，当业务运行很长一段时间后，由于部分业务表频繁被增删改数据，会导致表以及索引的碎片化越来越严重，严重影响到数据库的性能，本文就教大家如何定期去检查数据库表的使用情况！
（都是笔者宝贵的经验总结！！）

1、表碎片化

查询当前用户下，超过100万表的碎片化情况

FRAG代表利用率，<0.3建议进行重建处理

SELECT * FROM (
  SELECT
    t.table_name,
    t.num_rows,
    t.avg_row_len * t.num_rows AS actual_data_size,
    s.total_bytes AS allocated_size,
    (t.avg_row_len * t.num_rows) / s.total_bytes AS frag
  FROM user_tables t
  JOIN (
    SELECT 
      segment_name,
      SUM(bytes) AS total_bytes
    FROM user_segments
    WHERE segment_type = 'TABLE'  -- 只考虑表段
    GROUP BY segment_name
  ) s ON t.table_name = s.segment_name
  WHERE t.num_rows > 1000000  -- 只检查超过100万行的表
    AND (t.avg_row_len * t.num_rows) / s.total_bytes < 0.7
  ORDER BY frag ASC
)
WHERE ROWNUM <= 20;

2、表的行迁移、行链接检查

业务用户需要被授予查询v_$parameter视图的权限才可以进行以下检测

GRANT SELECT ON v_$parameter TO 用户名;

如果数据库中存在行链接，行迁移，将会严重影响数据库的性能（访问的块变多了，IO增加）如果指标严重，建议重建表

SELECT
    table_name AS "表名",
    num_rows AS "总行数",
    chain_cnt AS "行迁移/链接行数",
    ROUND((chain_cnt / NULLIF(num_rows, 0)) * 100, 2) AS "问题行占比(%)",
    avg_row_len AS "平均行长度(字节)",
    (SELECT value FROM v$parameter WHERE name = 'db_block_size') AS "块大小(字节)",
    CASE
        WHEN chain_cnt IS NULL OR num_rows = 0 THEN '无数据'
        WHEN chain_cnt = 0 THEN '无问题'
        WHEN (chain_cnt / num_rows) < 0.05 THEN '轻微'
        WHEN (chain_cnt / num_rows) < 0.2 THEN '中度'
        ELSE '严重'
    END AS "严重程度"
FROM user_tables
WHERE 
chain_cnt > 0  -- 只显示有问题的表
ORDER BY "问题行占比(%)" DESC;

3、表的高水位线检查

业务用户需要被授予查询v_$parameter视图的权限才可以进行以下检测

GRANT SELECT ON v_$parameter TO 用户名;

水位线过高会导致不必要的块扫描浪费性能，浪费比例超过50%，建议重建表

SELECT 
    table_name AS "表名",
    blocks AS "HWM以下块数",
    empty_blocks AS "HWM以上空块数",
    blocks + empty_blocks AS "总分配块数",
    (SELECT value FROM v$parameter WHERE name = 'db_block_size') AS "块大小(字节)",
    ROUND((blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024, 2) AS "HWM以下空间(MB)",
    ROUND((empty_blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024, 2) AS "HWM以上空间(MB)",
    ROUND(((blocks + empty_blocks) * (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024, 2) AS "总分配空间(MB)",
    -- 新增浪费比率计算
    ROUND(
        CASE WHEN blocks + empty_blocks > 0 
             THEN (empty_blocks / (blocks + empty_blocks)) * 100 
             ELSE 0 
        END,
        2
    ) AS "浪费比率(%)"
FROM dba_tables
 where (blocks > 0 OR empty_blocks > 0)
ORDER BY "浪费比率(%)" DESC, "总分配空间(MB)" DESC;

4、重建表方法

create table的方式进行重建

新建表结构

create table test_new as select * from test where 1=2;

等待业务无更新期间，进行插入

insert into test_new select * from test;

根据原表创建完索引后，建议收集重新收集该表统计信息，方法见8

修改表名,完成重建

alter table test_new rename to test;

重建完成以后，注意检查是否有外键关系需要更新

5、索引失效问题

查询当前用户下是否有失效的索引，失效的索引建议重建

SELECT 
  INDEX_NAME, 
  TABLE_NAME, 
  STATUS 
FROM 
  user_indexes 
WHERE 
  STATUS = 'INVALID';

6、索引碎片、高度检查

此方法需要先定位问题表，手动分析问题表索引以后才能进行检查

ANALYZE INDEX 索引名 VALIDATE STRUCTURE;

查询索引碎片以及索引高度,碎片严重或者高度过高建议重建索引

SELECT 
  name AS 索引名,
  HEIGHT AS 索引高度,
  PCT_USED AS 空间利用率,
  ROUND(DEL_LF_ROWS / NULLIF(LF_ROWS, 0) * 100, 2) AS 碎片率百分比,
  CASE 
    WHEN HEIGHT >= 4 THEN '高度异常: 需重建'
    WHEN DEL_LF_ROWS / NULLIF(LF_ROWS, 0) > 0.3 THEN '严重碎片化'
    ELSE '状态正常'
  END AS 健康状态
FROM index_stats;

7、索引重建

alter index 索引名 rebuild online;

8、统计信息检查

用户需要有dba_tab_statistics的查询权限

grant select on dba_tab_statistics to 用户名;

查询指定用户下表的统计信息收集情况，last_analyzed代表最后一次收集统计信息的时间，stale_stats如果是YES表示已经过期，如果某表查询突然变慢，且统计信息过期，建议重新收集

select owner, table_name, object_type, stale_stats, num_rows, last_analyzed
  from dba_tab_statistics
  where owner = '用户名'

收集统计信息

begin
dbms_stats.gather_table_stats
(ownname => '用户名',
tabname => '表名',
granularity => 'ALL',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size auto',
no_invalidate => false,
degree => 4,
cascade => true);
END;
/