Overview

Version

The monitoring query introduced on this page can be used from the version that reflects BUG-31372.

• Altibase HDB version 5.3.3.33
• Altibase HDB version 5.3.5.15
• Altibase HDB version 5.5.1.0.3
• Altibase HDB version 6.1.1
• Altibase HDB version 6.3.1

To check the usage of disk tables and indexes

• ALTER TABLE table_name AGING and ALTER INDEX index_name AGING commands must be executed to get the correct usage.
• Unaging space is calculated as used, so if there are frequent deletes on a table, it may be calculated larger than the actual usage if aging is not performed.
• During the execution of the ALTER TABLE table_name AGING, ALTER INDEX index_name AGING command, the table is full-scanned while holding X LOCK on the table, so other operations on the table and indexes are waiting.
• TOTAL_USED_SIZE of v$segment is volatile temporary data. When the Altibase server is restarted, it is initialized to the total allocation size of the table and index, not the actual usage amount.
• v$segment query itself does not affect the database.

Disk table

• Even if the table data is deleted with DELETE and USED, it does not decrease.
• To check the actual USED excluding FREE PAGE after DELETE and table_name AGING; must be executed.

• While executing ALTER TABLE ~ AGINING;, the table is locked, so other requests for the table are put in waiting for state.

  ALTIBASE HDB 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk table usage query
  Example of output

Disk Index

• Even if the table data is deleted with DELETE, the USED of the index does not decrease.
• To check the actual USED except FREE PAGE after DELETE, ALTER INDEX index_name AGING; must be executed.

• When executing ALTER INDEX ~ AGINING, the table is locked. Therefore, other requests for the table are put in a waiting state, so be cautious when executing the ALTER INDEX ~ AGAING.

  ALTIBASE HDB 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk index usage query
  Example of output

Reference - How to check the count of disk table and index

set linesize 1024;
set colsize 50;
SELECT DECODE(T.IS_PARTITIONED, 'T', 'PARTITIONED     TABLE CNT : '||PART_T.CNT, 'F', 'NON-PARTITIONED TABLE CNT : '||T.CNT) TABLE_COUNT
  FROM (SELECT IS_PARTITIONED
             , COUNT(*) CNT
          FROM V$DISKTBL_INFO D
             , SYSTEM_.SYS_TABLES_ T
         WHERE D.TABLE_OID = T.TABLE_OID
         GROUP BY IS_PARTITIONED) T
     , (SELECT COUNT(*) CNT FROM SYSTEM_.SYS_TABLE_PARTITIONS_ ) PART_T ;