Skip to content
≡

Administrator

Developer

Tools

External Tools

5. The Optimizer and Statistics#

This chapter explores why statistics are important for query optimization and explains how the user can collect and set statistics.

Overview of Statistics#

Well-built statistics play an important role when the optimizer determines the most efficient execution plan. The Altibase server provides the following statistics for the query optimizer to determine the most efficient execution plan.

Table Statistics#

  • The number of records

  • The number of pages

  • The average length of records

Column Statistics#

  • The number of distinct values in columns

  • The number of NULLs in columns

  • The average length of columns

Index Statistics#

  • The average number of slots per leaf nodes

  • The depth from the index root to the leaf nodes

  • Clustering factor: The level to which data is sorted according to the index.

  • The maximum value of the index

  • The index minimum value

Database system statistics#

  • The average time spent reading a single page

  • The average time spent reading multiple pages

  • The average time spent hashing

  • The average time spent comparing


Managing Statistics#

Altibase can gather statistics both manually and automatically.

Stats Collection#

If the latest statistics of an object are needed, the user should collect statistics or directly update them through the DBMS Stats stored procedure, and the collected statistics would be efficiently utilized for the optimizer to establish an execution plan.

Altibase server gathers the statistics based upon sampling data if the following stored procedures are called. The statistics would be more accurate when the ratio of the sampling data is higher.

  • GATHER_SYSTEM_STATS

  • GATHER_DATABASE_STATS

  • GATHER_TABLE_STATS

  • GATHER_INDEX_STATS

The user can manually set statistics for objects or the system using the following system stored procedures.

  • SET_SYSTEM_STATS

  • SET_TABLE_STATS

  • SET_INDEX_STATS

  • SET_COLUMN_STATS

The user can manually lock or unlock the table statistics using the following system stored procedures.

  • LOCK_TABLE_STATS
  • UNLOCK_TABLE_STATS

For more detailed information about each stored procedure, please refer to DBMS Stats in the Stored Procedures Manual.


Auto Stats#

Inquiring Statistical Information#

The collected statistics are stored in the V$DBMS_STATS performance view. For more information on performance view, refer to the General Reference > Chapter 3. The Data Dictionary.

Restrictions#

For the system statistics, it is recommended to collect only once after starting the Altibase server with a certain amount of load (repeating FULL SCAN and INDEX SCAN). Otherwise, some statistical values may not be measured correctly.

Because objects and data in the database are constantly changing, users need to update statistics periodically to optimize queries. It is recommended to collect the table statistics monthly if when there are many data changes.