Surveying the allocated usable space (ie. for example not including partially
used block space not on the freelist or bitmap because of pctfree AND blocks
with not enough space to actually allocate a row for the particular dictionary
base object) on a periodic basis can be useful if you have recorded a trend map
for, say, the new blocks used over time to get, say, the maximum for any month.
Then, if a survey indicates an underflow, allocate a maximum (so-far) of space
for each underlying object with less than a (so-far) allocated usable month of
space.
Such monitoring was (in addition to resource acquisition planning, alerting to
unusual bursts of usage, and the approaching (now obsolete ) maxexts problem)
a purpose of “exthist.” I long since stopped maintaining “exthist” (mostly
because lots of folks shared the source code, which was just a set of scripts
and scheduler information).
It’s not rocket science. But if you do track growth of objects in a fine
grained sense (like once a day or so) so you have reasonable data upon which
automated processes can act, then you can nearly eliminate the chances that
anything needs fresh space allocation during anything like a peak activity
where the race conditions JL mentions also occur.
JL’s analysis is almost certainly correct barring some bug we all haven’t
noticed yet. And it seems unlikely a bug in space allocation resource
minimization would go undetected for long. So my bet would be that JL is
correct.
I believe this prophylaxis is useful even if you never hit this condition,
because space is cheap and any operation you don’t have to do during peak time
means that operations during peak time run faster. And even if your average
peak time has lots of slack compared to requirements, it is really difficult to
predict unusual peak times.
If you’re running EBIZ, at least do this prior to upgrades and patches.
Good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Laurentiu Oprea
Sent: Monday, August 28, 2023 2:11 PM
Cc: ORACLE-L (oracle-l@xxxxxxxxxxxxx)
Subject: Re: Wnnn blockers for grant operation
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.
