Oracle error explanation

Just by accident I found out that I have开发者_StackOverflow中文版 an performance monitoring tool for oracle-db so I tried to look out for some performance issues. Now the Software gives me the following alerts:

• SQL library cache miss rate (somewhere arround 80%)
• Latch waits are (somewhere between 4-5%) of non-idle wait time
• Datafile random read avg time is 200ms

Can someone explain me what that means to the database and me?

---

SQL Library Cache miss rate means that when you execute a query it is most of the time (80%) not already in the cache, i.e. has not been seen before recently. As a result, 80% of the queries need to be evaluated and compiled from scratch. This probably indicates that you are not using bind variables (so that every single SQL is a little different).

---

if your users are not complaining than you have no problem. If they are complaining, your first thing to check if the shared_pool sizing and settings like open_cursors, session_cached_cursors. How much parsing is going on? Parsing is a real scalability killer. Lot's of applications generate sql that is repeated very often, with literals in the query instead of bind variables. You need to know how your app works.

What version databases do you have?

Also, the average random access time of 200ms ..... normally that is not considered healthy. Anything above 10ms is high and not helping your happy user experience.

What is the app doing, large scans, lots of updates, many commits? Performance questions always seem to raise more questions ...

best regards, Ronald.