Re: ITL waits in the dictionary

I have an old presentation of a severe SEG$ contention case (both gc buffer busy waits and enq: ITL waits against it) caused by highly parallel CTAS to partitioned table that caused many new segments (one for each partition) created *and* later extended concurrently. That caused SEG$ block modification contention.

https://www.slideshare.net/tanelp/troubleshooting-complex-performance-issues-oracle-seg-contention

--
Tanel Poder
https://blog.tanelpoder.com/seminar


On Thu, Nov 21, 2019 at 4:07 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
wrote:



>

> Trying to come up with some reason why ind$ might be subject to ordinary


> ITL waits - if you're gathering stats use a level of concurrency greater

> than one the ind$ could be subject to N concurrent processes updating it.

>

>

> Regards

> Jonathan Lewis

>

>

> ________________________________________

> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on

> behalf of Mladen Gogala <gogala.mladen_at_gmail.com>

> Sent: 20 November 2019 17:46

> To: oracle-l_at_freelists.org

> Subject: ITL waits in the dictionary

>

> I have a bad habit of checking whether any objects in the database are

> being used so intensely to cause ITL waits. I do occasionally encounter an

> object and if it's a user object, I can usually fix it. However, here is

> something that I'm stuck with:

>

>

> SQL> select owner,object_name,object_type,value from v$segment_Statistics

> where statistic_name like 'ITL%' and value>0;   OWNER    OBJECT_NAME

> OBJECT_TYPE    VALUE

> ________ ______________ ______________ ________

> SYS      IND$           TABLE              1049

> SYS      SEG$           TABLE                69

> SYS      CDEF$          TABLE                22

> SYS      I_OBJ2         INDEX                 1

> SYS      I_OBJ5         INDEX                 1

> SYS      I_COL1         INDEX                 4

>

>

>


--
http://www.freelists.org/webpage/oracle-l