Oracle 调优/分析表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1800124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle tuning / analyze tables
提问by paweloque
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.
安排自动“分析表”的方法是什么。当大量数据通过插入和删除发生变化时，是否可以请求自动“分析表”？对自动分析表过程进行参数化的方法是什么，即在应该触发时设置规则。
回答by Justin Cave
What version of Oracle are you using? From 10.1 on, Oracle has shipped with an automatic job that gathers statistics every night on any object that has been changed substantially (based on an internal algorithm subject to change, but I believe the threshold is ~15%) since that last time statistics were gathered. Assuming you are using a currently supported release, statistics are gathered automatically by default unless you explicitly disable that job.
您使用的是哪个版本的 Oracle？从 10.1 开始，Oracle 提供了一项自动作业，该作业每晚收集自上次统计以来发生重大变化的任何对象的统计信息（基于可能发生变化的内部算法，但我认为阈值约为 15%）聚集。假设您使用的是当前支持的版本，默认情况下会自动收集统计信息，除非您明确禁用该作业。
You can use the DBMS_STATSpackage to gather statistics on an object based on an event and you can use the DBMS_JOB(or DBMS_SCHEDULER) package to have that done asynchronously. So at the end of an ETL process, you can gather statistics
您可以使用该DBMS_STATS包根据事件收集对象的统计信息，并且可以使用DBMS_JOB(或DBMS_SCHEDULER) 包异步完成该操作。所以在 ETL 过程结束时，您可以收集统计信息
BEGIN
dbms_stats.gather_table_stats(
ownname => <<schema name>>,
tabname => <<table name>>
);
END;
You can have that run asynchronously
你可以让它异步运行
DECLARE
l_jobno INTEGER;
BEGIN
dbms_job.submit(
l_jobno,
'BEGIN dbms_stats.gather_table_stats( ''<<schema name>>'', ''<<table name>>'' ); END;',
sysdate + interval '1' minute
);
END;
回答by Gary Myers
"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.
“当有很大比例的数据更改时，有没有办法触发这项工作”理论上，您可以在自动触发 DBMS_STATS 的表上执行 AFTER INSERT 触发器。
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.
但要小心你想要什么。如果您正处于大型 ETL 作业的中间，刚刚在表中插入了一百万行，您不一定希望立即自动启动 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.
您通常希望这些统计数据收集在使用率较低的时段（夜间、周末）运行的作业。如果您有一个加载到许多表中的大批量运行，我会将 DBMS_STATS 构建到批处理中。
