DBMS_STATS

Сбор статискики:

DBMS_STATS.gather_database_stats;
DBMS_STATS.gather_database_stats(estimate_percent => 15);
DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

DBMS_STATS.gather_schema_stats('owner');
DBMS_STATS.gather_schema_stats('owner', estimate_percent => 15);
DBMS_STATS.gather_schema_stats('owner', estimate_percent => 15, cascade => TRUE);

DBMS_STATS.gather_table_stats('owner', 'table_name');
DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 15);
DBMS_STATS.gather_table_stats('owner', 'table_name', estimate_percent => 15, cascade => TRUE);
DBMS_STATS.gather_index_stats('owner', 'index_name');

DBMS_STATS.gather_dictionary_stats;

Удаление статистики:

DBMS_STATS.delete_database_stats;
DBMS_STATS.delete_schema_stats('owner');
DBMS_STATS.delete_table_stats('owner', 'table_name');
DBMS_STATS.delete_column_stats('owner', 'table_name', 'column_name');
DBMS_STATS.delete_index_stats('owner', 'index_name');

DBMS_STATS.delete_dictionary_stats;


Представления такие:

DBA_TABLES
DBA_TAB_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUB_PARTITIONS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTIONS

DBA_TAB_HISTOGRAMS
DBA_PART_HISTOGRAMS
DBA_SUBPART_HISTOGRAMS


Примеры:

create  table  t  (x int);

insert into t values ( 1 );


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.delete_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.lock_table_stats( 'ANGOR', 'T');
end;
/

begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , force=>true);
end;
/

begin
dbms_stats.unlock_table_stats( 'ANGOR', 'T');
end;
/

begin
dbms_stats.delete_table_stats( 'ANGOR', 'T' );
end;
/

begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , estimate_percent => 15 );
end;
/



select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';



drop table t;

create table t as select * from all_objects;

create index t_idx on t (object_name);


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , cascade => true );
end;
/


select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';
    
    
select owner,
table_name,
index_name,
round(sample_size*100/nvl(decode(num_rows, 0, 100000, num_rows), 1000000),2) percent,
last_analyzed
from all_ind_statistics
where owner = 'ANGOR'
     and table_name = 'T';


begin
dbms_stats.delete_index_stats( 'ANGOR', 'T_IDX' );
end;
/


begin
dbms_stats.gather_table_stats( 'ANGOR', 'T' , cascade => true, estimate_percent => 15 );
end;
/

select owner,
table_name,
round(sample_size/decode(num_rows, 0, 100000000000, num_rows)*100,2) percent,
last_analyzed
from all_tables
where owner = 'ANGOR'
     and table_name = 'T';
    
    
select owner,
table_name,
index_name,
round(sample_size*100/nvl(decode(num_rows, 0, 100000, num_rows), 1000000),2) percent,
last_analyzed
from all_ind_statistics
where owner = 'ANGOR'
     and table_name = 'T';



begin
dbms_stats.gather_index_stats( 'ANGOR', 'T_IDX' , estimate_percent => 15 );
end;
/