RE: Wnnn blockers for grant operation

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Monday, August 28, 2023 2:11 PM
Cc: ORACLE-L (oracle-l_at_freelists.org) Subject: Re: Wnnn blockers for grant operation  

Appreciate the feedback.  

On Mon, Aug 28, 2023, 12:40 PM Jonathan Lewis <jlewisoracle_at_gmail.com> 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_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