一条sql导致数据库整体性能下降的诊断和解决的全过程

玉面飞龙 · 发表于 2004-9-21 13:06

最初由 Fenng 发布
[B]analyze table im_blacklist compute statistics; 类似这样？

analyze 分析得到的柱状图有的时候不是很准确 [/B]

同感如下方法更好一点
EXECUTE DBMS_STATS.GATHER_TABLE_STATS
(‘fdamuser’,'App_AssetInFolder, method_opt=>'for columns FolderID，Display size skewonly');

在处理柱状图和BIND VAR之间始终很麻烦

xzh2000 · 发表于 2004-9-21 13:09

最初由 fly_bug 发布
[B]今天早上一来，数据库load就比往常高了许多。想想数据库唯一的变化是昨天早上我曾经重新分析过数据库对象。

请教一个问题，如何监控数据库load是不是高了？ [/B]

linux下可以用top

Fenng · 发表于 2004-9-21 13:25

最初由 grassbell 发布
[B]

这样怎么能得到住状图？

应该是类似于这样的吧。。。

analyze table im_blacklist compute statistics
for table
for all indexes
for all indexed columns

而且使用了默认的75个buckets [/B]

我那句话要说的意思是analyze 不如dbms包

biti_rainy · 发表于 2004-9-21 14:05

在 analyze  and  dbms_stats 包的问题上，包括 histograms ，大家放心，这个东西我已经考究过2年了。不会犯一般性的错误。

我现在陷入的困境是研究 cost 的计算方法上

目前通过测试我大抵知道对于字符串计算histograms中
end point  value 是最长取 14 个字符，根据 ascii * power(256,x) + ascii * power(256,x-1) + ...+ ascii * power(256,0)  来计算
x最大为14，若字符串定义长度超过14则取14，小于14则是按照实际定义长度计算。

但是如何计算  DENS: 上我遭遇到了麻烦，并不是 1/NDV,在字符串有 histograms (example  size=75 )的时候，计算方法我目前并没有掌握。  即使知道了 DENS ，计算cost的时候如果是单列索引，那就是 clus * dens + x (几乎可以忽略的值，这里不详细说明了)  ，但是如果是复合索引，这里又出现很大的未知的东西，恐怕是难以通过实验的方法来很好地掌握计算方法的。很多时候靠的就是感觉了

于是，最后我决定：

使用 dbms_stats包创建statistics 表，每次分析对象前备份statistics，不周期性地分析对象，潜在的风险太大。发现不合适的对象，部分地分析。结合周期性的statspack数据观察本次分析带来的影响，如果对整体性能有明显影响及时调整（包括根据以前statistics恢复或者备份新的良好的statistics）。
大家千万要注意，尤其分析的时候带着 for  all  indexed  columns 这样的方法，一定要小心观察。如果不做好statistics备份出了问题很麻烦。在数据库运行比较稳定的状况下不要轻易分析。

biti_rainy · 发表于 2004-9-21 14:08

至于 dbms_stats包，前天早上我还遇到一个bug，已经mail通知alibaba所有dba了：

hi,all！

今天早上对数据库对象进行分析的时候遭遇一个bug ，当时弄的我莫名其妙，某些表和索引都存在这个问题，有时表重新单个分析就好了，而索引在rebuild之后才好，查了下metalink 发现是bug ，解决办法就是设置百分比为 99.999

The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4
This problem can occur on any platform.

Errors
ORA-6512 "at %sline %s"
ORA-904 %s: invalid identifier / invalid column name

Symptoms
Analyze with DBMS_STATS with 100 percent gives the following error:

ORA-904: : invalid identifier
ORA-6512: at "SYS.DBMS_STATS", line 9375
ORA-6512: at "SYS.DBMS_STATS", line 9389
ORA-6512: at line 1
Cause
While using dbms_stats, if we use 100 percent the statistics are gathered in "compute statistics" mode. The compute statistics mode of gathering statistics causes the error ORA-904. There is an internal Bug logged for this.
Fix
Use percentage to be less than 100.
Even 99.999 works fine.

This issue has been resolved in 9.2.0.5

This is not recommended but, ANALYZE command can be used too.

Fenng · 发表于 2004-9-21 14:22

哈看来你们的库还是9204

9205 也有很多新的问题

biti_rainy · 发表于 2004-9-21 14:34

最初由玉面飞龙发布
[B]

同感如下方法更好一点
EXECUTE DBMS_STATS.GATHER_TABLE_STATS
(‘fdamuser’,'App_AssetInFolder, method_opt=>'for columns FolderID，Display size skewonly');

在处理柱状图和BIND VAR之间始终很麻烦 [/B]

9204 中存在histograms，bind var 在cbo 中 hard parse的时候会窥视 value 的，这个才造成了我所遇到的问题。现在对于要分析 columns 的东西要保持警惕