So you have a particular table.
Let’s talk about that table.
How many rows are there per block for that table? (min, max, average).
How many insert, update, and delete operations can reasonably be simultaneously
in play for that table?
I’m restraining myself from asking about indexes because you wrote that you’re
sure that is not it.
Are rows inserted into this table born “full length” or is a skeleton key
insert made with lots of null columns which are then updated to actual values?
Are the rows often multi-block rows?
Are there many migrated rows?
After a row is inserted and perhaps updated a few times very soon after the
insert, does it tend to become quiet and rarely if ever be updated again?
Your answers to these questions will tend to reduce the possible improvement
suggestions from hundreds to a dozen or fewer.
Good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Krishnaprasad Yadav
Sent: Thursday, April 04, 2024 1:02 PM
To: Pierre Labrousse
Cc: Oracle L
Subject: 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@xxxxxxxxx> 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@xxxxxxxxxx>
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)
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>
@
<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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> de la part
de Krishnaprasad Yadav <chrishna0007@xxxxxxxxx>
Envoyé : jeudi 4 avril 2024 17:05
À : Oracle L <oracle-l@xxxxxxxxxxxxx>
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
