Re: ITL waits

Hi Mark ,

Thanks for revert !

EVENT                                 COUNT(*) PERCENT
 ----------------------------------- ---------- ----------
 enq: TX - allocate ITL entry            372570        46% ==> update

                                         302698        37%==> update

  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