set statistics
Set UPDATE STATISTICS execution control options
Syntax
set_stats:
SET {STATISTICS | STATS}
[ HISTOSIZE [=] num ]
[ SAMPLING [=] (pct_num, minrows_num, maxrows_num) ]
[ AUTORUN [=] (pct_num, minrows_num) ]
[ THREADS [=] num]
Description
The SET STATISTICS statement will set the control options to be used by the UPDATE STATISTICS statement.
The HISTOSIZE clause specifies the number of distinct values for each column whose distribution counts are to be kept. The default is 25. The minimum allowed value is 10 and the maximum allowed value is 100.
The SAMPLING clause specifies how much of the database is to be sampled and includes a minimum number of rows, maximum number of rows, and percent of table to be scanned (integer value between 0 and 100) of each table to be sampled. So, for example, a "sampling (10, 100, 10000)" means that 10% of the number of rows in each table will be sampled but at least 100 (if there are 100 rows) and no more than 10,000 rows must be sampled. The larger these values are, the more accurate will be the distribution statistics but UPDATE STATISTICS will take longer to run. If the SAMPLING clause is omitted the system default is (10, 1000, 10000). The minimum number of rows specified must be at least 100.
The AUTORUN clause specifies how often RDM SQL should automatically run an UPDATE STATISTICS. The pct_num (an integer value greater than or equal to 5) specifies a percentage change in the total number of rows stored in the database since the last update UPDATE STATISTICS execution. When a percentage change in size greater than pct_num occurs, an UPDATE STATISTICS will be automatically executed. The minrows_num is an integer value that specifies the minimum number of rows that must be contained in the database before an UPDATE STATISTICS will automatically be executed. The minimum allowed minrows_num value is 100 rows.
If the AUTORUN clause is not specified then the statistics will only be updated the next time an UPDATE STATISTICS statement is executed. If an AUTORUN was specified in a prior UPDATE STATISTICS, it can be disabled by omitting the AUTORUN clause.
Automatic UPDATE STATISTICS executions occur on the first database open after the specified AUTORUN conditions have occurred. It will block the application (database open call) until it completes. This means that when this occurs, the database open time delay may be quite noticeable.
The THREADS clause can be used to explicitly specify the number of threads to use during UPDATE STATISTICS execution. If not specified, the system will invoke one thread for each CPU/core available on the computer. Each thread collects the stats on a single table. Note that multi-threaded operation is only possible when the database has been opened in shared mode.
Example
open bookshop;
set stats histosize=35 sampling=(20,100,5000) autorun=(20,1000) threads=4;
update stats on bookshop;
select dbname, exectime, totrows, memreqd from sys$dbstats;
DBNAME EXECTIME TOTROWS MEMREQD
bookshop 0.06 2586 88743
select colname, entry, histo_eq_tot, histo_val from sys$colhisto
where colname = "publ_year";
COLNAME ENTRY HISTO_EQ_TOT HISTO_VAL
publ_year 1 1 1644
publ_year 2 1 1651
publ_year 3 1 1674
publ_year 4 1 1805
publ_year 5 1 1816
publ_year 6 1 1822
publ_year 7 1 1840
publ_year 8 1 1843
publ_year 9 2 1846
publ_year 10 3 1848
publ_year 11 2 1852
publ_year 12 1 1853
publ_year 13 1 1854
publ_year 14 2 1855
publ_year 15 3 1864
publ_year 16 2 1865
publ_year 17 2 1873
publ_year 18 1 1875
publ_year 19 1 1876
publ_year 20 2 1879
publ_year 21 1 1881
publ_year 22 1 1886
publ_year 23 1 1887
publ_year 24 1 1888
publ_year 25 1 1889
publ_year 26 3 1894
publ_year 27 2 1897
publ_year 28 2 1899
publ_year 29 3 1902
publ_year 30 3 1906
publ_year 31 3 1911
publ_year 32 1 1917
publ_year 33 1 1920
publ_year 34 2 1922
publ_year 35 1 1939
See Also