10g SQL AREA Get Hitrate

Palinski

My company has given me the Toad DBA tool. One of the measures is the SQL Area Get Hitrate. I assume this is the SQL Area Hit rate. This ratio is basically 0. I've noticed that some other databases have higher hit rates.

I was wondering if anyone had any experience using this Toad measure and any thought as to affecting it. I'm not sure that the database is experiencing any problems, just curious of the measure.

Thanks,

John

--------------------------------------

 

mbobak

 

Hi John,

Some thoughts:

First off, looking at ratios is generally not particularly useful. The first question you should ask is if the database has any performance problems. Ratios are not generally a good indicator of performance, cause they mask information. They average out relevant information. As Jonathan likes to say, "if you put your feet over a fire and your head in a bucket of ice, on average, you're quite comfortable." ;-)

However, in this case, I think you may be on to something. I did some poking around, and my 10g database (which is not using automagic SGA tuning) also exhibits similar behavior. I see lots of gets, but essentially no gethits. (My ratio is around .006!)

So, first let's talk about what gets, gethits, pins, and pinhits are. A "GET" is a request for a handle to a library cache object. (This handle is also known as a library cache lock structure.) By extension, a GETHIT is Oracle saying, "Oh, here's the address of the handle, I've already got it, no need to allocate and create a new lock structure". A "PIN" is a request to ensure that the lock structure is pinned into the SGA, to guarantee that it won't get aged out. (This is required if you're going to execute that chunk of SQL.) A "GETHIT" is "Oh, look, the pin's already there." So, that's what GETS, GETHITS, PINS, and PINHITS are.

At this point it's interesting to note, that if you set session_cached_cursors to a non-zero positive integer, what you're really caching is library lock structures. If you further set cursor_space_for_time=TRUE, then you're also caching the library cache pins. This is why it's said that unless your application is very well behaved, setting cursor_space_for_time=true will likely cause shared pool memory problems. You're effectively pinning all SQL (thereby preventing it aging out of the shared pool). For well-behaved apps with a well-defined and sharable set of SQL, this is a good thing, and can improve performance, but for apps that don't share SQL well, it can bring your system down with ORA-4031 errors.

So, up to this point, I'm fairly certain that what I said is correct, meaning, there's still a good chance I got something wrong, but that's what the "model" in my head looks like right now.

The following is new (to me) info, but I think I've probably got it (mostly) right:

Starting in 10g, Oracle introduced the concept of a mutex to protect SQL, instead of a library cache structure. This is a lightweight mechanism that is intended to further improve scalability of parsing and execution.

This is where the wild speculation begins:

With the change to mutexes, from library cache lock structures, I wonder if, for most (but not all, since gethits isn't zero) code paths, when utilizing mutexes, Oracle no longer records gethits, but for some reason is still recording gets, causing this statistical discrepancy/anomaly?


-Mark

PS For more info in V$LIBRARYCACHE statistics, do a seach on "V$LIBRARYCACHE" on AskTom, and also see the most excellent Oracle whitepaper by Connie Green and John Beresniewicz, available here:
http://download-east.oracle.com/oowsf2005/003wp.pdf

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/310745/viewspace-6257/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/310745/viewspace-6257/