Re: Wnnn blockers for grant operation

It's certainly possible to come up with a hypothesis that could be checked:

Fact: A grant operation inserts rows into at least one SYS table and if there is no free space in the table then a new extent would have to be allocate to the table.

Fact: Wnnn processes are the things that handle space allocation, and would have to read and update a file header block to mark space in the file as allocated, then read and update a segment header block to attach that space to the segment (then, maybe, read and modify more blocks to do some of the work of formatting the new extent correctly).

Possible explanation: A "library cache lock" suggests parsing activity, but a grant is DDL and DDL may execute on the parse call, so with a little glitch in timing, or some odd concurrency conditions, it's possible that the parse wait (library cache lock) is on the execute and waiting for the Wnnn to supply the space for the grant to be recorded in the data dictionary.

Regards
Jonathan Lewis

On Fri, 25 Aug 2023 at 06:23, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Dear oracle community,
>
> Can anyone help me with an opinion on why Wnnn processes waiting for "db
> file sequential read" will block some grant operations which are waiting
> for "library cache lock"?
>
> Appreciate your answers.
>

--
http://www.freelists.org/webpage/oracle-l