Principles and experiments of Oracle automatic statistics collection

Principles and experiments of Oracle automatic statistics collection

Starting from Oracle Database 10 Gb, Oracle creates a scheduled task named GATHER_STATS_JOB by default after the Database is created, which is used to automatically collect CBO statistics. This automatic task is enabled by default between-on the workday and all day on the weekend.

Call DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to collect statistics. This process first detects the missing and obsolete objects of statistics. Then, determine the priority and start the statistics. The collection rule is that the number of Operation Records in user_tab_modifications exceeds 10% of the number of tables. For more information, see the official documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#sthref1068.

The user_tab_modifications table contains inserts, updates, and deletes, which indicates how many rows of data have been modified. Here is an experiment:

SQL> create table test as select * from dba_objects;

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
Unselected row

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;
The PL/SQL process is successfully completed.

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
Unselected row

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;
The PL/SQL process is successfully completed.

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
Unselected row

SQL> exec dbms_stats.gather_table_stats (user, 'test ');
The PL/SQL process is successfully completed.

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
Unselected row

SQL> delete from test where rownum <= 10000;
10000 rows have been deleted.
SQL> commit;
Submitted.

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
Unselected row

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;
The PL/SQL process is successfully completed.

SQL> select table_name, inserts, updates, deletes, timestamp
From user_tab_modifications where table_name = 'test ';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
--------------------------------------------------------------------------
TEST 0 0 months 12-7 months-13

In addition:

Disable the automatic statistics command, exec DBMS_SCHEDULER.DISABLE ('gather _ STATS_JOB ');

Enable the automatic statistics command, exec DBMS_SCHEDULER.ENABLE ('gather _ STATS_JOB ');

Check whether automatic statistics are enabled,

Oracle 10g: select owner, JOB_NAME, enabled from DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'gather _ STATS_JOB ';
Oracle 11g: select t1.owner, t1.job _ name, t1.enabled from dba_scheduler_jobs t1 where t1.job _ name = 'bsln _ MAINTAIN_STATS_JOB ';

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031