Home » RDBMS Server » Performance Tuning » General question on dbms_stats (11g)
General question on dbms_stats [message #616274] Sat, 14 June 2014 08:00 Go to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
General question on dbms_stats and auto sample size?

what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV .

How does it impact?
Re: General question on dbms_stats [message #616276 is a reply to message #616274] Sat, 14 June 2014 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

if something changes in the statistics, as the optimizer uses the statistics, it is obvious there will be some changes in the execution plans.
How? It depends on your objects and data and how you query/use them.

[Updated on: Sat, 14 June 2014 09:45]

Report message to a moderator

Re: General question on dbms_stats [message #616277 is a reply to message #616274] Sat, 14 June 2014 10:43 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
kiranrathodkr916 wrote on Sat, 14 June 2014 18:30

what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV .


Yes, there have had been several discussions on this and conclusion was :

A quote from J.Lewis Quote:

if you've got IOTs with overflows and you switch to auto_sample_size and enable approximate NDV then the time taken to gather stats on those IOTs may (depending to a large extent on the data clustering) take much longer than it used to.


For more information look at this link http://jonathanlewis.wordpress.com/2014/03/02/auto-sample-size/
Previous Topic: how to troubleshoot slow running queries?
Next Topic: SQL performance tunning :- DBMS_STATS procedure errored out
Goto Forum:
  


Current Time: Thu Mar 28 06:32:29 CDT 2024