Oracle 调优/分析表 [英] Oracle tuning / analyze tables

问题描述

安排自动分析表"的方法是什么.当大量数据通过插入和删除发生变化时，是否可以请求自动分析表"?参数化自动分析表过程的方法是什么，即设置应该触发的规则.

What are the means to schedule automatic "analyze tables". Is it possible to request an automatic "analyze tables" when a lot of data change througt insert and deletes? What are the means to parametrize the automatic analyze tables process, i.e. to set rules when it should be triggered.

推荐答案

当有很大比例的数据发生变化时，有没有办法触发这个作业"理论上，您可以在自动触发 DBMS_STATS 的表上执行 AFTER INSERT 触发器.

"Is there a way to have this job being triggered when there is a substantial proportion of data changed" In theory you could do an AFTER INSERT trigger on a table that automatically sets off DBMS_STATS.

但是要小心你想要什么.如果您正在执行大型 ETL 作业，并且刚刚在表中插入了 100 万行，您不一定希望立即自动启动 DBMS_STATS 作业.同样，当您处于最繁忙的在线处理时间(例如午餐)时，您不希望通过同时收集统计数据来减慢速度.

But be careful what you wish for. If you are in the middle of a large ETL job, having just inserted a million rows into the table, you don't necessarily want to automatically immediately set off a DBMS_STATS job. Equally, when you are at your busiest on-line processing time (eg lunch) you don't want to slow it down by gathering stats at the same time.

您通常希望这些统计信息收集在使用率较低的时段(夜间、周末)运行的作业.如果您有一个加载到许多表中的大批量运行，我会将 DBMS_STATS 构建到批处理中.

You generally want these stats gathering jobs running at low-usage periods (nights, weekends). If you have a large batch run that loads into a lot of tables, I'd build the DBMS_STATS into the batch.

这篇关于Oracle 调优/分析表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！