RE: ITL waits

From: Pierre Labrousse <"Pierre>
Date: Thu, 4 Apr 2024 16:13:35 +0000
Message-ID: <PR1P264MB182421273EBECE962156C5C9F63C2_at_PR1P264MB1824.FRAP264.PROD.OUTLOOK.COM>

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) Mobile +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

Received on Thu Apr 04 2024 - 18:13:35 CEST