Oracle 11g Enhancements to statistical information

In 11g, Oracle has made further enhancements in statistics.

This article introduces pending statistics.

In 10g, Oracle provides a way to restore statistics, which causes problems that can be solved quickly by restoring the original statistics.

In 11g, Oracle is a step further, and the statistics that the user collects can be set to pending until the statistics are validated and then published into the data dictionary.

Sql> SELECT * from V$version;

BANNER

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

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

Sql> SELECT dbms_stats. Get_prefs (' PUBLISH ') from DUAL;

Dbms_stats. Get_prefs (' PUBLISH ')

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

TRUE

This query checks whether the collected statistics are effective immediately, and by default the statistics collected with the Dbms_stats package are immediately updated to the data dictionary and take effect on the optimizer.

Sql> CREATE TABLE T (ID number);

Table has been created.

Sql> INSERT into T SELECT rownum from TAB;

24 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

The PL/SQL process has completed successfully.

Sql> ALTER session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

The session has changed.

Sql> SELECT table_name, num_rows, BLOCKS, last_analyzed

2 from User_tables

3 WHERE table_name = ' T ';

TABLE_NAME Num_rows BLOCKS last_analyzed

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

T 24 5 2010-10-28 08:04:50

By default, if you modify the global settings, the pending method takes effect:

Sql> EXEC dbms_stats. Set_table_prefs (USER, ' T ', ' PUBLISH ', ' FALSE ')

The PL/SQL process has completed successfully.

Sql> DELETE T;

24 rows have been deleted.

Sql> INSERT into T-SELECT rownum from All_objects;

69442 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T ')

The PL/SQL process has completed successfully.

Sql> SELECT table_name, num_rows, BLOCKS, last_analyzed

2 from User_tables

3 WHERE table_name = ' T ';

TABLE_NAME Num_rows BLOCKS last_analyzed

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

T 24 5 2010-10-28 08:04:50

As you can see, the newly collected statistics do not overwrite the original statistics in the data dictionary because the global properties of table T are modified, and the statistics collected at this time are not published immediately, but as to the PENDING state, you can query the data dictionary User_tab_pending_ Stats get pending statistics:

Sql> SELECT table_name, num_rows, BLOCKS, last_analyzed

2 from User_tab_pending_stats

3 WHERE table_name = ' T ';

TABLE_NAME Num_rows BLOCKS last_analyzed

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