How to tune performance when you have heavy concurrency on an Oracle table
On an Oracle 10g table which is read / write intensive, we sometimes get a huge spike in concurrency by quoting "latch library cache" using the entire cpu.
What approaches can we take to reduce the load on the database. We've seen stuff about free listings and are increasing them. Any other opinions.
a source to share
The "latch library cache" is unrelated to whether or not there is high I / O in a given table. This means that you are waiting for a cache latch for the SQL statements being parsed; so chances are when you see these bursts there is an unusual amount of hard parsing.
The most common reason for this is that you are not using bind variables, so you are parsing a lot of similar operators with different values. EG. you follow instructions like:
SELECT name FROM emp WHERE empid = 3;
and changing the value of the literal identifier each time the request is made, forcing new parsing. Better to replace the binding variable literal and bind a new value for each execution. Exactly how to do this depends on the language / library / environment you are using to execute statements on the database.
a source to share