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@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Mladen Gogala <gogala.mladen@xxxxxxxxx>
Sent: 20 November 2019 17:46
To: oracle-l@xxxxxxxxxxxxx
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
