oracle statistics - ghdrako/doc_snipets GitHub Wiki

histogram

exec dbms_stats.gather_table_stats(user,'T',options=>'GATHER AUTO',no_invalidate=>false);

https://dbaclass.com/article/useful-gather-statistics-commands-oracle/

  1. Gather dictionary stats:
EXEC DBMS_STATS.gather_dictionary_stats;
  1. Gather fixed object stats:
EXEC DBMS_STATS.gather_fixed_object_stats;
  1. Gather full database stats:
EXEC DBMS_STATS.gather_database_stats(estimate_percent=>15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent=>15,cascade=>TRUE);
EXEC DBMS_STATS.gather_database_stats(estimate_percent=>DBMS_STATS.AUTO_SIMPLE_SIZE,degree=>8);
  1. Gather schema statistics:
EXEC DBMS_STATS.gather_schema_stats('DBACLS');
EXEC DBMS_STATS.gather_schema_stats('DBACLS',estimate_percent=>15);
EXEC DBMS_STATS.gather_schema_stats('DBACLS',estimate_percent=>15,cascade=>TRUE);
EXEC DBMS_STATS.gather_schema_stats(ownname=>'DBACLS',method_opt=> 'FOR ALL COLUMNS SIZE 1',granularity='ALL',degree=>8,cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SIMPLE_SIZE);
  1. Gather table statistics:
EXEC DBMS_STATS.gather_table_stats('DBACLS','EMP');
EXEC DBMS_STATS.gather_table_stats('DBACLS','EMP',estimate_percent=>15);
EXEC DBMS_STATS.gather_table_stats('DBACLS','EMP',estimate_percent=>15,cascade=>TRUE);
EXEC DBMS_STATS.gather_table_stats(ownname=>'DBACLS',tabname=>'EMP',method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',granularity='ALL',degree=>8,cascade=>TRUE);
EXEC DBMS_STATS.gather_table_stats(ownname=>'DBACLS',tabname=>'EMP',method_opt=> 'FOR ALL COLUMNS SIZE 1',granularity='ALL',degree=>8,cascade=>TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SLOG',tabname => 'BASE',estimate_percent => 1);
  1. Gather stats for single partition of a table:
BEGIN
EXEC DBMS_STATS.gather_table_stats(
 ownname=>'DBACLS'
,tabname=>'EMP'
,partname=>ÉMP_JAN201910'
,method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1'
,granularity => ÁPPROX_GLOBAL AND PARTITION',
,degree=>8);
END;
/
  1. Lock/unlock statistics:
select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null  -- lista tabel


EXEC DBMS_STATS.lock_schema_stats('DBACLS');
EXEC DBMS_STATS.lock_table_stats('DBACLS','EMP');
EXEC DBMS_STATS.lock_partition_stats('DBACLS','EMP','EMP_JAN2019');

EXEC DBMS_STATS.unlock_schema_stats('DBACLS');
EXEC DBMS_STATS.unlock_table_stats('DBACLS','EMP');
EXEC DBMS_STATS.unlock_partition_stats('DBACLS','EMP','EMP_JAN2019');

8 . Delete statistics:

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('DBACLS');
EXEC DBMS_STATS.delete_table_stats('DBACLS','EMP');
EXEC DBMS_STATS.delete_column_stats('DBACLS','EMP','EMPNO');
EXEC DBMS_STATS.delete_index_stats('DBACLS','EMP','EMP_PK');
EXEC DBMS_STATS.delete_dictionary_stats;
EXEC DBMS_STATS.delete_fixed_object_stats;
EXEC DBMS_STATS.delete_system_stats;

  1. Setting statistics preference:

  2. Deleting preferences :

  3. Publish pending statistics:

  4. Delete pending statistics:

  5. Upgrade stats table:

  6. View/modify statistics retention period:

  7. create stats table:

  8. Export stats data:

  9. Import stats table data:

  10. Few stats related sql queries: