so in addition to multiple thread suite updates where 10s of thousands of
indexes are dropped and created, a partitioned index would presumably mimic the
situation you found via CTAS on seg$.
I’ve always been mystified by large.bsq was not adopted as the default once
storage got pretty doggone cheap. The total cost in blocks of storage is pretty
small compared to pretty much eliminating dictionary contention.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Tanel Poder
Sent: Friday, November 22, 2019 9:11 AM
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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@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
