Oracle DBA Daily Experiences

Another way of changing retention
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
—————————
31

SQL> exec dbms_stats.alter_stats_history_retention(30);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
—————————
30

Using dbms_stats.get_stats_history_availability one can find date of oldest AWR information

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
—————————————————————————
09-FEB-10 10.13.18.954562000 PM -07:00

DBA_TAB_MODIFICATIONS view contains tables that were modified since last time statistics was gathered on the table. It contains the number of inserts, deletes and updates have occurred on the table. In 10g this is turned on automatically when the statistics_level is typical/all.

By calling DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO one can flush monitoring information from memory into dictionary. When DBMS_STATS.GATHER_*_STATS is called it calls DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO as one of the step to flush modification information.

In the example below it shows when dbms_stats.gather_schema_stats is called it flushes modification information in the dictionary.

SQL> select timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;

TABLE_NAME TIMESTAMP
—————————— ——————–
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

SQL> create table scott.table3( x number );

SQL> insert into scott.table3 values ( 10 );

1 row created.

– insert few rows in table3
SQL> /

..

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats(‘SYS’);

PL/SQL procedure successfully completed.

SQL> select table_name, timestamp from dba_tab_modifications where table_owner = ‘SCOTT’;
TABLE_NAME TIMESTAMP
—————————— ——————–
TABLE3 09-FEB-2010 22:23:10
TEST1 09-FEB-2010 22:17:46
TABLE2 09-FEB-2010 22:19:06

Information on DBA_TAB_MODIFICATIONS
SQL> desc dba_tab_modifications;
Name Null? Type
—————————————– ——– —————————-
TABLE_OWNER VARCHAR2(30) — table owner
TABLE_NAME VARCHAR2(30) — table name
PARTITION_NAME VARCHAR2(30) — partition name
SUBPARTITION_NAME VARCHAR2(30) — sub partition name
INSERTS NUMBER — number of inserts in the table
UPDATES NUMBER — number of updates in the table
DELETES NUMBER — number of deletes in table
TIMESTAMP DATE — timestamp when the table was
TRUNCATED VARCHAR2(3) — if table was truncated
DROP_SEGMENTS NUMBER — if segments were dropped

http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html

http://dioncho.wordpress.com/tag/copy_table_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 name
SQL> execute dbms_stats.create_stat_table(ownname= 'scott', stattab= 'backup_stats');

PL/SQL procedure successfully completed.

– procedure to export statistics, exports statistics scott.test into scott.backup_stats, cascade=>true means it will export index statistics too
SQL> exec dbms_stats.export_table_stats(ownname=>'scott', tabname=>'test', statown=>'scott', stattab=>'backup_stats', cascade=>true);

PL/SQL procedure successfully completed.

– import table stats
SQL> exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'test1', statown=>'scott', stattab=>'backup_stats', cascade=>true);

PL/SQL procedure successfully completed.

– drop statistics table
SQL> execute dbms_stats.drop_stat_table(ownname= 'scott', stattab= 'backup_stats');

PL/SQL procedure successfully completed.

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/
– shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…

Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE

To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

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 shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

– create table
SQL> create table test ( x number );

Table created.

– create index
SQL> create index test_idx on test(x);

Index created.

– shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
– lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.