Re: Another Oracle "Myth"?

most of the nasty performance problems i've seen have to do with not how efficiently the work is being done (i.e., good hit-ratio) but with how much unnecessary work is being done

one of the examples in the paper indicates the true issue -- i think it was something like ~90% efficiency getting 10 blocks vs ~99% efficiency getting 10,000 blocks for the same resultset

i still find BUFFER_GETS in V$SQLAREA to be a real good indicator of where problems are hiding -- the higher the value, the more suspicious i am of the SQL

• mcs

"Geomancer" <pharfromhome_at_hotmail.com> wrote in message news:cf90fb89.0311201853.126b1516_at_posting.google.com...
| Cary Millsap makes the assertion that a buffer hit ratio of > 99%
| OFTEN indicates inefficient SQL:
|
| http://www.hotsos.com/dnloads/1.Millsap2001.02.26-CacheRatio.pdf
|
| According to Mr. Millsap:
|
| "A hit ratio in excess of 99% often indicates the existence of
| extremely inefficient SQL that robs your system's LIO capacity."
|
| With 30 gigabyte data buffer becoming more common and RAM caches
| approaching 100% for small systems, I wonder if it is true that a
| 99.9% data buffer hit ratio is due to high caching of frequently
| referenced objects than some mysterous un-tuned SQL.
|
| To me, this does not make any sense, because many well-tuned systems
| benefit from additional RAM. The v$db_cache_advice view was
| introduced in 9i for this very reason.
|
| Is this another Myth, or am I missing something?
Received on Fri Nov 21 2003 - 12:22:09 CST