Re: question on initrans

No , actually i had checked the current_obj# in dba_hist_active_sess_history during that high "buffer busy wait" period. So the top contributor/object was pointing to a table followed by another index partition. I have not checked figures wrt index block split during this period though.

Thanks
Pap

On Fri, May 14, 2021 at 10:21 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:

> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Pap <oracle.developer35_at_gmail.com>
> *Sent:* Thursday, May 13, 2021 2:54 PM
> *To:* K Gopalakrishnan <kaygopal_at_gmail.com>
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *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_at_gmail.com>
> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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
>
>
>
>

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