Dear DBA Friends,
DB Version - 11.1.0.7
Need some help understanding what exactly happens when dbms_stats.unlock_schema_stats procedure runs and why would this cause "Library cache lock" contention in the database..?
Here's more background to the issue
I have a shell script to gather schema level stats and scheduled to run from CRON, every Sat 5 AM. Below is sequence of steps -
1. Unlock stats step - Application is seeing stuck threads (DB library cache locks) This continues for about 20-30 min... unlock step completes, lib cache locks are gone.. application is fine...
2. Gather stats step - There are no db/application issues during the gather schema stats step.
3. Lock stats - Again, application stuck threads/DB library cache locks as soon as lock schema step is invoked. It took an hour to complete lock schema stats step. Then everything is fine...
I want to understand below 2 points -
1. What is the impact if I remove unlock/lock steps from my script? We have "Automated maintenance job" disabled, so no risk of stats being altered until we gather next week...
2. How else this could be troubleshooted? Our App folks say this is the least busy time and we still seeing contention issues with gather stats. (specifically unlock/lock steps).
Thanks for all help.