|
|
|
|
|
|
| Re: what are the disadvan of using incremental stats in oracle 11g? [message #613726 is a reply to message #613712] |
Fri, 09 May 2014 13:10   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
That's why I asked you to post your specific concern. I have had done enough experiments for the same.
Frankly speaking, for such generic questions, the best place to find answer is Oracle documentation. If you still don't find your answer, then please come up with your question with a test case. I understand sometimes it's not possible to explain the requirement through a test case, but at least an elaboration is much appreciated.
So, if you come across any issue then post it here and any member in this forum would definitely help you.
|
|
|
|
| Re: what are the disadvan of using incremental stats in oracle 11g? [message #614366 is a reply to message #613726] |
Wed, 21 May 2014 03:40 |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Quote:There are some limitations, as far as I remember, it didn't work at partition level.
Gathering statistics on partitioned tables consists of gathering statistics at both the table level and partition level. Prior to Oracle Database 11g, adding a new partition or modifying data in a few partitions required scanning the entire table to refresh table-level statistics. If you skipped gathering the global level statistics, the Optimizer would extrapolate the global level statistics based on the existing partition level statistics.
This approach is accurate for simple table statistics such as number of rows by aggregating the individual rowcount of all partitions - but other statistics cannot be determined accurately: for example, it is not possible to accurately determine the number of distinct values for a column (one of the most critical statistics used by the Optimizer) based on the individual statistics of all partitions.
Oracle Database 11g enhances the statistics collection for partitioned tables with the introduction of incremental global statistics. If the INCREMENTAL preference for a partitioned table is set to TRUE, the DBMS_STATS.GATHER_*_STATS parameter GRANULARITY includes GLOBAL, and ESTIMATE_PERCENT is set to AUTO_SAMPLE_SIZE, Oracle will gather statistics on the new partition, and accurately update all global level statistics by scanning only those partitions that have been added or modified, and not the entire table.
Below are the steps necessary to use incremental global statistics.
Begin by switching on incremental statistics at either the table or the global level.
BEGIN
DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');
END;
/
Gather statistics on the object(s) as normal, letting the ESTIMATE_PERCENT and GRANULARITY parameters default.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');
END;
/
source: blogs.oracle.com/optimizer search for statistics document
Regards,
Pointers
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
