Gathering Stats

Command to find stats for the tables: 

exec dbms_stats.delete_table_stats('PSHCM','PS_A_HC_DED_BAL_ER');

exec dbms_stats.gather_table_stats(ownname=> 'PSHCM', tabname=> 'PS_A_HC_DED_BAL_ER', estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, block_sample=>FALSE,Degree=> 8, cascade=> true);

Command to find the stats for four different tables: Note* PSHCM is the ACCESS ID

exec dbms_stats.gather_table_stats('PSHCM','PS_PAY_EARNINGS',degree=>dbms_stats.default_degree);
exec dbms_stats.gather_table_stats('PSHCM','PS_PERSONAL_DT_FST',degree=>dbms_stats.default_degree);
exec dbms_stats.gather_table_stats('PSHCM','PS_FREQUENCY_TBL',degree=>dbms_stats.default_degree);
exec dbms_stats.gather_table_stats('PSHCM','PS_EMPLOYMENT',degree=>dbms_stats.default_degree);


To lock the stats for the table in peoplesoft :
exec dbms_stats.lock_table_stats('SYSADM', 'PSDBOWER');

To lock the stats for the table in peoplesoft

exec dbms_stats.unlock_table_stats('SYSADM', 'PSDBOWER');

To check which table in the database has stats lock on them.

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'PSDBOWNER' and owner = 'SYSADM';


The GATHER_DATABASE_STATISTICS procedure collects optimizer statistics for the entire database.


EXECUTE dbms_stats.gather_database_stats (-
> ESTIMATE_PERCENT => NULL, -
> METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', -
> GRANULARITY => 'ALL', -
> CASCADE => 'TRUE',-
> OPTIONS => 'GATHER AUTO');

If the statastics is locked

SQL> exec dbms_stats.unlock_table_stats('PSHCM', 'PS_FST_SCRTY_2_TMP');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('PSHCM','PS_FST_SCRTY_2_TMP',degree=>dbms_stats.default_degree);

PL/SQL procedure successfully completed.


3. Run the following SQL again on the OLTP DB to verify whether any lock remaining.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;