RE: ITL waits in the dictionary
Date: Fri, 22 Nov 2019 10:28:48 -0500
Message-ID: <027a01d5a149$8984e450$9c8eacf0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Friday, November 22, 2019 9:11 AM
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
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_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
Received on Fri Nov 22 2019 - 16:28:48 CET