Re: ITL waits in the dictionary

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 21 Nov 2019 21:06:28 +0000
Message-ID: <CWXP265MB17500CA825134E4C73226DC7A54E0_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>

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

6 rows selected.

My pain is obviously with the SYS.IND$ table, which doesn't contain anything of importance, only the definitions of all the indexes in the database. I should have changed SQL.BSQ but that water is now under the bridge. Is there anything else I can do to increase INITRANS for SYS.IND$? The advice like "tell your users not to create indexes" is obviously impossible to follow.

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

Received on Thu Nov 21 2019 - 22:06:28 CET