gather statistics, dbms_stats

Manage statistics using dbms_stats 
 
create stat table
begin
dbms_stats.create_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable',
tblspace=> 'tools');
end;

drop stat table
begin
dbms_stats.drop_stat_table(
ownname => 'SYSADM',
stattab => 'mystattable');
end;

gather database Stat
begin
dbms_stats.gather_database_stats(
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete database stat
EXEC DBMS_STATS.delete_database_stats;

gather schema Stat
begin
dbms_stats.gather_schema_stats(
ownname => 'SYSADM',
method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade=>TRUE);
end;

delete schema stat
EXEC DBMS_STATS.delete_schema_stats('SCOTT');

export table stat
dbms_stats.export_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

import table stat
begin
dbms_stats.import_table_stats(
ownname => 'SYSADM',
tabname =>'my_table',
stattab =>'mystattable',
statid =>'bkup',
cascade =>true);
end;

delete table stat
begin
dbms_stats.delete_table_stats(
ownname => 'SYSADM',
tabname =>'my_table');
end;

gather table stat
begin
dbms_stats.gather_table_stats(
ownname => 'SYSADM',
tabname => 'my_table',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1',
estimate_percent => 15,
cascade => TRUE );
end;

gather index stat
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

delete index stat
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

delete column stat
exec dbms_stats.delete_column_stats(USER, 'PERSON', 'PERSON_ID');

check stats
select table_name, num_rows from dba_tables
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select table_name,index_name, num_rows from dba_indexes
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name ;

select TABLE_NAME, COLUMN_NAME, NUM_BUCKETS from dba_tab_columns
where table_name in ('PS_JOB','PS_SCRTY_TBL_DEPT','PS_NAMES','PS_PERSON') order by table_name, column_name;

select count(*) from PS_SCRTY_TBL_DEPT ;
select count(*) from PS_JOB ;
select count(*) from PS_NAMES ;
select count(*) from PS_PERSON ;


Steps

1. Export current stats
2. Gather or Import or Set Stats
3. trace
4. tkprof

More info



Hope this helps. Regards Rupam