Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1354176
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19









分类: Oracle

2012-10-24 16:49:37

tune database的一个主要工作是降低LIO, 而不是PIO. LIO有可能会导致PIO. Index也不是万能的, 它可能导致LIO增加, 原因是LIO会保证读写的一致性, 从而产生latch,产生lock, 产生竞争, 影响性能. 所以要降低LIO. 另外cache hit ratio means nothing.

LIOs = consistent gets = latches on buffer cache = locks, serialization devices.
locks = serialization.
latching = cpu consumption (the more people attempting to get the latch at the same time, the higher the cpu)

1) by rewriting your SQL, tuning your SQL, setting environmental things like sort_area_size or db_file_multi_block_read_count or the optimize_index_* parameters.

2) the piece of advice I have for everyone: totally and forever forget the concept that "if my query ain't using an index, it must be broken". Massive nested loop joins are the main culprit of this. If you are joining a 1,000,000 row table to a 2,000,000 row table and trying to get 2,000,000 rows back -- it is very doubtful that an index should be used.

3) ratios STINK ( i have stronger words but this is a public forum after all ). There is one ratio I use -- soft parse ratio (the ratio of soft to hard parses). It should be near 100 for most systems. All other ratios -- forget about them. There is no magic "good number". It is like the stupidest ratio of them all - cache hit. I have a theory that systems with high cache hit ratios, over 95, 96% -- are among the most poorly tuned systems. They are experiencing excessive LIO's due to massive nested loop joins. Yet, their DBA's sit there and say "well, my cache hit is 99% so all is well in the world".

阅读(1596) | 评论(0) | 转发(0) |