oracle table statistics - ghdrako/doc_snipets GitHub Wiki

• https://satya-dba.blogspot.com/2010/06/oracle-database-statistics-rbo-cbo.html

Procedure	Collects
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DICTIONARY_STATS	Statistics for all dictionary objects
GATHER_DATABASE_STATS	Statistics for all objects in a database

Sprawdzenie czy zbierane

SELECT
    table_name,
    num_rows,
    blocks,
    last_analyzed
FROM user_tables
WHERE table_name = 'CARD_PID_20251022';

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SLOG',
    tabname          => 'CARD_PID_20251022',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- oracle sam dobiera probkowanie
    cascade          => TRUE                         -- zbiera te dla indeksow
  );
END;
/

Gdy duza taela

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SLOG',
    tabname          => 'CARD_PID_20251022',
    estimate_percent => 10,  --10% probkowanie
    cascade          => TRUE,
    degree           => 4  -- równoległość
  );
END;
/

Dla calego schematu

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'SLOG',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE
  );
END;
/

Szczegoly statystyk

SELECT
    table_name,
    column_name,
    num_distinct,
    density,
    histogram
FROM user_tab_col_statistics
WHERE table_name = 'CARD_PID_20251022';

Wyczyszczenie statystyk

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('SLOG', 'CARD_PID_20251022');
END;
/

EXEC DBMS_STATS.GATHER_DATABASE_STATS;
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>20);

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, options, statown, no_invalidate, gather_temp, gather_fixed);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'MRT');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',ESTIMATE_PERCENT=>10);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',ESTIMATE_PERCENT=>15);

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','EMP_PK');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','EMP_PK',ESTIMATE_PERCENT=>15);

This package also gives us the ability to delete statistics:
EXEC DBMS_STATS.DELETE_DATABASE_STATS;
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT','EMP');
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','EMP_PK');
EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','SALES');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'"DWH"',OPTIONS=>'GATHER AUTO');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE);

When gathering statistics on system schemas, we can use the procedure DBMS_STATS.GATHER_DICTIONARY_STATS. This procedure gathers statistics for all system schemas, including SYS and SYSTEM, and other optional schemas, such as CTXSYS and DRSYS.