Oracle 手动更新所有表的统计信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16380732/
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 manually update statistics on all tables
提问by Tom
Is there a way to update all statistics for all tables regardless of the owner?
无论所有者如何，有没有办法更新所有表的所有统计信息？
I found this sniplet, but I'm not sure if this will grab all tables....
我找到了这个 sniplet，但我不确定这是否会抓取所有表....
BEGIN
FOR A IN ( SELECT owner FROM SYS.all_tables ) LOOP
execute immediate
EXEC dbms_stats.gather_schema_stats( 'A.owner', cascade='TRUE');
END LOOP;
END;
回答by Jon Heller
回答by sehrope
No the DBMS_STATSpackage can do at most one schema at a time.
否DBMS_STATS包可以一次最多一个模式去做。
You can use the script below to gather stats for all objects types in all schemas. The one you listed has a couple of issues (needless execute immediate, `A.owner' is a string but it should be an object, etc).
您可以使用下面的脚本来收集所有模式中所有对象类型的统计信息。您列出的那个有几个问题（不需要立即执行，'A.owner' 是一个字符串，但它应该是一个对象等）。
You can add additional schemas to skip in the INlist as you probably don't want to do this for the built in schemas (they're mostly static anyway so it'd be waste). Also, you'll need to have the appropriate privileges for each schema you are gathering stats on (or be logged in as a DBA).
您可以添加其他模式以跳过IN列表，因为您可能不想为内置模式执行此操作（无论如何它们大多是静态的，因此会浪费）。此外，您需要为正在收集统计信息的每个模式（或以 DBA 身份登录）拥有适当的权限。
Gather stats on all objects (probably what you really want):
收集所有对象的统计信息（可能是你真正想要的）：
BEGIN
FOR rec IN (SELECT *
FROM all_users
WHERE username NOT IN ('SYS','SYSDBA'))
LOOP
dbms_stats.gather_schema_stats(rec.username);
END LOOP;
END;
Gather stats on just tables:
仅收集表格的统计信息：
BEGIN
FOR rec IN (SELECT *
FROM all_tables
WHERE owner NOT IN ('SYS','SYSDBA'))
LOOP
dbms_stats.gather_table_stats(rec.owner, rec.table_name);
END LOOP;
END;
