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".
转自:
阅读(1981) | 评论(0) | 转发(0) |
