Skip to Main Content
  • Questions

  • Database getting locked during DBMS STATS Execution

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sai Kiran.

Asked: November 29, 2018 - 3:49 am UTC

Last updated: March 30, 2020 - 12:04 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hello Team,

Would really appreciate any help in this one. Every day our DBMS Stats job runs at around 10 GMT which is around 4pm CST. We have a table that holds close to 10M records and keeps inserting into the table almost every second.

Recently during the time of database scan we are having issues with the table with million records getting locked and does not process any further transactions. We are being forced to restart our database instance during that time to kill these jobs.

Does DBMS scan actually establish any lock on the table and prevent any DML? Any idea why the inserts that we make almost 100k from morning fails but gets hung during that scan time?

Thank you so much for your assistance.

and Chris said...

You should be able to gather stats and use the table normally at the same time.

That said, there are a couple of bugs relating to dbms_stats & library cache locks. In particular if you have partitioned tables.

But the first step is to verify that it really is the stats gathering job that's causing the issue. Either move the time of the default job, lock stats on the table or both. See if this helps in any way.

If you verify that it is related to stats gathering, it's time to contact support.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Happens at the time of database scan

Sai Kiran, November 29, 2018 - 5:02 pm UTC

Thank you so much for your response. The issue starts happening only during the time when the jobs starts to run and not for rest of the day.

Also what are the bugs in this process??

That said, there are a couple of bugs relating to dbms_stats & library cache locks.

Is this applicable only for 11g ?
Chris Saxon
November 29, 2018 - 5:22 pm UTC

For example: "Bug 19790972 "library cache lock" waits due to DBMS_STATS gather of stats for a subpartition"

But I don't know enough about your situation to be sure that's the cause. It could be something else! If you've definitely narrowed the problem down to stats gathering, you need to contact support.

Interested in Bug information

A reader, March 27, 2020 - 5:02 pm UTC

Hi Chris,
That said, there are a couple of bugs relating to dbms_stats & library cache locks. In particular if you have partitioned tables.


Could you please share bug information which you are pointing above.
I am getting library cache lock event for SQL ID "dbms_stats.gather_database_stats_job_proc ( )" in AWR and at the same time other SQL's running slow.
Connor McDonald
March 30, 2020 - 12:04 am UTC

You should check with Support, because you would want to make sure observations you are making are related or not to the bugs below:

Bug 7292916 - Improved row cache callbacks / Slow DBMS_STATS
Bug 19790972 - "library cache lock" waits due to DBMS_STATS gather of stats for a subpartition
Bug 20754583 - 'library cache: mutex x' in different instances where dbms_stats was executed
Bug 6011045 - DBMS_STATS causes deadlock between 'cursor: pin S wait on X' and 'library cache lock'

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.