![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Fragment-level statistics
Fragment-level statistics affects the way UPDATE STATISTICS MEDIUM/HIGH gathers data and generates column distribution on fragmented tables. Statistics generated by UPDATE STATISTICS LOW on tables and indexes have always been maintained at fragment level. These statistics are stored in catalog sysfragments.
Fragment-level statistics offer a finer granularity of statistics for fragmented tables. The statistics are calculated and stored at the individual fragment level.
You may want to consider fragment-level statistics in the following two main situations.
- If you have a time-cyclic database schema where data changes are localized to rows with certain specific column values. For example, a sales table fragmented by transaction date column such that new rows are added each month to store current month sales data.
- You have fragmented tables and you frequently use ALTER FRAGMENT ATTACH/DETACH to add or remove data from the fragmented table. Breaking the column distribution per fragment allows for statistics management at the fragment level. For example, refreshing of statistics can happen at fragment level, and fragment statistics can be added to, or removed from, table statistics.
Setting your system to use fragment-level statistics
Do the following to allow the server to build and use fragment-level statistics.
- Set ONCONFIG parameter AUTO_STAT_MODE to 1. This is a default setting and affects the entire system. If you wish to restrict the feature to certain databases only, you can alternatively set the session environment AUTO_STAT_MODE to 1 in your sysdbopen procedure for the desired databases.
- Ensure the ONCONFIG parameter SYSSBSPACENAME is set to a valid
smartblob space and allocate the smartblob using the
onspaces -c -Scommand. For example:onspaces -c -S sbspace -p /work/dbspaces/sbspace -s 100000 -o 0
The amount of space required for a fragmented statistics of column depends on its datatype, number of fragments, and the resolution used in update statistics. The datatype of the column determines the actual number of bytes a single minibin requires. - Consider increasing logical logs to account for the additional fragment-level statistics rows in the sysfragdist catalog. Since the fragmented statistics are more granular than table statistics, they do take up more space as well.
With these steps, the database server can now identify the candidate tables that can benefit from fragment-level statistics and creates them AUTOMATICALLY when the regular update statistics is run. The candidate tables are identified by using the following set of rules.
- Table is fragmented by expression, LIST, or INTERVAL strategy.
- Table has over a million rows.
Alternatively, you can explicitly indicate which tables should have fragmented statistics by tweaking the table property STATLEVEL explained in the next section.
STATLEVEL is the level or granularity of the column distributions. STATLEVEL can be specified with CREATE TABLE and/or ALTER TABLE commands.
- TABLE - Distributions are created at table level.
- FRAGMENT - Distributions are created and maintained at each fragment.
- AUTO - In Automatic mode, apply rules to determine if fragment-level statistics should be created, otherwise default to table level distributions.
The following example forces the use of fragment-level statistics by setting the STATLEVEL at creation time.
Listing 7. Example of fragment-level statistics using STATLEVEL
CREATE TABLE tab1(col1 integer, col2 char(10))
FRAGMENT BY EXPRESSION
(col1 >= 0 AND col1 < 1000) IN dbspace1,
(col1 >= 1000 AND col1 < 2000) IN dbspace2,
(col1 >= 2000 AND col1 < 3000) IN dbspace3,
remainder in rootdbs
STATLEVEL FRAGMENT;
|
Note: The FRAGMENT option of STATLEVEL can be used for any fragmented table regardless of strategy used.
Alternatively, the following ALTER command can be used at a later time achieves the same effect.
ALTER TABLE tab1 STATLEVEL FRAGMENT; |
The new fragment-level statistics will be created and stored in the sysfragdist system catalog when UPDATE STATISTICS MEDIUM/HIGH is run on table tab1e.
When you perform an ALTER FRAGMENT ATTACH/DETACH operation on a fragmented table with fragment-level statistics, the server will automatically refresh column statistics of the tables involved. Refreshing of the statistics runs in the background and the ALTER FRAGMENT command returns immediately after the alter operation is completed. The refreshing of statistics either starts after the alter operation is completed or if you are in a transaction, statistics refresh starts after the transaction is committed.
- For ATTACH operation: fragmented statistics of the new fragment is built, and table-level statistics is rebuilt from all fragmented statistics. Any existing fragments with out-of-date column statistics will also be rebuilt at this time.
- For DETACH operation: table-level statistics of the resulting tables are rebuilt from the fragmented statistics.
Note: Due to smarter statistics, any good fragmented statistics will be directly used for merging into table-level statistics. This is not equivalent to running a complete statistics refresh on resulting tables of an ALTER FRAGMENT.
The background task that refreshes statistics called refresh_table_stats is defined in the ph_task table of the sysadmin database. If any errors are found, it will print errors in online.log.
