Oracle: 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 01-JAN-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

on the XXPK0EMIANCE index the inital extent is 64k

also these

OBJECT_NAME       OBJECT_TYPE   STATISTIC_NAME         VALUE
XXPK0MINSTANCE    INDEX         ITL waits               1,123
XXIEKILSTANCE     INDEX         ITL waits                 467

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        开发者_StackOverflow社区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

These figure are for the best part of 6 months. I don't think you can really draw anything meaningful from them.

I think you would be better spending your time looking at the reports from AWR (or statspack if you don't have the diagnostics and tuning license). Look at the performance over a 1 hour snapshot during your busy periods and see if anything stands out there.

From a performance perspective, if nobody is complaining, there is probably nothing wrong.

Yes. When an object needs more space it is an overhead. The question is, how often does it need more space and do the users notice an significant issue when this happens. As I suggested earlier. If the users do not perceive a problem, then there probably isn't a problem. I know that sounds a bit reactive, rather than proactive, but there is little point wasting time tuning something that is not causing a problem. :)

As for the stats. Yes. Oracle tracks them and yes, they are useful. My problem with it is you are looking at the stats over a 6 month period. I'm not sure this gives anything useful you can work with. For example, what if most of those figures were accumulated in the first month, then the database has done nothing in the subsequent 5 months, or vice versa. Using these figures doesn't allow you to draw any conclusions in itself.

Reports such as AWR and statspack use the same database statistics, but report a change over time. For example, the change in the stats over the last hour. If I look at a snapshot spanning my busy periods and see that the database is being hammered, I might want to take a look at what is using all the resources. If I check the AWR/statspack report for my busy period and the database is quiet, what is the point in trying to tune it. It's doing nothing.

So the stats are useful, but you have to understand how the context in which they are used affects their value.