SQL Performance (MOSC)

MOSC Banner

Metrics in User-defined statistics 11g ?

edited May 23, 2013 4:55AM in SQL Performance (MOSC) 11 commentsAnswered
Hi ,

We have upgraded our Oracle database ( 5To) from Oracle 10gR2 to 11gR2.

Since 2008 statistics are user-defined statistics (fixed) by using:

DBMS_STATS.SET_TABLE_STATS

DBMS_STATS.SET_INDEX_STATS

These statistics are based on the following metrics:

FOR EACH TABLE:

    NUM_ROWS

    BLOCKS

    AVG_ROW_LEN

    LAST_ANALYZED

FOR EACH INDEX:

    NUM_ROWS

    LEAF_BLOCKS

    DISTINCT_KEYS,

    AVG_LEAF_BLOCKS_PER_KEY

    AVG_DATA_BLOCKS_PER_KEY

    CLUSTERING_FACTOR

    BLEVEL

    LAST_ANALYZED

FOR EACH COLUMN:

    COLUMN_NAME

    NUM_DISTINCT

    DENSITY

    NUM_NULLS

    AVG_COL_LEN

    LAST_ANALYZED

My question is:

Theses metrics  , are they sufficient for the optimizer 11g to find a good execution plan?  Should we add others metrics?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center