Managing Volatile Object Statistics

Volatile objects are objects that can drastically change in size over the
course of a day. For example, tables that are the target of a bulk-load operation (where the
number of rows increases by 10 percent or more) would be considered volatile. Also, tables
that are truncated or dropped, and then rebuilt, would also be considered volatile. Volatile
objects run the risk of having no statistics (if they were dropped and rebuilt) or having inaccurate
statistics.

As part of Oracle’s query optimization, any table with no statistics will have them generated dynamically via the dynamic sampling feature. This “just-in-time” statistics generation ensures that no query will be executed without statistics. The parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher to enable this feature.


SQL> select LAST_ANALYZED,avg_row_len from dba_tables where table_name='T1' and owner='HR';

LAST_ANAL AVG_ROW_LEN
--------- -----------
14-APR-08 3

SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
14-APR-08 3 1

--To set statistics for a table to NULL, delete and lock the statistics as shown:
SQL> exec dbms_stats.delete_table_stats('HR','T1');

PL/SQL procedure successfully completed.

SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------

--The second option is to set statistics to values that are typical for the table and lock them. To
--achieve this, gather statistics when the table is at a typical size. When complete, lock the table’s
--statistics, as shown in the preceding example.
SQL> exec dbms_stats.lock_table_stats('HR','T1');

PL/SQL procedure successfully completed.

SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------


SQL> insert into hr.t1 values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table hr.t1 compute statistics;
analyze table hr.t1 compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked


SQL> exec dbms_stats.unlock_table_stats('HR','T1');

PL/SQL procedure successfully completed.

SQL> analyze table hr.t1 compute statistics;

Table analyzed.

SQL> select LAST_ANALYZED,avg_row_len,num_rows from dba_tables where table_name='T1' and owner='HR';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
18-APR-08 6 2