On an Oracle 10g table which has heavy read/write we occassionally get a huge spike in concurrency quoting "latch library cache" using all the CPU.
What approaches can we take to lessen the load within the database. We've seen stuff about free lists and increasing those. Any other opinions.
"latch library cache" isn't related to whether I/O is high on a given table. It means you are waiting on a latch for the cache of parsed SQL statements; so most likely when you see these spikes there are an unusual number of hard parses occurring.
The most common reason for this is that you are not using bind variables so you are hard-parsing many similar statements with different values. E.G. you are executing statements like:
SELECT name FROM emp WHERE empid = 3;
and changing the literal ID value each time the query is executed, causing a new statement to be parsed. It is better to replace the literal with a bind variable and bind a new value for each execution. Exactly how to accomplish this depends on the language/library/environment you are using to execute statements against the database.