Brief introduction of Oracle statistical information Automatic collection mechanism

Statistics are critical to Oracle databases, especially when using the CBO (cost-based optimizer) pattern, statistics include the use of blocks, free blocks, average length of the table, and statistical information collection time. In the Oracle9i database, two optimizer modes Rbo (rule-based optimizer) and CBO (cost-based optimizer) coexist, by default, the value of the Optimizer_mode parameter is choose,choose not the optimizer pattern, it says, In the analysis of the statements in the database, if there is statistical information on the object, is to use the CBO method to generate the execution plan, if there is no statistical information on the object, use the Rbo method.

Overall, the CBO is more accurate than RBO, but it requires statistical information and statistics to be precise, or Oracle may make false judgments. So, in the Oracle9i database, we're going to plan what collection strategy we're using at what time to collect statistics. In any case, the collection of oracle9i statistics is done in an artificial way.

To the oracle10g database, by default, Optimizer_mode=all_rows, that is, the CBO approach, in order to ensure the accuracy of the implementation plan, from Monday to Friday, from the night of 22, to the next morning 6 points, through a job gather_ Stats_job automatically collects the statistics of objects, not all of them, but collects objects and statistics that have no statistical information over old objects.

How does Oracle determine if an object has been manipulated, and how much data is being manipulated?

Let's look at the following example

Sql> Conn Scott/tiger

Connected.

Sql>

Sql>

Sql> select * from tab;

Tname Tabtype Clusterid

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

BONUS TABLE

Salgrade TABLE

T TABLE

Sql> desc T

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

EMPNO VARCHAR2 (10)

Ename VARCHAR2 (10)

SAL Number (7,2)

Oracle introduced the User_tab_modifications data dictionary to record object modifications, but not that the changes would be written to the data dictionary immediately, but in memory

Sql> Select COUNT (*) from user_tab_modifications;

COUNT (*)

----------

0

Sql> Update T set sal=1000 where empno=7369;

1 row updated.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from user_tab_modifications;

COUNT (*)

----------

0

Sql> Conn System/oracle

Connected.

Let Oracle flush the memory operation to the data dictionary table by executing the following statement user_tab_modifications

Sql> exec dbms_stats. Flush_database_monitoring_info;

Pl/sql procedure successfully completed.

Sql> Conn Scott/tiger

Connected.

Sql> Select COUNT (*) from user_tab_modifications;

COUNT (*)

----------

1

sql> desc user_tab_modifications;

Name Null? Type

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

----------------------------------------------------- -------- ------------------------------------

TABLE_NAME VARCHAR2 (30)

Partition_name VARCHAR2 (30)

Subpartition_name VARCHAR2 (30)

Inserts number

UPDATES number

Deletes number

TIMESTAMP DATE

Truncated VARCHAR2 (3)

Drop_segments number

Sql> COL table_name for A10

Sql> Col partition_name noprint

Sql> Col subpartition_name noprint

Sql> select * from User_tab_modifications;

TABLE_NAME Inserts UPDATES deletes TIMESTAMP TRU

---------- ---------- ---------- ---------- --------- --- -------------

T 0 1 0 05-oct-10 NO 0

Sql>

For us, you can use the Oracle Default collection method, of course, you can also decide what time, how to collect.

Collection is done through command analyze, or package dbms_stats,oracle recommended for use with package dbms_stats.