Pap, do you mean, "followed by an index block split" instead of "followed by an
index partition"?
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Pap <oracle.developer35@xxxxxxxxx>
Sent: Thursday, May 13, 2021 2:54 PM
To: K Gopalakrishnan <kaygopal@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: question on initrans
Thank you very much Gopal and Mark. Your explanation really helped me to
understand it better. Thank you.
I will try to dig more into the buffer busy wait part and try to see pctfree
and minimize_records_per_block options. Btw From the current_obj# in
v$actie_session_history i see the top contributor is pointing to a non
partitioned table(In which single row INSERT is happening from multiple
session) followed by an index partition(index is on a sequence generated
column) and here UPDATE happening on that indexed column.
Regards
Pap
On Wed, May 12, 2021 at 9:01 PM K Gopalakrishnan
<kaygopal@xxxxxxxxx<mailto:kaygopal@xxxxxxxxx>> wrote:
Pap-
ASM manages the storage at the host level and ASSM manages the storage at
Segment level.
Within the segments, you have extents and ASSM manages extents (allocation,
size etc)
ITLs manage block level concurrency issues.
On the other hand, Freelists maintain the list of blocks which are eligible for
INSERTs in non ASSM managed extents.
With ASSM, there are no freelists as the insert eligible blocks are managed by
bitmap blocks in segment headers.
There are multiple levels of bitmap blocks (Level 1 - Leaf BMB/ Level 2 -
Branch BMB and Level 3 (root BMB)) in a tree foramt manages the space inside
the segments.
Most of these things are automated and seldom require any changes. You can
still set PCTFREE here as that is honored.
PCTUSED used to decide when to link/unlink the blocks from the freelist chain.
This is replaced by different groupings in ASSM.
BTW you mention you are encountering Buffer Busy Waits and this could be
totally a different issue altogether.
In this case, you have to reduce the block level concurrency - (rather than
improving it with ITL fixes).
You can either increase the PCTFREE to higher values or use Jonathan Lewis's
trick (minimize_records_per_block).
-Gopal
On Tue, May 11, 2021 at 10:41 PM Pap
<oracle.developer35@xxxxxxxxx<mailto:oracle.developer35@xxxxxxxxx>> wrote:
Thank you so much for this detailed explanation.
If I get it correctly, it's the ASM tablespace(which we do have) manages the
dynamic increase and decrease of ITL or initrans based on concurrency so we
don't' need to set them explicitly. Also it should be looked into only if by we
see the ITL related waits. Correct if my understanding is correct. As you
mentioned in case of high concurrency we may need to increase pctfree to higher
value, will that not automatically managed by ASM and also in such cases do we
see any other waits or same ITL waits will be seen in AWR?
Is freelists also managed dynamically as above and no need manually to be set
to higher on a ASM? (Note-Actually we are encountering buffer busy waits during
high activity period and the object it's pointing to is table followed by
index.)
Regards
Pap
On Wed, 12 May 2021, 8:20 am K Gopalakrishnan,
<kaygopal@xxxxxxxxx<mailto:kaygopal@xxxxxxxxx>> wrote:
Pap-
The parameter INITRANS - defines number of ITL (Interested Transaction Lists)
slots _initially_ created during new blocks allocation to the segment.
Any transaction that is interested in conducting DMLs on that block, should
fill in some details about that transaction.
This includes setting the Transaction ID, Undo Block Address, Lock Byte ,
Cleanout System Change Number and Free Space Credit.
The row level locking in Oracle is implemented with the help of these ITL
information as well.
In other words, treat ITL like your parking space in the office space. Anyone
entering the office must park their car in the parking space allotted.
You can park your car in the allotted space or any free space in that building.
Alloted parking spaces are fixed ITLs during the creation of the block.
If allocated parking space is full, you park in the free space in the building.
Once you use this free space, this space will never return data storage, only
for ITLs.
This defaults to 2 per table and 3 per index IIRC, but DBA_TABLES still show
the default as 1 per table and 2 per index.
You can quickly validate this by simple block dumps.
Note that the ITLs can be created when there is enough space (~24 bytes per
ITL) in the variable header space in the data block.
So when there is a demand for additional transaction slots, oracle
automatically creates ITL slots subject to free space in the block.
If there is no space in the block , you might see contention for ITLs and you
will see ITL waits in the session_wait views.
There are some internal validations in oracle to limit the variable part of the
block header that can not exceed 50% of the block size.
i.e you can not create 50 ITLs if the block size is 2K. (50 ITLs require
50x24=1200+ bytes which is clearly over 50% of the block size).
There are many other checks similar to these as well to stop the abuse :)
Also the requirement of such high ITL depends on the number of active rows per
block.
If the block itself has less than 50 rows, you would neer need 50 ITLs for that
block.
Similar to parking slot analogy, you would not need more parking spots than the
number of residents.
Readers (aka visitors) do not need any ITL entries to record their transactions.
You do not need to manually set the ITLs, as ITLs will be created on the need
basis.
If there is an issue with the ITLs , you will clearly see this in the
session_waits.
Also segment_stats has specific metrics to show the objects with ITL contention.
If you are not seeing any of this in the DB, this recommendation from oracle is
totally irrelevant.
So 50 ITLs (per table & 100 ITLs on their indexes) seems such a waste of block
space.
There are much better mechanisms to handle this. If you suspect such a high
rate of concurrency,
please set the PCTFREE to a higher number (say 15%) so that additional ITLs can
be created when required.
The space reserved with PCTFREE can not be used for INSERTS. They are used for
ITL expansion and updates.
-Gopal
On Tue, May 11, 2021 at 11:47 AM Pap
<oracle.developer35@xxxxxxxxx<mailto:oracle.developer35@xxxxxxxxx>> wrote:
This database was recently moved from HP to exadata X7 and the DB version also
migrated from 11.2.0.4 to 19.9.0.0.0. It's an OLTP database. In the past we saw
some contention and got a recommendation from Oracle to set INITRANS value of
certain objects(both table and indexes) to as high as 50. And now the team is
thinking to set the initrans value to ~10 for almost all the objects(~around
~200+ objects). Want to understand , what is the negative side of setting it to
this non default value considering this is an OLTP database exposed to many
concurrent DML/query and we encounter "buffer busy waits" many times?
Regards
Pap
