Gather stats of a table, Index or a Schema in Oracle Apps

Gather stats of an oracle table :-

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SMART' , tabname => 'AGENT',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

When analyzing a single table, you must also remember to analyze all associated indexes using dbms_stats.gather_index_stats:

exec dbms_stats.gather_table_stats(null, 'CHANGELOG', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_PARENTID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

Gather stats of an Index :-

exec dbms_stats.gather_index_stats('SCHEMA_NAME', 'TABLE_NAME');

Analyse an Index :-

ANALYZE INDEX SCHEMA_NAME.INDEX_NAME VALIDATE STRUCTURE

/