Oracle: Statistics Update

Categories:

  less than a minute  

Get Last statistics Update

SELECT
    table_name,
    last_analyzed,
    sample_size,
    num_rows sample_pct
FROM
    dba_tables
WHERE
    owner = 'your_schema_name_here'
ORDER BY
    last_analyzed;

Note:

  • last_analyzed column shows the last analyzed date
  • sample_size and sample_pct should be same (or difference should be very less)

Statistics Update (for schema)


execute DBMS_STATS.GATHER_SCHEMA_STATS(
    OWNNAME=> 'your_schema_name_here',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
    METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',
    DEGREE => 8,
    CASCADE=> TRUE
);
 

Statistics Update (for table)


EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'your_schema_name_here',
    'your_table_name_here',
    estimate_percent=>'',
    method_opt => 'for all indexed columns size auto',
    DEGREE => 8,
    CASCADE => TRUE
);