Re: Another Oracle "Myth"?

On Thu, 20 Nov 2003 18:53:02 -0800, Geomancer wrote:

> 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?

Yes, you are missing something. Basically, if you cache your whole database, your performance gain will not be as large as you may expect, because, with all the overhead of oracle processing (conistent image, various locks and latches required to maintain internal structures), LIO ("logical I/O") is a fairly expensive beast, with its duration shooting close to the millisecond range. Performing several millions LIO calls from memory instead of performing them from disk will be several orders of magnitude slower then performing those same several millions of calls from memory, but the latter will be an order of magnitude slower then performing a few hundreds of LIO calls from either memory or disk. Cary's approach looks toward reducing the number of IO calls in the first place. Second, there is a question of logic. Before I proceed, let me explain my background. I'm a senior Oracle DBA from Croatia (ex-YU) and I am a US resident since March 1997. In the US, I've been working for a software development company (NYC, 1997-1999), a large HMO in CT (1999-2003) and now I'm working for a hedge fund in CT. Before coming to US, I've been working for a large international telecom. Hopefully, that establishes my credentials as a senior oracle DBA. All calls from users coming to me have always started with "why is my application slow today". Answering that question with "the cache hit ratio on the database is bad today" is, essentially, the same thing as saying "there is something strange with the Force today" or pulling out the "solar flares excuse", for those of us who appreciate BOFH. Traditionally, BCHR has not even been a good indicator of problems, especially not so when you encounter things like "global cache locks". There is only one logical way to continue conversation with the complaining user: ask him what exactly is slow and what exactly he or she is doing. The goal is to uniquely identify the user session and that is why one should always know the application system thast is being used to access the database. When and if you identify session (it is sometimes impossible to do so, especially with JDBC drivers taking advantage of connection pooling), you, generally speaking, first proceed by querying V$SESSION_WAIT, which is probably the single most important V$ table in the database. That table will give you the event(s) that the database session is waiting for, and those are the ones that have to be addressed. From here, it is the question of the DBA's knowledge of both programming tools and practices, database internals and political skills (needed in order to get the vile creatures called "developers" to fix their stuff as well as for making users to lay the blame on them and not on the DBA. That is so called CYA methodology which is so frequently needed in the modern companies.). That is the essence of Cary Millsap's method (called "Cary's method" because it was first documented by Anjo Kolk in his "wait" paper for Oracle7 and later in the YAPP paper). BCHR doesn't enter the picture at any stage and is usually used in the same way as a hare's paw, four leaf clover, horseshoe and other good luck charms. As I don't believe in magic, I don't use it. Cutting down the number of IOs is the best and also the hardest strategy one could attempt.

-- 
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)