User Tools

Site Tools


tech:rdbms:ora_stats

Oracle STATS (schema,table)

Stats for a complete schema e.g. SYSADM

BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME => 'SYSADM', ESTIMATE_PERCENT => NULL, BLOCK_SAMPLE => FALSE, DEGREE => 32, GRANULARITY => 'ALL', CASCADE => TRUE );
END;
/

Stats for a table e.g. PS_R_PER507

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYSADM', TABNAME => 'PS_R_PER507', ESTIMATE_PERCENT => NULL, BLOCK_SAMPLE => FALSE, DEGREE => 32, GRANULARITY => 'ALL', CASCADE => TRUE );
END;
/

Stats for Dictionary Objects - helps with Cost Based Optimizer

BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
   DBMS_STATS.GATHER_DATABASE_STATS(gather_sys => TRUE);
   DBMS_STATS.GATHER_DICTIONARY_STATS;
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;   
END;
/
tech/rdbms/ora_stats.txt · Last modified: 2024/06/21 12:04 by 127.0.0.1