What is bad # for ‘Space Allocated/Used” and ‘ITL Waits’ in V$SEGMENT_STATS
I ran a query against the V$SEGMENT_STATISTICS view today and got some possibly disturbing numbers. Can some one let me know if they are bad or if I just reading to much into them.
DB has been up since 1/10/2011 so they represent the stats since then. DB size is 3TB
OBJECT_NAME OBJECT_TYPE STATISTIC_NAME VALUE
XXPK0EMIANCE INDEX space allocated 27,246,198,784
ITEMINTANCE TABLE space allocated 22,228,762,624
LITEMINSTANCE TABLE space used 19,497,901,889
XXPK0TEMINSTANCE INDEX space used 17,431,957,592
TTINGCORE TABLE space allocated 8,724,152,320
XXPK0IANCE INDEX space allocated 6,912,212,992
SKISTANCE TABLE space allocated 4,697,620,480
IIXCNSTANCE TABLE space allocated 4,697,620,480
on the XXPK0EMIANCE index the inital extent is 64k
XXPK0MINSTANCE INDEX ITL waits 1,123
XXIEKILSTANCE INDEX ITL waits 467
XXPKLINSTANCE INDEX ITL waits 463
XXPKCE INDEX ITL waits 338
XXIE3ENT INDEX ITL waits 237
If these are bad do they impact performance? My understanding is that being wait states, things stop until they are resolved. Is that true.
Also these looked high, are they?
LATION_PK INDEX logical reads 242,212,503,104
XXAK1STSCORE INDEX logical reads 117,542,351,984
XXPK0TSTANCE INDEX logical reads 113,532,240,160
TCORE TABLE db block changes 1,913,902,176
SDENT TABLE physical reads 72,161,312
XXPK0PDUCT INDEX segment scans 35,268,027
ESTSORE TABLE buffer busy waits 2,604,947
XXPK0SUCORE INDEX buffer busy waits 119,007
XXPK0INSTANCE INDEX row lock waits 63,810
XXPK0EMINSTANCE INDEX row lock waits 58,129
XXPK0NSTANCE INDEX row lock waits 57,776
XXIE2DDSTANCE INDEX row lock waits 54,788
XXPK0DDDSTSCORE INDEX row lock waits 49,167
Am i just reading too much into this? I am not a DBA, our DBA is too busy doing data changes and such to spent time looking at these stuff. I was tasked to try to find out why our DB is so slow.