Oracle Concurrency Wait Event Solution: library cache lock cursor and pin S wait on X

Hi,

I will explain how to solve Concurrency Wait Event ( library cache lock cursor and pin S wait on X ) in Oracle in this article.

 

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.

 

 

 

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 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

 

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

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *