广东省建设职业注册中心网站,外贸软件,wordpress的主题博客,营销型手机网站建设小伙伴们#xff0c;有没有因为统计信息不准#xff0c;导致了业务卡顿#xff0c;各种状况频出#xff0c;这几天在实践和实操的过程中#xff0c;时不时就需要进行统计信息的收集。同时统计信息收集的动作也是OCM必考内容。
数据库中的数据是地图#xff0c;统计信息是…小伙伴们有没有因为统计信息不准导致了业务卡顿各种状况频出这几天在实践和实操的过程中时不时就需要进行统计信息的收集。同时统计信息收集的动作也是OCM必考内容。
数据库中的数据是地图统计信息是导航仪而优化器则是驾驶策略的制定者CBO依赖统计信息做出最优成本和路径选择。
一、统计信息功能
在Oracle数据库管理中统计信息收集Statistics Gathering是非常非常重要的环节直接影响着数据库的性能优化和查询效率。
优化查询性能
数据库优化器使用统计信息来评估执行计划的成本选择最优的执行路径。可以根据列的分布、表的大小、索引的选择性等信息来决定是全表扫描还是索引扫描。
自动调整执行计划
统计信息帮助数据库自动调整执行计划。随着数据的变化统计信息会更新这可以确保数据库持续使用最优的执行策略。避免因数据分布变化导致的执行计划突变Plan Flip
提高查询的准确性
统计信息提供了关于数据的精确度这对于估算查询结果的行数非常关键。在执行聚合查询如COUNT、SUM等时准确的统计信息可以确保返回的结果更加准确。
改善成本估算
数据库优化器通过统计信息来估算各种操作的成本如扫描行数、连接操作的开销等。这些估算用于选择最佳的查询执行计划。减少不必要的I/O和CPU消耗降低全表扫描风险
支持分区和物化视图
对于使用分区表和物化视图的数据库设计统计信息对于优化器的决策至关重要。分区表的选择性统计可以帮助优化器更有效地决定使用哪个分区。
支持高级功能
同样支持高级功能自动SQL调优、数据仓库优化器这些也将依赖于准确的统计信息来提供最佳的性能。
二、收集方式
1. 自动收集机制
Oracle通过GATHER_STATS_JOB自动任务实现智能收集 触发条件当DBA_TAB_MODIFICATIONS中记录的DML量 表行数的10%时标记为陈旧Stale时间窗口默认工作日晚10点-早6点及周末全天优先级策略先处理缺失统计信息对象再处理陈旧度高的对象 --启用命令BEGINDBMS_AUTO_TASK_ADMIN.ENABLE(client_name auto optimizer stats collection,operation NULL,window_name NULL);
END;
/
--PL/SQL procedure successfully completed.
--直接用表名和用户名可以做粗略收集其他按照列等选项收集需要细化
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(用户名);
EXEC DBMS_STATS.GATHER_TABLE_STATS(用户名, 表名);
2. 手动收集原则
以下场景需手动干预 ETL作业后避免优化器使用陈旧信息数据分布倾斜存在极端值的列需特殊处理性能敏感对象核心业务表结构变更后导出到导入必要收集统计信息 三、统计信息的要点
下面用SH Schema验证统计信息直接决定分区表SALES、大表CUSTOMERS的查询效率 优化器决策依据统计信息为CBO提供数据分布特征。举例 -- 查看SALES表统计信息关键字段
SELECT num_rows, blocks, avg_row_len
FROM dba_tables
WHERE ownerSH AND table_nameSALES;
--NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------0 0 0 若num_rows严重偏离实际值如因数据迁移未更新优化器可能错误选择全表扫描而非分区裁剪。 资源消耗优化准确的索引统计信息如CUSTOMERS_PK的聚簇因子可避免低效索引扫描 SELECT clustering_factor
FROM dba_indexes
WHERE index_nameCUSTOMERS_PK;
--
CLUSTERING_FACTOR
-----------------00
高聚簇因子接近表块数表明索引效率低需结合统计信息调整索引策略。
四、举例SH Schema的统计信息收集方法
自动收集策略优化
启用增量收集降低分区表开销
-- 开启SALES表增量统计
EXEC DBMS_STATS.SET_TABLE_PREFS(SH, SALES, INCREMENTAL, TRUE);
--PL/SQL procedure successfully completed.
-- 验证设置
SELECT preference_value
FROM dba_tab_stat_prefs
WHERE ownerSH AND table_nameSALES AND preference_nameINCREMENTAL;
--效果仅收集数据变更的分区。
PREFERENCE_VALUE
--------------------------------------------------------------------------
TRUE
设置用户SH自动收集任务 BEGINDBMS_SCHEDULER.CREATE_JOB (job_name gather_stats_job,job_type PLSQL_BLOCK,job_action BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(SH); END;,start_date SYSTIMESTAMP,repeat_interval FREQDAILY; INTERVAL1, -- 例如每天执行一次enabled TRUE,comments 自动收集统计信息);
END;
/
手动收集关键场景
1. 直方图精准控制
对偏斜字段SALES.AMOUNT_SOLD收集等高直方图
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname SH,tabname SALES,method_opt FOR COLUMNS SIZE 254 AMOUNT_SOLD, -- 254桶数degree 4);
END;
/
--
PL/SQL procedure successfully completed.
SYSFREE
-- 验证直方图
SELECT column_name, histogram, num_buckets
FROM dba_tab_cols
WHERE ownerSH AND table_nameSALES AND column_nameAMOUNT_SOLD;
--
COLUMN_NAME HISTOGRAM NUM_BUCKETS
______________ ____________ ______________
AMOUNT_SOLD NONE 0判定若HISTOGRAMHEIGHT BALANCED且NUM_BUCKETS100则有效反映数据分布。
2. 分区级统计验证
检测分区SALES_Q4_2001的陈旧状态--提前确认有分区
SELECT partition_name, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE ownerSH AND table_nameSALESAND partition_nameSALES_Q4_2024AND stale_statsYES; -- 陈旧状态检测
处理若返回记录需对该分区单独收集
--提前检测确认分区
EXEC DBMS_STATS.GATHER_TABLE_STATS(SH,SALES,partnameSALES_Q4_2024);
三、可验证监控脚本举例SH Schema
准确性验证
对比COSTS表统计行数 vs 实际行数
WITH actual AS (SELECT /* DYNAMIC_SAMPLING(4) */ COUNT(*) actual_rows FROM sh.costs
)
SELECT t.num_rows 统计行数,a.actual_rows 实际行数,ROUND(ABS((t.num_rows - a.actual_rows)/NULLIF(a.actual_rows,0))*100,2) diff_pct
FROM dba_tables t, actual a
WHERE t.ownerSH AND t.table_nameCOSTSAND ABS(t.num_rows - a.actual_rows) 10000; -- 差异1万行告警no rows selected
阈值建议diff_pct 5% 时需手动刷新统计。
自动任务健康监测
检查自动任务状态及失败历史
SELECT job_name, enabled, last_start_date,(SELECT COUNT(*) FROM dba_scheduler_job_run_details WHERE job_nameGATHER_STATS_JOB AND statusFAILED) fail_count
FROM dba_scheduler_jobs
WHERE job_nameGATHER_STATS_JOB
UNION ALL
-- 检查SH模式下统计信息锁定
SELECT STATS_LOCK, NULL, NULL, COUNT(*)
FROM dba_tab_statistics
WHERE ownerSH AND lockedYES;
JOB_NAME ENABLED LAST_START_DATE FAIL_COUNT
_____________ __________ __________________ _____________
STATS_LOCK 4890
处理逻辑 fail_count 0 → 检查 dba_scheduler_job_logSTATS_LOCK 0 → 使用 DBMS_STATS.UNLOCK_TABLE_STATS 解锁。 四、实操体会
在举例SH Schema中需重点关注 分区表增量统计降低90%收集开销尤其对时间分区字段如SALES.TIME_ID。偏斜字段直方图对金额/数量等高基数列AMOUNT_SOLD、QUANTITY_SOLD定制桶数。混合收集策略 自动任务处理日常变更ETL后对SALES/COSTS手动刷新使用 PENDING STATS 测试 EXEC DBMS_STATS.SET_TABLE_PREFS(SH,SALES,PUBLISH,FALSE);
--
PL/SQL procedure successfully completed.
TIPS 通过统计信息收集可精准验证统计信息对查询优化的实际影响实现从理论到高效运维的闭环在进行大量数据加载或数据修改后及时重新收集统计信息是非常重要的以确保优化器能基于最新数据做出正确的决策。在生产环境中建议定期监控统计信息的准确性和完整性必要时进行手动或自动的调整和重新收集过度频繁地收集统计信息可能会影响系统性能因为这会增加数据库的负载。因此应根据实际需要平衡收集频率和系统性能。
