STATISTICS

STATISTICS

Gather stats for schema

Begin
dbms_stats.gather_schema_stats(
ownname => ‘SCOTT’, — schema name 
options => ‘GATHER AUTO’,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => ‘for all columns size repeat’,
degree => 24
);
END;
/

Gather stats for a table

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘SCOTT’,
tabname => ‘TEST’,
cascade => true, —- For collecting stats for respective indexes 
method_opt=>’for all indexed columns size 1′,
granularity => ‘ALL’,
estimate_percent =>dbms_stats.auto_sample_size, 
degree => 8);
END;
/

— For a single table partition
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘SCOTT’,
tabname => ‘TEST’, — TABLE NAME
partname => ‘TEST_2018’ — PARTITOIN NAME
method_opt=>’for all indexed columns size 1′,
GRANULARITY => ‘APPROX_GLOBAL AND PARTITION’,
degree => 8);
END;
/

Lock/unlock statistics

— Lock  statistics

EXEC DBMS_STATS.lock_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.lock_table_stats(‘SCOTT’, ‘TEST’);
EXEC DBMS_STATS.lock_partition_stats(‘SCOTT’, ‘TEST’, ‘TEST_2018’);

— Unlock statistics

EXEC DBMS_STATS.unlock_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.unlock_table_stats(‘SCOTT’, ‘TEST’);
EXEC DBMS_STATS.unlock_partition_stats(‘SCOTT’, ‘TEST’, ‘TEST_2018’);

— check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = ‘TEST’ and owner = ‘SCOTT’;

Export import statistics

— Create staging table to store the statistics data

exec dbms_stats.create_stat_table(ownname => ‘SCOTT’, stattab => ‘STAT_BACKUP’,tblspace=>’USERS’);

— Export stats

exec dbms_stats.export_table_stats(ownname=>’SCOTT’, tabname=>’EMP’, stattab=>’STAT_BACKUP’, cascade=>true);

— Import stats

exec dbms_stats.import_table_stats(ownname=>’SCOTT’, tabname=>’EMP’, stattab=>’STAT_BACKUP’, cascade=>true);

— STALE STATS FOR TABLE
select owner,table_name,STALE_STATS from dba_tab_statistics where owner=’&SCHEMA_NAME’ and table_name=’&TABLE_NAME’;

— FOR INDEX
select owner,INDEX_NAME,TABLE_NAME from DBA_IND_STATISTICS where owner=’&SCHEMA_NAME’ and index_name=’&INDEX_NAME’;

Table statistics history

— For getting history of TABLE statistics
setlines 200
col owner for a12
col table_name for a21
select owner,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where table_name=’&TABLE_NAME’;

Space used to store stats

— Space currently used to store statistics in SYSAUX in KBytes,

select occupant_desc, space_usage_kbytes from v$sysaux_occupants
where OCCUPANT_DESC like ‘%Statistics%’;

Enable incremental stats collection

— Check the status of incremental pref

select dbms_stats.get_prefs(‘INCREMENTAL’, tabname=>’EMPLOYEE’,ownname=>’SCOTT’) from dual;

FALSE

— Enable incremental stats collection

SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘SCOTT’,’EMPLOYEE’,’INCREMENTAL’,’TRUE’);

PL/SQL procedure successfully completed.

— Check the pref again:

select dbms_stats.get_prefs(‘INCREMENTAL’, tabname=>’EMPLOYEE’,ownname=>’SCOTT’) from dual;

TRUE

Delete statistics

— Delete statistics of the complete database
EXEC DBMS_STATS.delete_database_stats;

— Delete statistics of a single schema

EXEC DBMS_STATS.delete_schema_stats(‘DBACLASS’);

— Delete statistics of single tabale
EXEC DBMS_STATS.delete_table_stats(‘DBACLASS’, ‘DEPT’);

— Delete statistics of a column
EXEC DBMS_STATS.delete_column_stats(‘DBACLASS’, ‘DEPT’, ‘CLASS’);

–Delete statistics of an index

EXEC DBMS_STATS.delete_index_stats(‘DBACLASS’, ‘CLASS_IDX’);

–Delete dictionary statistics in db

EXEC DBMS_STATS.delete_dictionary_stats;

— If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>’TEST’,STATTAB =>’STAT_TEST’);

Upgrade statistics in db

— If we are importing stats table from higher version to lower version,
then before importing in the database, we need to upgrade the stats table.

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(OWNNAME =>’TEST’,STATTAB =>’STAT_TEST’);

  • September 27, 2018 | 23 views
  • Comments