Re: ITL waits

Adding to mail , Q represents a query , so Q1=query1,q2=query2,q3=query3...

On Fri, 5 Apr 2024 at 11:39, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Mark ,
>
> Thanks for revert !
>
> EVENT COUNT(*) PERCENT
> ----------------------------------- ---------- ----------
> enq: TX - allocate ITL entry 372570 46% ==> update
> statement updates status column ,avg.row len:138 and blocks :419217 table
> size:3.2GB
> 302698 37%==> update
> statement updates status and one more column ,avg.row len :207 table size
> :92 GB
>
> 1.not sure how insertion happens , but it updates status column in both
> tables
> 2.Block size is 8K , i am not sure about rows are often multi-block
> rows or not
> 3.Rows are not migrated rows
>
> Update statement does not has any older history of execution , from last
> 2 month of AWR data , we could see that only got executed at 3 -APR , below
> is execution with 30 mins of snap.
>
> Q1 Q2 Q3 Q3
> 9672 2585 2259 2602
> 803 1112 1070 1111
> 4245 3839 3316 3840
> 1707 1280 1358 1279
> 2827 1659 1968 1659
> 874 1592 1146 1590
> 1715 2417 2479 2419
> 11487 8151 18507 8145
> 1610 1676 1757 1675
> 386 693 575 694
> 1772 690 785 691
> 7596 5227 5559 5227
> 258 474 283 475
> 784 964 511 970
> 1810 2076 1315 2073
> 3930 2922 3154 2918
> 3235 3172 2628 3171
> 3971 3350 3261 3350
> 2735 2749 1707 2750
> 3094 4233 4409 4235
> 5391 5839 6012 5842
> 7309 6428 6899 6417
> 2335 2017 1839 2016
> 10455 9438 10096 9438
> 4702 2731 2417 2734
> 3316 9801 10653 9804
> *188891* *187068* *189944* *187061*
> 1707 1027 1066 1031
> 11642 4481 8448 4490
> 8852 9287 4333 9318
> TOTAL 309111 288978 299754 289025
>
>
> Regards,
> Krishna
>
> On Fri, 5 Apr 2024 at 03:31, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *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_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