Our reader Ruth Ratcliffe writes:

I was particularly interested in the "ORACLE and BUFFER HIT RATE PREDICTION" article (Issue 5 - ed). When I started as an Oracle DBA on release 7.0.12 I found the DB_BLOCK_LRU_EXTENTDED_STATISTICS very useful and, as it did have a noticeable effect on performance, I left it in the parameter file. After several upgrades (7.1.3, 7.1.6, 7.2.2 and 7.2.3) it continued to cause no problems so I left it in place on our various databases. When we upgraded to 7.3 it had a significant impact - the database hung. As soon as anyone did anything more than a very simple query the database "stalled" (only curable by a shutdown abort) - no one could logon only connect internal and then only to view the v$ stuff. This was a problem even when there was only one user! When I spoke to Oracle they said it was a known bug, with no patches for 7.3 or 8.0 and they only recommended using DB_BLOCK_LRU_EXTENTDED_STATISTICS when the database was under (very) light load. I think this bug first appears in 7.3.2. My databases were on Unix Solaris. Do you know of this problem?

Gary at Metron says: I have to say I've not heard of a reaction so drastic from a database, I think you've been very unfortunate. Tim, the author of the tip here, does add that the parameter should be removed once used to avoid the overhead. Looking at various authors, I notice that they either say for example "Don't use this during any normal operations because of the negative impact on performance" Tuning Oracle - Corey/Abbey/DeChichio or they just assume it's going to run fine. Even the excellent Ahmed Alomari in "Oracle8 & Performance Tuning" seems to indicate it can just be used with no special consideration.

During my DBA days I used this quite often, and as you point out is was an absolute boon. However, I would only use it on "test" days when no other work was being done on the database, and the parameter would be removed immediately after. This was as a result of noticing reduced performance during on-line periods, although fortunately we had no crashes.

I think what you've done is to point out an important omission in a lot of Oracle documentation. Although obvious once in use, this would perhaps be a helpful addition whenever this technique is mentioned. The use of this should be restricted to individual exercises performed during off-line periods, if you're lucky enough to have any.

Ben Pyett has pointed out that "You fail to stress the loss in performance that the additional init.ora parameter causes for its lifetime! Also the fact the adding this extra parameter can sometimes lead to severe Latch contention during heavy periods of database activity has been known to hang databases!" Thanks for that Ben!

Hopefully, a positively last comment on the same subject comes from Nigel Bayliss, who says "For what it's worth - the 804 docs do say "Setting this parameter can cause a large performance loss, so it should only be set when the system is lightly loaded." The DBA courses definitely tell you all about this too. The whole concept of tuning using this kind of procedure is long outmoded anyway - a point that most of the "money for old rope" performance tuning books completely miss. I think that it is a hang over from ye olden times when the days were long and we used to carefully twiddle memory usage. In support of this point of view is the fact that it's dead in 8.1.3 anyway."

Thanks again for this information - yet another Oracle potential pitfall to be mindful of.