|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/10/2007 9:39:00 AM
Posts: 5,
Visits: 1
|
|
What are factors to decide how much time it should take for "collect statistic" and "refresh statistic"?
My question is related to time it takes for "collect statistic" and "refresh statistic"?
Thanks n Regards, Yuvaraj B Birari
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 5:54:22 PM
Posts: 173,
Visits: 50
|
|
It is how frequently your data in the tables is added/modified/deleted. If the data changes very frequently, you might need to collect /refresh statistics often to get best performance for your queries.Ofcourse,remember that collect stats is resource intensive !
Leo Issac "Wants to Learn More!"
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 4/30/2008 4:36:46 AM
Posts: 32,
Visits: 14
|
|
WHEN EVER THERE IS EXTENSIVE INSERTs, DELETEs, UPDATEs, OR ANY OTHER MAINTANENCE TO ANY COLUMNS WHICH IS CONTAINING STATISTICS.WE SHOULD REFRESH THE STATS.
THE CHANGE OF DATA SHOULD OF 5 TO 10 %
Regards, GogulM
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
Another parameter to check is the confidence level in the explain plan.
Vinay
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 5:54:22 PM
Posts: 173,
Visits: 50
|
|
one more hint. use "diagnostic helpstats" to get recommendations on collect stats on particular columns. This will be displayed at the end of explain text
Leo Issac "Wants to Learn More!"
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 2/26/2008 11:42:13 AM
Posts: 16,
Visits: 32
|
|
Hi all,
Could u pls tell me something more about "diagnostic helpstats" .....
Thanks,
abc
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 2/26/2008 11:42:13 AM
Posts: 16,
Visits: 32
|
|
Hi all,
Is the same procedure followed to do the collect stats when it is done for the first time and when done for any successive times on the same table (i,e when 5% - 10% of data is being altered)
Thanks,
abc
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 9:19:53 PM
Posts: 469,
Visits: 463
|
|
diagnostic helpstats is used to understand the optimizer recommendations of what stats it thinks might be useful in making a (probably) better query execution plan.
you can do it by typing the following command in sql assistant and doing the explain on your query after wards.
diagnostic helpstats on for session;
explain
select a, b, c
from t1, t2
where t1.a = t2.b
....
When you recollect stats on a table, teradata internally does all the same operations it did the first time you collected stats on it. so depending on whether it was a full stats or a sampled stats it would either scan the full table (exceptions are if there are index subtables which would give a faster results) or scan a percentage of the table to collect stats information (this is across the whole table and not just the additional 5% or 10% of the records that changed)
|
|
|
| | |