Re: ITL waits

Adding some more info , querys lio is around 15 to 17 only

On Thu, 4 Apr, 2024, 22:31 Krishnaprasad Yadav, <chrishna0007_at_gmail.com> wrote:

> Dear Pierre,
>
> Thanks for your reply
>
> I have seen top object is table from reports , so 100%sure on it
>
> I know inittrans would help but what i feel that its might be cascaded one
> (suspecting) because as i mentioned
> Concurrency is low , table size is low
> Not sure about connection strom which i need to work on
>
> Regards,
> Krish
>
>
> On Thu, 4 Apr, 2024, 21:43 Pierre Labrousse, <Pierre.Labrousse_at_digora.com>
> wrote:
>
>> Hello Krishna,
>>
>> Are you sure that waits are on table segment and not on index segment ?
>> Generally this waits appears more on indexes than tables.
>>
>> Effectively, INITRANS should be raised (by default it's 1 for table and
>> 2 for index), but it will be taken into account only for new blocks (unless
>> you move table or rebuild index). You could also raise PCTFREE to have less
>> rows into the same block or create a new tablespace with a data block size
>> smaller to minimize number of rows into the same block and move your
>> tables/index into this tablespace.
>> Partitioning would be a good solution but it is "just" extra cost 😉
>>
>> Best regards.
>> Pierre
>>
>>
>> *Pierre **LABROUSSE*
>> * Consultant DBA ORACLE (OCM 10g/11g/12c)*
>> *M*obile +33 (0)7 56 05 27 38
>> *pierre.labrousse
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>**_at_
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>**digora.co
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>m
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com>*
>>
>> ------------------------------
>> *De :* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> de
>> la part de Krishnaprasad Yadav <chrishna0007_at_gmail.com>
>> *Envoyé :* jeudi 4 avril 2024 17:05
>> *À :* Oracle L <oracle-l_at_freelists.org>
>> *Objet :* ITL waits
>>
>> Dear Gurus ,
>>
>> i have made observation of ITL waits in DB , and it lasted more than
>> 4+hrs
>> i see that certain new DML was introduced newly in database , and same
>> time these ITL wait events triggered .
>>
>> I validated top object i see 3 tables were seen as DML are using these
>> tables , i saw concurrency it was around 3k to 10K in 30 mins of snap .
>> Also table is non partitioned and it was only 3GB in size .
>>
>> total execution in DAY was around 400K for all 3 DML statements , seeing
>> these bit surprise that how with such low concurrency its landed in issue
>> Also able extent management is AUTO .
>>
>> i know increasing INITTRANS will help them (probably ?) but i am
>> suspecting its coming something out of DB , i validated OS watcher details
>> , i see some potential bottleneck but those stats which i suspect as
>> bottleneck are seen in good time as well , i validate CPU utilization
>> which was high during start of issue but after 10 mins it got down to 60%
>> but spike remain in DB , validate IO busyness trend is similar to CPU .
>>
>> It will be helpful if you can share any of experience from which i can
>> relate and take ahead my case .
>>
>> Regards,
>> Krishna
>>
>

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