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

说明：

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

1、表碎片化

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

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

SELECT * FROM (SELECTt.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 fragFROM user_tables tJOIN (SELECT segment_name,SUM(bytes) AS total_bytesFROM user_segmentsWHERE segment_type = 'TABLE'  -- 只考虑表段GROUP BY segment_name) s ON t.table_name = s.segment_nameWHERE t.num_rows > 1000000  -- 只检查超过100万行的表AND (t.avg_row_len * t.num_rows) / s.total_bytes < 0.7ORDER BY frag ASC
)
WHERE ROWNUM <= 20;

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

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

GRANT SELECT ON v_$parameter TO 用户名;

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

SELECTtable_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 "块大小(字节)",CASEWHEN 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_tableswhere (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_analyzedfrom dba_tab_statisticswhere 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;
/