Can I Join The Military If I Have Ezcema

Optimizing statistical collection on partitioned tables


Oracle 10g uses a two pass algorithm to collect statistics on partitioned tables:

1. One pass over the entire table to update the global statistics. 2. A second pass to collect statistics on each of the partitions.

This approach has the disadvantage that if changes are made in a few partitions to make them eligible for automatic collection maintenance window, as well as update an own partition statistics in question, it must perform the global update of the table. For the latter it runs the entire table, including partitions did not change. This can be done very heavy depending on the size of the table.
From Oracle 11g adopting a one-pass algorithm, so that instead of making one pass around the table to update the global information, it performs an incremental update inferring changes from the modified partition. Some of statistics can be derived easily from the statistics of the partitions (eg the number of rows), but other statistics, such as the number of distinct values \u200b\u200bin a column no. To resolve this Oracle uses a new structure called a synopsis for each column at the level of the partition so that the number of distinct values \u200b\u200b(NDV) globally can be derived by merge of the synopsis of the partitions analyzed.







While this is a feature of 11g R1, Oracle 10g R2 10.2.0.4 more precisely on an option to simulate the incremental collection through a new value 'APPROX_GLOBAL AND PARTITION' for the parameter in the procedure GATHER_TABLE_STATS GRANULARITY. Their behavior is equal to 11g
except for the NDV of the column is not partitioned and the number of keys than the global index.

incremental maintenance is disabled by default and can be enabled at the table, schema, even at the level of the database.

Then I pass the results of my tests using Oracle 11g R1 (11.1.0.7):

I'll use a table partitioned by date range with 3 partitions. The table is small (about 5M rows) but serve to illustrate:


select partition_name,
num_rows from table_name WHERE user_tab_partitions = 'T';
partition_name
NUM_ROWS ---------- ------------------------------
P0810 2583379 P0710 1332466 P0910 1084155

PMAX 0


100.000
I will delete records in a partition:


delete from t partition (p0910) WHERE rownum

update statistics, using the default, ie without incremental collection:

begin
dbms_stats.gather_table_stats (ownname => user, tabname => 'T');
end;
Procedure PL / SQL completed successfully. Elapsed: 00:00:11.71




took almost 12 seconds.
select dbms_stats.get_prefs ('INCREMENTAL', tabname => 'T') from dual; FALSE

With

above query was verified that the conventional collection

Now I will turn the collection on table T and I delete rows and I will return to collect the statistics:
begin
dbms_stats.set_table_prefs (ownname => user, tabname => 'T',
pname => 'INCREMENTAL', pvalue => 'TRUE' )
end;


actually verified that the incremental mode is activated on the table T:


select dbms_stats.get_prefs ('INCREMENTAL', tabname => 'T') from dual;
TRUE


begin dbms_stats.gather_table_stats (ownname = > user, tabname => 'T'); end;

Elapsed: 00:00:04.71


Now

took 4s. Instead of going through the whole table looked just changed the partition and then derived the global statistics based on the changes and using the synapses of the partition.

must take into account the global histograms are not preserved after run incremental collection (see Bug 8686932 in Metalink).

While this method used by Oracle to make more effective the collection has been studied in academic and laboratory time ago, is Oracle's first relational database engine to implement it.