oracle 作为gather_schema_stats 的一部分收集物化视图的统计信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7455531/
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
Gather statistics on materialized views as part of gather_schema_stats
提问by grenade
We use the following to refresh statistics for all tables in a given schema:
我们使用以下内容来刷新给定模式中所有表的统计信息：
exec dbms_stats.gather_schema_stats(ownname => 'some_schema', estimate_percent => dbms_stats.auto_sample_size, cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 12);
This however, sets row-counts for our materialized views to zero and has the unwanted side effect of causing inefficient query plans for queries against materialized views. We work around this by gathering table stats against the specific mviews after the schema stats have run.
然而，这会将我们的物化视图的行数设置为零，并且会导致针对物化视图的查询的查询计划效率低下。我们通过在模式统计信息运行后针对特定 mviews 收集表统计信息来解决这个问题。
My question is: can I change the parameters to gather_schema_stats in any way that will cause mview row-counts not to be set to zero?
我的问题是：我可以以任何方式更改gather_schema_stats 的参数，从而导致mview 行计数不被设置为零吗？
采纳答案by Justin Cave
You can't tell GATHER_SCHEMA_STATSto exclude certain objects. You could do a GATHER STALEto gather statistics only on the objects where statistics are stale but it is entirely possible that would include your materialized views. A few ways to work around that
您无法判断GATHER_SCHEMA_STATS要排除某些对象。您可以GATHER STALE只收集统计信息陈旧的对象的统计信息，但完全有可能包括您的物化视图。解决这个问题的几种方法
1) Use the LOCK_TABLE_STATSprocedure to lock the statistics on your materialized views. That will prevent GATHER_SCHEMA_STATSfrom gathering statistics on those objects until you call the UNLOCK_TABLE_STATSprocedure (presumably as part of the process that refreshes the materialized view statistics periodically).
1) 使用该LOCK_TABLE_STATS过程锁定物化视图的统计信息。这将阻止GATHER_SCHEMA_STATS收集有关这些对象的统计信息，直到您调用该UNLOCK_TABLE_STATS过程（大概作为定期刷新物化视图统计信息的过程的一部分）。
2) Use the EXPORT_TABLE_STATSprocedure to save the statistics for the materialized views before gathering schema statistics and then call RESTORE_TABLE_STATSafter the GATHER_SCHEMA_STATScall completes to put the materialized view statistics back.
2) 使用该EXPORT_TABLE_STATS过程在收集模式统计信息之前保存物化视图的统计信息，然后RESTORE_TABLE_STATS在GATHER_SCHEMA_STATS调用完成后调用将物化视图统计信息放回原处。
3) Don't use GATHER_SCHEMA_STATS. Call GATHER_TABLE_STATSin a loop where you exclude whatever objects you want. Something like
3）不要使用GATHER_SCHEMA_STATS。GATHER_TABLE_STATS在一个循环中调用你排除任何你想要的对象。就像是
BEGIN
FOR x IN (SELECT *
FROM dba_tables
WHERE owner = 'SOME_SCHEMA'
AND table_name NOT IN (<<list of MVs>>))
LOOP
dbms_stats.gather_table_stats( x.owner, x.table_name, ... );
END LOOP;
END;
