Gather Statistics And Grant To Table Caused Library Cache Lock And Cursor: Mutex S (Doc ID 2892264.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

The database was recently upgraded from 11.2.0.4 and gather stats job ran fine nightly on this database prior to upgrade. The database environment is using Multi Threaded Server (MTS) shared server connections.

This issue is occurring on multiple databases for the custom gather stats job. Trying to gather statistics for table, and after gather statistics , grant to dba to invalidate SQL.

This action caused library cache lock, cursor: mutex S, and library cache: mutex X wait events when the cursor invalidations happens due to DDL on an object used in the SQL.

There were some SQL with high execution rate observed which were creating a new child cursor for every execution and the version count report showed high version counts with the following reason USER_BIND_PEEK_MISMATCH.

Changes

 Upgraded 11.2.0.4 database to 19.9 recently, issue only occurs for the nightly custom cron job for gather stats on upgraded database.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.