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
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.
--
//www.freelists.org/webpage/oracle-l
