I will explain how to solve Concurrency Wait Event ( library cache lock and Cursor: Pin S Wait On X ) in Oracle in this article.
Cursor: Pin S Wait On X and library cache lock
Sometimes your Oracle database may hang, or Oracle Database hangs periodically with lots of sessions being blocked by each other, deadlocks can be seen as well, with different waits such as library cache lock cursor and ‘cursor: pin S wait on X’, ‘row cache lock’ and ‘library cache load lock’ and etc.
When Oracle database hangs, I have checked Enterprise Manager Cloud control Performance Page and seen following Concurrency wait event between 5 and 7 o’clock.
Cursor: pin S wait on X
I have also generated Oracle AWR ( Automatic Workload Report ) report between 5 and 7 o’clock, Top 10 Foreground Events by Total Wait Time sections show me too much library cache lock cursor and pin S wait on X wait event.
Now I have make sure that Database hangs because of Concurrency ( library cache lock cursor and pin S wait on X )
So How to solve Concurrency ( library cache lock cursor and pin S wait on X ) ?
Oracle Concurrency
Oracle Concurrency occurs because of following causes
- Automatic Shared Memory Management
- High hard parses
- High number of versions of the SQL statement
- ADAPTIVE setting of parallel_degree_policy (auto DOP feature)
- Extra Load in Database or there has been an increase of activity in a certain application area that requires memory changes
Concurrency Solution
To Solve Oracle Concurrency, perform following actions.
1- Disable Automatic shared memory management by setting SGA_TARGET=0.
To reduce frequency of Shared pool resizing, you can increase the time interval between resizes. To perform this, following parameter should be set dynamically
alter system set "_memory_broker_stat_interval"=999;
2- Check Shared Pool size change, and You can increase it for workaround solution.
select * from V$SGA_RESIZE_OPS where component='shared pool' order by end_time desc;
Or find how many grow operations are performed in Shared pool like following.
select component,oper_type,count(1) FROM gV$SGA_RESIZE_OPS group by component,oper_type order by 3 desc;
Find Maximum and minumum final_size of Shared pool like following.
SELECT MAX(final_size),MIN(final_size) FROM V$SGA_RESIZE_OPS WHERE component='shared pool';
You can increase Shared pool value from 4GB to 10GB or 16GB for workaround solution.
3- Use Bind Variable in SQL and Avoid using literal SQL, thus hard parsing will also reduce shared pool latch and library cache latches
4- Increase sga_target it for workaround solution.
5- Set PARALLEL_DEGREE_POLICY parameter to MANUAL