And seg$ could run into problems if your system has run into the issue of
having undo segments with very large numbers of small (64KB extents) which get
release or acquired in extreme bursts from concurrent processes occsionally.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: 21 November 2019 21:06
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: ITL waits in the dictionary
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
--
//www.freelists.org/webpage/oracle-l
