I am using Oracle 11g R2, I am trying to simulate load testing using Jmeter (Apache load testing tool) . I tried concurrent 200 users to execute the same query (report) but i observed a "latch: cache buffers chains" (Timed Foreground Events) with below details :
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
latch: cache buffers chains 85,303 5,561 65 43.56 Concurrency.
I understand that this is due same data block concurrent read from buffer, but i want to know how can i minimize this concurrency effect by tuning any parameter / pool ? i want to tune my DB for 200 concurrent sessions.
1) What does your query look like and what does the execution plan look like. Even though it is a concurrency issue you still need to make sure the execution plan is optimal as the longer it takes to run the more likely you will have concurrency issues.
2) Do all users run it with the same parameters?
In the past I managed to reduce these sorts of waits by spreading the data across more blocks by increasing pct free. Normally you have to optimise concurrency for a large number of different queries rather than the same query which you can optimise using a number of different ways such as partitioning, using multiple buffer pools etc.