Document TypeㅣTechnical Information
FieldㅣManagement/Configuration
Applicable Product Versionㅣ7FS02PS
Document NumberㅣTADTI177
Overview
The database stores statistical information in the Data Dictionary, and the collected statistics can be viewed through Dictionary Views. Tibero extensively uses statistical information about Tables and Indexes stored in the Data Dictionary. Since objects continuously change, periodic updates of statistical information are necessary.
Tibero provides the DBMS_STATS package to facilitate the generation of statistical information. Using various procedure calls in this package, you can collect and manage (e.g., delete or initialize) statistical information about columns, Tables, Data Dictionary (DD), Indexes, Schemas, Systems, and more.
Note
For detailed information on the various procedures provided by the DBMS_STATS package, refer to "Tibero tbPSM Reference Guide" under "DBMS_STATS".
Method
1. DBMS_STATS Package
The DBMS_STATS package provides the following functionalities.
- Collect (Gather) object statistics and optionally save current statistics into a backup table before overwriting
- Lock or unlock statistics stored in the Data Dictionary
- Copy object statistics from one partition or subpartition to another
- Restore object statistics to the Data Dictionary
- Delete object statistics stored in the Data Dictionary or backup tables
- Export object statistics from the Data Dictionary to backup tables
- Import object statistics from backup tables to the Data Dictionary
- Get object statistics stored in the Data Dictionary or backup tables
- Set object statistics stored in the Data Dictionary or backup tables
| Function | Database | Dictionary | Schema | Table | Index | Column |
|---|---|---|---|---|---|---|
| Gather/Delete | O | O | O | O | O | X / O |
| Lock/Unlock | O | O | ||||
| Copy | O | |||||
| Restore | O | O | ||||
| Export/Import | O | O | O | O | O | O |
| Get/Set | O | O | O |
Key items related to statistics collection are as follows.
- DBMS_STATS.GATHER_DATABASE_STATS: Collect statistics for all objects in the database
- DBMS_STATS.GATHER_DICTIONARY_STATS: Collect statistics for schema objects (SYS, SYSCAT)
- DBMS_STATS.GATHER_INDEX_STATS: Collect statistics for indexes
- DBMS_STATS.GATHER_SCHEMA_STATS: Collect statistics for all objects in a schema
- DBMS_STATS.GATHER_SYSTEM_STATS: Collect system statistics
- DBMS_STATS.GATHER_TABLE_STATS: Collect statistics for tables
Note
For detailed information on the various procedures provided by the DBMS_STATS package, refer to "Tibero tbPSM Reference Guide" under "DBMS_STATS".
- ALTER_STATS_HISTORY_RETENTION: Change the retention period for statistics history. See “PURGE_STATS”
- COPY_TABLE_STATS: Copy source partition statistics of tables and local indexes to target partition statistics
- CREATE_STAT_TABLE: Create a statistics table to store statistics information
- DELETE_COLUMN_STATS: Delete statistics for a column
- DELETE_DATABASE_STATS: Delete statistics for all tables in the database
- DELETE_DICTIONARY_STATS: Delete statistics for all data dictionary schemas (SYS, SYSCAT)
- DELETE_INDEX_STATS: Delete statistics for indexes
- DELETE_SCHEMA_STATS: Delete statistics for an entire schema
- DELETE_SYSTEM_STATS: Delete workload system statistics and initialize system statistics
- DELETE_TABLE_STATS: Delete statistics for tables
- DROP_STAT_TABLE: Drop a statistics table
- EXPORT_COLUMN_STATS: Save statistics for specified columns to the statistics table
- EXPORT_DATABASE_STATS: Save statistics for tables in the database to the statistics table
- EXPORT_INDEX_STATS: Save statistics for specified indexes to the statistics table
- EXPORT_SCHEMA_STATS: Save statistics for an entire schema to the statistics table
- EXPORT_SYSTEM_STATS: Save system statistics to the statistics table
- EXPORT_TABLE_STATS: Save statistics for tables to the statistics table
- GET_COLUMN_STATS: Retrieve statistics for specified columns from DD (Data Dictionary) or statistics table
- GET_INDEX_STATS: Retrieve statistics for specified indexes from DD or statistics table
- GET_TABLE_STATS: Retrieve statistics for specified tables from DD or statistics table
- IMPORT_COLUMN_STATS: Import statistics for specified columns from statistics table to data dictionary
- IMPORT_DATABASE_STATS: Import statistics for all tables from statistics table to data dictionary
- IMPORT_INDEX_STATS: Import statistics for specified indexes from statistics table to data dictionary
- IMPORT_SCHEMA_STATS: Import statistics for an entire schema from statistics table to data dictionary
- IMPORT_SYSTEM_STATS: Import system statistics from statistics table to data dictionary
- IMPORT_TABLE_STATS: Import statistics for tables from statistics table to data dictionary
- LOCK_TABLE_STATS: Lock statistics for the specified table and its associated indexes, partitions, and columns
- LOCK_SCHEMA_STATS: Lock statistics for tables, indexes, partitions, and columns belonging to the specified schema
- SET_COLUMN_STATS: Modify statistics for specified columns in DD or statistics table
- SET_INDEX_STATS: Modify arbitrary statistics for specified indexes in DD or statistics table
- SET_TABLE_STATS: Modify arbitrary statistics for specified tables in DD or statistics table
- PURGE_STATS: Remove statistics history older than the specified time
- RESTORE_SCHEMA_STATS: Restore statistics for all objects in a schema to a specified timestamp (as_of_timestamp)
- RESTORE_TABLE_STATS: Restore statistics for a table to a specified timestamp (as_of_timestamp)
- SET_PARAM: Change default parameter values of the DBMS_STATS package
- SET_SYSTEM_STATS: Manually set system statistics values
- UNLOCK_TABLE_STATS: Unlock statistics for the specified table and its associated indexes, partitions, and columns
- UNLOCK_SCHEMA_STATS: Unlock statistics for tables, indexes, partitions, and columns belonging to the specified schema
1.2 Configuration Items
Parameters configurable per DBMS_STATS are as follows.
| Parameter | Database | Dictionary | Schema | Table | Index |
|---|---|---|---|---|---|
| ownname | O | O | O | ||
| tablename | O | ||||
| idxname | O | ||||
| partname | O | O | |||
| estimate_pecent | O | O | O | O | O |
| block_sample | O | O | O | O | |
| method_opt | O | O | O | O | |
| degree | O | O | O | O | O |
| cascade | O | O | O | O | |
| gather_sys | O | ||||
| no_validate | O | O | O | O | O |
| options | O | ||||
| granularity | O | O | O | O | O |
| force | O | O | O | O | O |
The following shows how to change parameter settings using DBMS_STATS.SET_PARAM.
call dbms_stats.set_param('STAT_ESTIMATE_PERCENT',0) ;
call dbms_stats.set_param('STAT_METHOD_OPT','FOR ALL COLUMNS SIZE 1') ;
call dbms_stats.set_param('STAT_DEGREE',1) ;
call dbms_stats.set_param('STAT_NO_INVALIDATE','TRUE') ;The following shows how to check parameter values using DBMS_STATS.GET_PARAM.
ESTIMATE_PERCENT : select dbms_stats.get_param ('STAT_ESTIMATE_PERCENT') from dual;
METHOD_OPT : select dbms_stats.get_param ('STAT_METHOD_OPT') from dual;
DEGREE : select dbms_stats.get_param('STAT_DEGREE') from dual;
NO_INVALIDATE : select dbms_stats.get_param ('STAT_NO_INVALIDATE') from dual;Below is an explanation of options used when collecting statistical information.
OWNNAME
Specifies the name of the schema to process. This parameter is mandatory, and setting it to USER refers to the current session user.
CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',estimate_percent=100);
TABNAME
Specifies the name of the table to process.
IDXNAME
Specifies the name of the index to process.
PARTNAME
Specifies the name of the partition or subpartition to process. If not specified, object statistics for all partitions and subpartitions are collected according to the granularity parameter (see granularity section). (Default: NULL)
ESTIMATE_PERCENT
Determines the percentage of data to sample. (Default: get_param ('STAT_ESTIMATE_PERCENT'), valid range: 0.000001 ~ 100)
Used in GATHER procedures such as GATHER_TABLE_STATS, GATHER_SCHEMA_STATS. Increasing the sampling rate improves statistics accuracy but may increase collection time.
If set to 0, the optimizer decides an appropriate sampling rate based on the table size. The following explains sampling rates based on table row count.
| Table Row Count | Sampling % |
|---|---|
| Less than 10,000 | 100% |
| Less than 1,000,000 | 10% |
| 1,000,000 or more | 100,000 / Number of Rows x 100 (%) |
Sampling is provided for collection performance. If operating a large-scale system, adjusting the Granularity value should also be considered.
BLOCK_SAMPLE
Determines whether to use block sampling. It is faster than row sampling but less accurate. (Default: FALSE)
METHOD_OPT
Determines the scope of statistics and histogram collection. (Default: get_param ('STAT_METHOD_OPT'))
- FOR ALL
Collects statistics for all columns.
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
| Item | Description |
|---|---|
| INDEXED | Collect statistics for indexed columns |
| HIDDEN | Collect statistics for columns created by functional indexes, not actual columns |
| size_clause | Required when setting the length for histogram creation |
- FOR ALL INDEXED COLUMNS
This option is generally recommended. It collects statistics only for indexed columns, which usually dominate selectivity, so it is not a significant issue in most cases.
- FOR COLUMNS
Collect statistics for specific columns.
FOR COLUMNS [size_clause] column [size_clause] [,column [size_clause]...]
| Item | Description |
|---|---|
| size_clause | Required when setting the length for histogram creation |
- SIZE CLAUSE
Select whether to generate column histograms.
SIZE_CLAUSE := SIZE {integer | REPEAT | AUTO}| Item | Description |
|---|---|
| integer | Number of histogram buckets (valid range: 1 ~ 256) |
| REPEAT | Collect histogram information again only for columns that already have histograms |
| AUTO | System automatically decides which columns to collect histograms for, considering data distribution and workload |
Note
For detailed information on histograms, refer to “3.1. Histogram”.
DEGREE
Sets the number of parallel query processes. (Default: get_param('STAT_DEGREE'))
degree [value]
If the value is NULL, the default value of the table is used when creating or altering the table.
CASCADE ('CASCADE_INDEXES' can also be used)
Collect statistics for indexes as well.
| Item | Description |
|---|---|
| TRUE | Collect statistics for indexes related to the target table |
| FALSE | Do not collect statistics for indexes related to the target table |
GATHER_SYS
Specifies whether to process the SYS schema. (Default: FALSE)
NO_INVALIDATE
When updating statistics during operation, invalidating cursors can cause excessive parsing issues. The no_invalidate option controls this. (Default: get_param('STAT_NO_INVALIDATE'))
no_invalidate [FALSE | TRUE]
| Item | Description |
|---|---|
| TRUE | Do not invalidate related SQL cursors after statistics update (does not delete related physical plans). The updated statistics are used when SQL cursors age out from the shared pool (library cache) and reload. If you want to use updated statistics immediately without waiting for age-out, execute the following DDL to clear the PP cache: ALTER SYSTEM FLUSH PPC; (Flushes only the PP cache in the shared pool) |
| FALSE | Immediately invalidate related SQL cursors after statistics update |
OPTIONS
Specifies the name of the index to process. This parameter is mandatory. It determines the basis for tables to collect. (Default: GATHER)
| Item | Description |
|---|---|
| GATHER | Collect statistics for all tables |
| GATHER AUTO | Collect statistics only for tables that need collection based on internal criteria |
| GATHER EMPTY | Collect statistics only for tables without statistics |
GRANULARITY
Statistics are categorized at the Table, Index, Table Partition, Index Partition, and column levels. Granularity determines which level of statistics to collect. Adjusting granularity is essential when operating a large-scale system.
granularity VARCHAR2 DEFAULT '[ ALL | GLOBAL | PARTITION | GLOBAL AND PARTITION | APPROX_GLOBAL AND PARTITION | SUBPARTITION]'
| Item | Description |
|---|---|
| ALL | Collect statistics for Table, Index, Table Partition, Index Partition, Table Subpartition, Index Subpartition Columns |
| GLOBAL | Collect only Table-level statistics such as Table, Index, and columns. Does not collect partition or subpartition statistics. |
| PARTITION | Collect partition-level statistics such as Table Partitions and Subpartitions. Collecting statistics only for new partitions also updates min, max, distinct count, and histograms for partition key columns. |
| GLOBAL AND PARTITION | Collect both GLOBAL and PARTITION statistics (performs an additional query for GLOBAL statistics to accurately collect NDV information at the table level) |
| APPROX_GLOBAL AND PARTITION | Collect PARTITION statistics, and aggregate GLOBAL information from partition-level statistics. Does not collect column statistics. |
| SUBPARTITION | Collect subpartition statistics |
FORCE
If set to TRUE, statistics are collected even if the table’s statistics are locked.