Re: ITL waits

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