I've never seen contention for the longop free list latch before. Are you using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS directly? If not, this is almost certainly a bug. Both your report.txt files cover about 15 minutes. In the bad case latch free waits account for about 17% of response time; in the normal case about 0%. However, the real difference in performance is actually due to the CPU usage, rather than the latch contention. In the bad case you have 100% CPU usage; in the normal case about 6%. In the bad case you have 127,698 gets on the longop free list latch per second; whereas in the normal case you only have 1 get per second on that latch. Or to put it another way, the number of gets on this latch is two orders of magnitude greater than the number of buffer gets. That definitely sounds like a bug, unless you've got some rogue code calling SET_SESSION_LONGOPS directly.

We have lots of literal SQL being fired at the database, mainly to perform seraches against a few Gig of data. I've realised that latch contention and fragmentation was occurring from monitoring v$system_event so in order to reduce this, I reduced the shared_pool from 1GB to 150M and created a large_pool of 100M to handle the MTS UGAs.

Before this change, I would see latch contention on the shared_pool latch and the load on the shared_servers would increase significantly causing performance problems.

The changes I made seemed to work well and I believe the performance has been increased slightly.

However, on Friday, the load of the db server hit a record...over 14 looking at the top utility. I bounced the database to bring the load back down and it was fine after that...and has continued all weekend hovering around 0.8 - 1.5.

Another spike occurred today, hitting 12-13 for about 40 minutes. I flushed the shared_pool and increased the number of shared_servers to 20. After about 5 minutes of doing this, the load returned to normal, about 0.8.

There was no increase in db activity, and in fact the website was fairly quiet.

I performed utle/bstat for the spiked period, and it clearly shows latch contention on the longop latch. But I do not know what this means and a search on metalink has not returned anything significant. There seems to be no problem with the shared_pool latch this time.

From the stats I collect every 10 minutes, we can see from v$system_event that there is a definite dramatic increase in latch_wait times for the period of heavy load. As a result, the shared_servers are put under a lot of load.

But I don't understand why this occurs or what I can do to stop it happening again. I should really upgrade to 8.1.6 which I believe has better algorithms for dealing with literal SQL, but I do have the resources at the moment to do this soon.

I've attached some report.txt files and some other stats I gathered at the time.