ITL waits in the dictionary
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 20 Nov 2019 12:46:42 -0500
Message-ID: <58d55708-9ffc-8bed-2b02-369ce9b7b933_at_gmail.com>
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:
Date: Wed, 20 Nov 2019 12:46:42 -0500
Message-ID: <58d55708-9ffc-8bed-2b02-369ce9b7b933_at_gmail.com>
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-lReceived on Wed Nov 20 2019 - 18:46:42 CET