z***y 发帖数: 7151 | 1 面试位置是senior database admin for data warehouse
因为事前没有协商好,我晚些才打电话进来。前面有公司的人问数据导入后一般再做什
么。面试人说了一些,
其中说起要update statistics. 我跟着问了下原因,回答因为数据更新了。我随后举
了一个例子:what if the import
process only import into one table with 500 million records and now this
import import or changed 150 million records,
in that case do we need do update statistics? What if this import import
or changed 75 million records, do we need
do this update statistics.
He said yes for both. I asked again are you sure? What is the re |
w****b 发帖数: 118 | 2 I think the answer is okay ya.
When you import 150 million addtional , 20% factor kick in, sql server will
decide the statistics is outdated.
when you import 75 million, it is less than 20%, sql won't think it is
outdated.
but it is always safe to just update statistics. Am I missing anything here?
import
【在 z***y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 面试位置是senior database admin for data warehouse : 因为事前没有协商好,我晚些才打电话进来。前面有公司的人问数据导入后一般再做什 : 么。面试人说了一些, : 其中说起要update statistics. 我跟着问了下原因,回答因为数据更新了。我随后举 : 了一个例子:what if the import : process only import into one table with 500 million records and now this : import import or changed 150 million records, : in that case do we need do update statistics? What if this import import : or changed 75 million records, do we need : do this update statistics.
|
i****a 发帖数: 36252 | 3 your questions are tough....
is the following answer along the direction thta you are looking for?
if the server has auto update statistic option turned on, it'll be
performed automatically by the server.
If it's turned off, then update stats should be run if the table has
indexes (can't imagine a table like this without index) and millions of
new rows are imported.
if it's update, then only if the updated column is indexed.
this
import
【在 z***y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 面试位置是senior database admin for data warehouse : 因为事前没有协商好,我晚些才打电话进来。前面有公司的人问数据导入后一般再做什 : 么。面试人说了一些, : 其中说起要update statistics. 我跟着问了下原因,回答因为数据更新了。我随后举 : 了一个例子:what if the import : process only import into one table with 500 million records and now this : import import or changed 150 million records, : in that case do we need do update statistics? What if this import import : or changed 75 million records, do we need : do this update statistics.
|
p*********d 发帖数: 136 | 4 His answer is right. Even indexes have been updated, the statistics may not
be updated. Also as webweb mentioned, it is always safe to just update
statistics. |
j*****n 发帖数: 1781 | 5 well, i can never remember why and when... updating stats won't cost too
long, why bother just get it done anyway?
i guess Zenny is just teasing this guy and see the attitude... am i right? ;
-) |
w*******e 发帖数: 1622 | 6 偶支持你的第一个关于20%的说法。
至于第二个,因为是500M的table, 我倒是持保留意见。
will
here?
【在 w****b 的大作中提到】![](/moin_static193/solenoid/img/up.png) : I think the answer is okay ya. : When you import 150 million addtional , 20% factor kick in, sql server will : decide the statistics is outdated. : when you import 75 million, it is less than 20%, sql won't think it is : outdated. : but it is always safe to just update statistics. Am I missing anything here? : : import
|
w*******e 发帖数: 1622 | 7 这个。。。。。
1. 即使autostats ON, 如果rowmodctr没超过20%, Database Engine是不会自己update
stats的.
2. 因为本来有500M了,而加入75M, 只有15%的modification, Database Engine认为
延用之前的statistics是很safe的。
【在 i****a 的大作中提到】![](/moin_static193/solenoid/img/up.png) : your questions are tough.... : is the following answer along the direction thta you are looking for? : if the server has auto update statistic option turned on, it'll be : performed automatically by the server. : If it's turned off, then update stats should be run if the table has : indexes (can't imagine a table like this without index) and millions of : new rows are imported. : if it's update, then only if the updated column is indexed. : : this
|
w*******e 发帖数: 1622 | 8 说实在的, 偶还没机会update stats against 这么大的table,
需要多长时间呀? 谁给说说??
;
【在 j*****n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : well, i can never remember why and when... updating stats won't cost too : long, why bother just get it done anyway? : i guess Zenny is just teasing this guy and see the attitude... am i right? ; : -)
|
a9 发帖数: 21638 | 9 从来没更新过这个,也从来没整理过索引的飘过。。。。。。
right?
【在 w*******e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 说实在的, 偶还没机会update stats against 这么大的table, : 需要多长时间呀? 谁给说说?? : : ;
|
z***y 发帖数: 7151 | 10 An strongly disagree...我从来不teasing interviewee, 因为几乎每个公司事后都要
做survey,
其中要对interviewer 评价的。 我至今还是4+ , 呵呵
面试的一个很重要的方面是要言之有据, 思路有的时候比结论重要。 我就是想知道他
做与不做
的考量是什么。 这本来就是一个open question.
;
【在 j*****n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : well, i can never remember why and when... updating stats won't cost too : long, why bother just get it done anyway? : i guess Zenny is just teasing this guy and see the attitude... am i right? ; : -)
|
|
|
j*****n 发帖数: 1781 | 11 500MB, 撒撒水了,分吧钟就好了。
【在 w*******e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 说实在的, 偶还没机会update stats against 这么大的table, : 需要多长时间呀? 谁给说说?? : : ;
|
w*******e 发帖数: 1622 | 12 Oh, 要是这样的话, 那倒是可以run一下
【在 j*****n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 500MB, 撒撒水了,分吧钟就好了。
|
w****b 发帖数: 118 | |
a****b 发帖数: 489 | 14 I do not think updating statistics for the table with this kind of size will
consume too much time. 3 minutes could be enough. But that definitely
depends on some specific situation. |
z***y 发帖数: 7151 | 15 If I had to pickup one to giving out baozi, I will choose iMajia. However I
am looking for more detail. Again my answer is nothing but my opinion:
The 20% default threshold is correct. Most of time we should do update
statistics AND rebuild index both.
When we introduce changes that less than 20% of current data, we do want to
update statistics--resampling the whole data. Otherwise you might get index
scan or table scan.
You might ask when we need to update stats AND rebuild index? Is it true |
w****b 发帖数: 118 | 16 It is true rebuilding index will aso update statistics. Re-org will not
update statistics.
For huge table, when/how to update statistics is case by case. For example,
you might find out the default sample rate is not good enough and you may
want to do a fullscan during weekend (in case you only rebuild index which
is at least 20% fragmented)
I
to
index
【在 z***y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : If I had to pickup one to giving out baozi, I will choose iMajia. However I : am looking for more detail. Again my answer is nothing but my opinion: : The 20% default threshold is correct. Most of time we should do update : statistics AND rebuild index both. : When we introduce changes that less than 20% of current data, we do want to : update statistics--resampling the whole data. Otherwise you might get index : scan or table scan. : You might ask when we need to update stats AND rebuild index? Is it true
|
i****a 发帖数: 36252 | 17 when I said "only if the column is indexed", I mean "statistic'ed"
zenny, are you thinking about the actual distribution of the data? if that
hasn't been changed much then no updated needed? meaning if the new data has
the same data distribution as existing data, then no update needed?
and if the change is small, and if using a small sample rate when running
update stat, it wouldn't pick up the new difference? |
g***l 发帖数: 18555 | |
i****a 发帖数: 36252 | 19 zenny 真是好人 ah, 又教學又發 baozi |
z***y 发帖数: 7151 | 20 hehe, I do phone screening services for a while...Actually I've learned
a lot from interviewee.
【在 i****a 的大作中提到】![](/moin_static193/solenoid/img/up.png) : zenny 真是好人 ah, 又教學又發 baozi
|
|
|
y********o 发帖数: 2565 | 21 I learn a lot from interviewers.
【在 z***y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : hehe, I do phone screening services for a while...Actually I've learned : a lot from interviewee.
|
n*****y 发帖数: 36 | 22 我再添点砖:
如果import data之后又rebuild index,那么不用update stat相关index,
首先是重复劳动,如果不带fullscan的update stat得到的statistics
还不如rebuild index时得到的准确。
虽然SQL Server会根据import数据量(20%)自动update stat,
但因为data import的任务通常是在maintenance window进行,
完成importhou 我习惯用cursor statement来更新其他的statistics
下面是其中的部分语句(更新指定表12小时内未更新的statistics)
请zenny和各位指正
SELECT
'UPDATE STATISTICS [' + sc.name + '].[' + t.name +'] [' + st.name +']
WITH FULLSCAN'
FROM
sys.stats st
JOIN sys.tables t ON t.object_id = st.object_id
JOIN sys.schemas sc on t.schema_id = sc.schema_id
LEFT JOIN sys.indexes i on st.object_id = i.object_id and st.name = i.name
WHERE
isnull(STATS_DATE(st.object_id, st.stats_id), '1900-Jan-01') < DATEADD(
hour, -12, GETDATE()) and
((i.name is not null and i.is_disabled = 0) or (i.name is null)) and
t.type = 'U'
and t.object_id = object_id('dbo.mytablename')
ORDER BY
t.name ASC,st.name ASC
import
【在 z***y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 面试位置是senior database admin for data warehouse : 因为事前没有协商好,我晚些才打电话进来。前面有公司的人问数据导入后一般再做什 : 么。面试人说了一些, : 其中说起要update statistics. 我跟着问了下原因,回答因为数据更新了。我随后举 : 了一个例子:what if the import : process only import into one table with 500 million records and now this : import import or changed 150 million records, : in that case do we need do update statistics? What if this import import : or changed 75 million records, do we need : do this update statistics.
|
g*****y 发帖数: 36 | 23 两个问题,
1.上面大家的讨论都是基于SQL Server吗?
2.rebuild index为什么不需要update stats呢?我理解的rebuild index只是重建索引
结构,对stats啥影响。即使rebuild index有一个默认的update stats选项,我感觉应
该只是update index关联列的stats。其他非索引列的stats还需要update.
导入数据后是否update stats,我认为要case by case分析。一个要考虑有没有足够长
的维护窗口来做一次足够强度的采样,二是考虑业务需求。有些业务是锁定统计数据的
,比方说有的表尺寸变化比较剧烈,这样的表不适合定期更新统计数据,需要锁定统计
数据防止在不合适的时间点做统计收集。
下面说Oracle。
Oracle也有根据数据变化百分比的threshold来触发统计数据收集作业。但是仍然导致
查询计划不稳定的问题。特别是对于低基数的索引列,where条件中用到的话,会被统
计收集成frequency histogram,任何bind variable命中到统计数据的[low,high]范围
之外马上会导致一个极小的选择率=1/(2*num_rows),从而使用错误的索引。在导入数
据之后,但没有达到threshold的情况下,统计收集作业不会被触发,从而错误地执行
计划就会被一直使用下去。在Oracle 10g之前地解决办法是,在用dbms_stats包作收集
统计数据的时候,指定opt_method=>'for all columns size 1'不去收集直方图避免极
小的选择率。在Oracle 11g,据我测试,我们在收集统计数据的时候就用默认选项,
不必刻意去避免收集直方图,在这个情况下[low,high] range已经被放宽到[low-(high
-low)/2,high+(high-low)/2],所以新导入的数据不会马上导致一个错误地执行计划,
所以给的容错时间比较多,足够默认的stats collection job启动更新统计数据。
Oracle 11g还有一个新功能可以根据bind variable的具体值来计算选择率来决定要不
要重新创建一个新的执行计划,来避免10g的bind variable peeking忽略具体bind
value的副作用。
总的说来Oracle 11g可以让执行计划变得更加稳定,比10g好很多了。但是我没在产品
环境中开始实际部署,得过段时间才能检验运营效果如何。
【在 n*****y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 我再添点砖: : 如果import data之后又rebuild index,那么不用update stat相关index, : 首先是重复劳动,如果不带fullscan的update stat得到的statistics : 还不如rebuild index时得到的准确。 : 虽然SQL Server会根据import数据量(20%)自动update stat, : 但因为data import的任务通常是在maintenance window进行, : 完成importhou 我习惯用cursor statement来更新其他的statistics : 下面是其中的部分语句(更新指定表12小时内未更新的statistics) : 请zenny和各位指正 : SELECT
|
n*****y 发帖数: 36 | 24 感觉大家是在讨论SQL server,另外俺的query也是在SQL Server
下写的,针对的就是那些user defined statistics 和auto created
statistics(没有对应index)因而会在index rebuild中遗漏。
我把该query包在一个cursor运行来保证所有的stat
都会在import data和index rebuild后updated,不用管是否已触发
auto stat update, 也不用担心做无用功。
谢谢对Oracle的补充,很久没有用到Oracle了,都快忘光了。
【在 g*****y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 两个问题, : 1.上面大家的讨论都是基于SQL Server吗? : 2.rebuild index为什么不需要update stats呢?我理解的rebuild index只是重建索引 : 结构,对stats啥影响。即使rebuild index有一个默认的update stats选项,我感觉应 : 该只是update index关联列的stats。其他非索引列的stats还需要update. : 导入数据后是否update stats,我认为要case by case分析。一个要考虑有没有足够长 : 的维护窗口来做一次足够强度的采样,二是考虑业务需求。有些业务是锁定统计数据的 : ,比方说有的表尺寸变化比较剧烈,这样的表不适合定期更新统计数据,需要锁定统计 : 数据防止在不合适的时间点做统计收集。 : 下面说Oracle。
|
c*****d 发帖数: 6045 | 25 肯定是sql server
oracle以前stats是不会自动update的,都要用dbms_stats or analyze table
【在 g*****y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 两个问题, : 1.上面大家的讨论都是基于SQL Server吗? : 2.rebuild index为什么不需要update stats呢?我理解的rebuild index只是重建索引 : 结构,对stats啥影响。即使rebuild index有一个默认的update stats选项,我感觉应 : 该只是update index关联列的stats。其他非索引列的stats还需要update. : 导入数据后是否update stats,我认为要case by case分析。一个要考虑有没有足够长 : 的维护窗口来做一次足够强度的采样,二是考虑业务需求。有些业务是锁定统计数据的 : ,比方说有的表尺寸变化比较剧烈,这样的表不适合定期更新统计数据,需要锁定统计 : 数据防止在不合适的时间点做统计收集。 : 下面说Oracle。
|