Appreciate the feedback.
On Mon, Aug 28, 2023, 12:40 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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@xxxxxxxxx>
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.
