Home » RDBMS Server » Performance Tuning » what are the disadvan of using incremental stats in oracle 11g?
what are the disadvan of using incremental stats in oracle 11g? [message #613706] Fri, 09 May 2014 07:02 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
what are the disadvantages of using incremental stats in oracle 11g?
Re: what are the disadvan of using incremental stats in oracle 11g? [message #613710 is a reply to message #613706] Fri, 09 May 2014 07:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
There are some limitations, as far as I remember, it didn't work at partition level. Even it did't work for partitioned indexes. I checked 11gR2 documentation for incremental statistics for partitioned indexes, didn't get any information.

If you ask such a generic question, then it is better that you search by yourself. Is there anything specific that you are concerned about?

Found this in google search, see if it helps http://www.slideshare.net/ylouis83/incremental-statistics-for-partitioned-tables-in-11-g]

[Updated on: Fri, 09 May 2014 07:36]

Report message to a moderator

Re: what are the disadvan of using incremental stats in oracle 11g? [message #613712 is a reply to message #613710] Fri, 09 May 2014 08:04 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
thanks for the share.didnt find much info on limitations other than it wont work at partitioned indexes

[Updated on: Fri, 09 May 2014 08:55]

Report message to a moderator

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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
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 Go to previous messageGo to next message
pointers
Messages: 350
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
Re: what are the disadvan of using incremental stats in oracle 11g? [message #614480 is a reply to message #613706] Thu, 22 May 2014 05:26 Go to previous message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
thanks all for sharing your knowledge and experience
Previous Topic: dba_hist_snapshot vs dba_hist_active_sess_history
Next Topic: Direct Path Wait event
Goto Forum:
  


Current Time: Wed Oct 22 23:58:04 CDT 2014

Total time taken to generate the page: 0.15585 seconds