We thought this optimizer setting was the magic bullet, but it turns out that it wasn't. We were desperate to try anything. Since then we have stepped back and looked at the whole picture. We have noticed that our database server's paging has gone way up which indicates not enough memory. Past experiences related by other Baan/Oracle upgrades indicated that Oracle 8i consumes more memory. So, we are going to add more memory and see what happens. Lately, I have personally spent a great deal of time reseaching Oracle memory tuning. Most of you know that there are three areas that bear looking at, and they are the Library cache, Dictionary cache, Block Buffer cache hit ratios when dealing with efficient Oracle memory usage. There are several queries based on V$ views that can be used to evaluate these that Oracle and several others provide for this purpose. Another tool that is new to Oracle 8i is STATSPACK and if you haven't installed it, do so. There is a web page that Oracle pointed me to that takes the output from the STATPACK report script and creates another report that provides a higher level of interpretation and advice for you to look at. The nice thing about it is that it is free. The information is only as useful as your understanding of Oracle internal workings. It is located at: http://www.oraperf.com,
and it works for either STATSPACK or UTLBSTAT/UTLESTAT outputs. One thing that Baan Tools support has recommended that we haven't tried yet is to revert back to the Baan/Oracle 7 driver. They have indicated that it has reduced memory usage and provided better performance for some customers. One question they asked us before they suggested it was, "Is your database using the US7ASCII characterset?" If yes, then try it, otherwise its not recommended. We are not sure if we want to try this just yet. I'll keep you posted on our progress.