When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s). exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1'); One can find the default value by executing as follows: select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
Tag: stats
How to view/change statistics retention and space used to store stats history?
See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 25-OCT-11 10.00.55.093000000 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it's size SQL> select occupant_desc, … Continue reading How to view/change statistics retention and space used to store stats history?
How to restore statistics and view statistics history?
Shows in this scenrio there are backup of stats that exists for SCOTT.TEST table SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST'; no rows selected Display current timestamp of last time when the table and index was analyzed SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = … Continue reading How to restore statistics and view statistics history?
How to view and change STATS retention?
Way of changing retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 -- value passed is in days -- If 0 is passed - old statistics are never saved. The automatic purge will delete all statistics history -- If 1 is passed - statistics history is never purged by automatic purge. SQL> exec dbms_stats.alter_stats_history_retention(30); PL/SQL … Continue reading How to view and change STATS retention?
How to find oldest STATS information?
Using dbms_stats.get_stats_history_availability one can find date of oldest STATS information. STATS older than this timestamp cannot be restored. SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 09-FEB-10 10.13.18.954562000 PM -07:00
Information on using dbms_stats.copy_table_stats
Information on dynamic sampling
How to backup and restore statistics using dbms_stats?
Using procedures in DBMS_STATS package one can backup statistics and restore them. Assumes the user scott already has access to execute DBMS_STATS and using "GRANT EXECUTE ON dbms_stats TO scott;" as sysdba one can grant execute access to DBMS_STATS. -- create table to backup statistics, ownname is statistics table owner and stattab is statistics table … Continue reading How to backup and restore statistics using dbms_stats?
How to lock/unlock statistics on a table?
In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn't change. The following example … Continue reading How to lock/unlock statistics on a table?
How to view session stats?
To see current stats of your session, one can run the following query. SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#; To view session stats of another session one can run the query below passing the session id. SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# and … Continue reading How to view session stats?
alexlima.com 