oracle 识别模式列表中具有陈旧统计信息的分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21250093/
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
Identify partitions having stale statistics in a list of schema
提问by Devaraj Mahesan
I have 5 development schemas. And each of them have partitioned tables. We also have scripts to dynamically create partition tables (Monthly/Yearly). We have to go to DBA everytime for gathering the details over the parition tables. Our real problem is we do have a parition table with 9 partitions. Every day after a delta load operation (Updates/Deletes using a PL/SQL) also some APPENDload using SQL*Loader. This operation happens when database has the peak load. We do have some performace issues over this table.(SELECTqueries)
我有 5 个开发模式。他们每个人都有分区表。我们还有脚本来动态创建分区表（每月/每年）。我们每次都必须去找 DBA 来收集分区表的详细信息。我们真正的问题是我们确实有一个包含 9 个分区的分区表。每天在增量加载操作（使用 更新/删除PL/SQL）之后，也会APPEND使用SQL*Loader. 此操作发生在数据库具有峰值负载时。我们确实在这个表上有一些性能问题。（SELECT查询）
When reported to DBA, they would say the table statistics are stale and after they do "gathering stats", magically the query works faster. I searched about this and identified some information about dynamic performanceviews.
当报告给 DBA 时，他们会说表统计信息已经过时，并且在他们进行“收集统计信息”之后，查询的运行速度奇迹般地更快了。我搜索了这个并确定了一些关于dynamic performance视图的信息。
So, now , I have the following Questions.
所以，现在，我有以下问题。
1) Can the developer generate a the list of all partitionon tables, partition name, no of records available without going to DBA?
2) Shall we identify the last analysed date of every parition
3) Also the status of the parition(index) if it usable or unusable.
1) 开发人员能否在不去 DBA 的情况下生成所有分区表、分区名称、可用记录数的列表？
2) 我们是否应该确定每个分区的最后分析日期
3) 还有分区（索引）的状态，如果它可用或不可用。
采纳答案by Maheswaran Ravisankar
I used to query all_ tables mentioned below.
我曾经查询过下面提到的所有_表。
The statistics and histogram details you mention will be updated in a frequency automatically by Oracle. But when the database is busy with many loads, I have seen these operations needs to be triggered manually. We faced similar situation, so we used to force the Analyze operation after our load for critical tables. You need to have privilege for the id you use to load the table.
您提到的统计数据和直方图详细信息将由 Oracle 自动按频率更新。但是当数据库繁忙的负载很多时，我看到这些操作需要手动触发。我们遇到了类似的情况，因此我们习惯于在加载关键表后强制执行分析操作。您需要拥有用于加载表的 id 的权限。
ANALYZE TABLE table_name PARTITION (partition_name) COMPUTE STATISTICS;
EDIT: ANALYZEno longer gather CBOstats as mentioned here
编辑：ANALYZE不再收集这里CBO提到的统计数据
So, DBMS_STATSpackage has to be used.
因此，DBMS_STATS必须使用包。
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
And until the analyze is complete, the view tables below may not produce the accurate results (Especially the last_analyzed and num_rows columns)
并且在分析完成之前，下面的视图表可能不会产生准确的结果（特别是 last_analyzed 和 num_rows 列）
Note: Try replace all_as dba_in table names, if you have access to it, you can try them.
注意：尝试替换all_为dba_表名，如果您可以访问它，您可以尝试它们。
You can also try to get SELECT_CATALOG_ROLEfor your development id you use, so that you can SELECT the data dictionary views, and this reduces the dependency over DBAover such queries.(Still DBA are the right persons for few issues!!)
您还可以尝试获取SELECT_CATALOG_ROLE您使用的开发 ID，以便您可以选择data dictionary views，这减少了DBA对此类查询的依赖。（对于少数问题，DBA 仍然是合适的人选！！）
Query to identify the partition table, partition name, number of rows and last Analysed date!
查询以识别分区表、分区名称、行数和上次分析日期！
select
all_part.owner as schema_name,
all_part.table_name,
NVL(all_tab.partition_name,'N/A'),
all_tab.num_rows,
all_tab.last_analyzed
from
all_part_tables all_part,
all_tab_partitions all_tab
where all_part.table_name = all_tab.table_name and
all_tab.partition_name = all_tab.partition_name and
all_part.owner=all_tab.table_owner and
all_part.owner in ('SCHEMA1','SCHEMA2','SCHEMA3')
order by all_part.table_name,all_tab.partition_name;
The Below Query returns the index/table name that are UNUSABLE
下面的查询返回索引/表名称 UNUSABLE
SELECT INDEX_NAME,
TABLE_NAME,
STATUS
FROM ALL_INDEXES
WHERE status NOT IN ('VALID','N/A');
The Below Query returns the index/table (PARTITION) name that are UNUSABLE
下面的查询返回索引/表 ( PARTITION) 名称UNUSABLE
SELECT INDEX_NAME,
PARTITION_NAME,
STATUS ,
GLOBAL_STATS
FROM ALL_IND_PARTITIONS
WHERE status != 'USABLE';
回答by Jon Heller
Normally there is no need to identify objects that need statistics gathered. Oracle automatically gathers statistics for stale objects, unless the task has been manually disabled. This is usually good enough for OLTP systems. Use this query to find the status of the task:
通常不需要识别需要收集统计信息的对象。Oracle 会自动收集陈旧对象的统计信息，除非该任务已被手动禁用。这对于 OLTP 系统来说通常已经足够了。使用此查询来查找任务的状态：
select status
from dba_autotask_client
where client_name = 'auto optimizer stats collection';
STATUS
------
ENABLED
For data warehouse systems there is also not much need to query the data dictionary for stale stats. In a data warehouse statistics need to be considered after almost every operation. Developers need to get in the habit of always thinking about statistics after a truncate, insert, swap, etc. Eventually they will "just know" when to gather statistics.
对于数据仓库系统，也不需要在数据字典中查询陈旧的统计信息。在数据仓库中，几乎每次操作后都需要考虑统计信息。开发人员需要养成在截断、插入、交换等之后总是考虑统计数据的习惯。最终他们将“只知道”何时收集统计数据。
But if you still want to see how Oracle determines if statistics are stale, look at DBA_TAB_STATISTICSand DBA_TAB_MODIFICATIONS.
但是，如果您仍想了解 Oracle 如何确定统计数据是否过时，请查看DBA_TAB_STATISTICS和DBA_TAB_MODIFICATIONS。
Here is an example of an initial load with statistics gathering. The table and partitions are not stale.
下面是一个带有统计信息收集的初始加载示例。表和分区不是陈旧的。
create table test1(a number, b number) partition by list(a)
(
partition p1 values (1),
partition p2 values (2)
);
insert into test1 select 1, level from dual connect by level <= 50000;
begin
dbms_stats.gather_table_stats(user, 'test1');
dbms_stats.flush_database_monitoring_info;
end;
/
select table_name, partition_name, num_rows, last_analyzed, stale_stats
from user_tab_statistics
where table_name = 'TEST1'
order by 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED STALE_STATS
---------- -------------- -------- ------------- -----------
TEST1 P1 50000 2014-01-22 NO
TEST1 P2 0 2014-01-22 NO
TEST1 50000 2014-01-22 NO
Now add a large number of rows and the statistics are stale.
现在添加了大量的行并且统计信息已经过时。
begin
insert into test1 select 2, level from dual connect by level <= 25000;
commit;
dbms_stats.flush_database_monitoring_info;
end;
/
select table_name, partition_name, num_rows, last_analyzed, stale_stats
from user_tab_statistics
where table_name = 'TEST1'
order by 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED STALE_STATS
---------- -------------- -------- ------------- -----------
TEST1 P1 50000 2014-01-22 NO
TEST1 P2 0 2014-01-22 YES
TEST1 50000 2014-01-22 YES
USER_TAB_MODIFICATIONSgives more specific information on table staleness.
USER_TAB_MODIFICATIONS提供有关表陈旧的更具体信息。
--Stale statistics.
select user_tables.table_name, user_tab_modifications.partition_name
,inserts+updates+deletes modified_rows, num_rows, last_analyzed
,case when num_rows = 0 then null
else (inserts+updates+deletes) / num_rows * 100 end percent_modified
from user_tab_modifications
join user_tables
on user_tab_modifications.table_name = user_tables.table_name
where user_tables.table_name = 'TEST1';
TABLE_NAME PARTITION_NAME MODIFIED_ROWS NUM_ROWS LAST_ANALYZED PERCENT_MODIFIED
---------- -------------- ------------- -------- ------------- ----------------
TEST1 P2 25000 50000 2014-01-22 50
TEST1 25000 50000 2014-01-22 50
回答by haki
I had a some what similar problem and I solved it by gathering stats on stale partitions only using 11g new INCREMENTALoption.
我遇到了一些类似的问题，我通过仅使用 11g 新INCREMENTAL选项收集陈旧分区的统计信息来解决它。
It's the reverse approach to your problem but it might worth investigating (specifically - how oracle determines what's a "stale" partition is).
这是解决您的问题的相反方法，但可能值得研究（特别是 - oracle 如何确定什么是“陈旧”分区）。
dbms_stats.set_table_prefs('DWH','FACT_TABLE','INCREMENTAL','TRUE')
I always prefer the pro active approach - meaning, gather stats on stale partition at the last step of my etl, rather then giving the developer stronger privs.
我总是更喜欢主动方法 - 意思是，在我的 etl 的最后一步收集陈旧分区的统计信息，而不是给开发人员更强的特权。
回答by Incognito
- Yes, you can generate a list of partitioned tables, and a lot of related data which you would like to see, by using
ALL_PART_TABLESorUSER_PART_TABLES(provided you have access).ALL_TAB_PARTITIONScan be used to get number of rows per partition, alongwith other details.
- 是的，您可以使用
ALL_PART_TABLES或USER_PART_TABLES（前提是您有权访问）生成分区表列表以及您希望查看的许多相关数据。ALL_TAB_PARTITIONS可用于获取每个分区的行数以及其他详细信息。
Check other views Oracle has for gathering details about partitioned tables.
检查 Oracle 的其他视图以收集有关分区表的详细信息。
I would suggest that you should analyze the tables, and possibly rebuild the indexes, every day after your data load. If your data load is affecting a lot of records in the table, and is going to affect the existing indexes, it's a good idea to proactively update the statistics for the table and index.
You can use on the system views to get this information (Check http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin005.htm)
我建议您应该在数据加载后每天分析表，并可能重建索引。如果您的数据负载影响表中的大量记录，并且会影响现有索引，那么主动更新表和索引的统计信息是个好主意。
您可以使用系统视图来获取此信息（检查http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin005.htm）
