收集物化视图的统计信息作为 Gather_schema_stats 的一部分

发布于 2024-12-05 11:35:56 字数 424 浏览 3 评论 0原文

我们使用以下内容来刷新给定模式中所有表的统计信息：

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);

但是，这会将物化视图的行计数设置为零，并且会产生不良副作用，导致针对物化视图的查询效率低下。我们通过在架构统计数据运行后针对特定 mview 收集表统计数据来解决此问题。

我的问题是：我可以以任何方式更改 Gather_schema_stats 的参数，从而导致 mview 行计数不设置为零吗？

原文

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.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄意 2024-12-12 11:35:56

您无法告诉 GATHER_SCHEMA_STATS 排除某些对象。您可以执行 GATHER STALE 来仅收集统计信息过时的对象的统计信息，但完全有可能包括您的物化视图。有几种方法可以解决这个问题

1) 使用LOCK_TABLE_STATS过程锁定物化视图上的统计信息。这将阻止 GATHER_SCHEMA_STATS 收集这些对象的统计信息，直到您调用 UNLOCK_TABLE_STATS 过程（大概是定期刷新物化视图统计信息的过程的一部分）。

2) 在收集模式统计信息之前使用EXPORT_TABLE_STATS过程保存物化视图的统计信息，然后在GATHER_SCHEMA_STATS调用完成后调用RESTORE_TABLE_STATS来放置物化视图统计返回。

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;

You can't tell GATHER_SCHEMA_STATS to exclude certain objects. You could do a GATHER STALE to 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

1) Use the LOCK_TABLE_STATS procedure to lock the statistics on your materialized views. That will prevent GATHER_SCHEMA_STATS from gathering statistics on those objects until you call the UNLOCK_TABLE_STATS procedure (presumably as part of the process that refreshes the materialized view statistics periodically).

2) Use the EXPORT_TABLE_STATS procedure to save the statistics for the materialized views before gathering schema statistics and then call RESTORE_TABLE_STATS after the GATHER_SCHEMA_STATS call completes to put the materialized view statistics back.

3) Don't use GATHER_SCHEMA_STATS. Call GATHER_TABLE_STATS in a loop where you exclude whatever objects you want. Something like

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;

回复收藏 0 原文

~没有更多了~