Disk table and index usage#
Overview#
This document summarizes disk table and index usage retrieve queries by version.
The user can use the following two methods for disk table usage. This page provides queries using v$segment(x$segment).
- v$segment: A method of calculating physical pages while full-scanning a table
- v$usage: A sampling method using statistical information
Altibase 4.3.9.x#
This is an Altibase version 4.3.9 of the disk table and index usage query.
This query can also be used in Altibase version 5.1.1.
Disk table usage query#
Altibase 4.3.9 Disk Table Usage Query
set linesize 1024;
set colsize 20;
SELECT U.USER_NAME 'USER_NAME' -- Database user
, TBL.TABLE_NAME 'TABLE_NAME' -- Table name
, TBS.NAME 'TBS_NAME' -- Name of the tablespace to which the table belongs
, TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024 , '999,999,999') 'TBS_MAX(KB)' -- Maximum size of tablespace
, TO_CHAR((TBS.A_EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)' -- The total size allocated from the table
, TO_CHAR((TBS.A_EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_FULL_COUNT)/1024, '999,999,999') 'USED(KB)' -- Actual usage of the table (data usage)
, TO_CHAR((((TBS.A_EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/(TBS.TOTAL_PAGE_COUNT TBS.PAGE_SIZE))100), '99.9') 'USAGE(%)' -- Utilization based on the maximum size of the tablespace
FROM X$SEGMENT SEG
, SYSTEM_.SYS_TABLES_ TBL
, V$TABLESPACES TBS
, SYSTEM_.SYS_USERS_ U
WHERE SEG.TABLE_OID = TBL.TABLE_OID
AND SEG.SPACE_ID = TBL.TBS_ID
AND SEG.SPACE_ID = TBS.ID
AND TBL.USER_ID = U.USER_ID
AND TBL.TABLE_TYPE = 'T'
AND SEG.SEGMENT_TYPE = 6
ORDER BY USER_NAME, TABLE_NAME
;
Example of output
USER_NAME TABLE_NAME TBS_NAME TBS_MAX(KB) ALLOC(KB) USED(KB) USAGE(%)
----------------------------------------------------------------------------------------------------------------------------------------------
SYS CUSTOMER USER_DATA 2,097,152 256 0 0.0
SYS DEPARTMENT SYS_TBS_DATA 254,976 256 0 .1
SYS DISK_T USER_DATA 2,097,152 851,200 850,944 40.6
SYS EMPLOYEE SYS_TBS_DATA 254,976 256 0 .1
SYS ORDERS USER_DATA 2,097,152 256 0 0.0
5 rows selected.
Disk index usage query#
Altibase 4.3.9 Disk Index Usage Query
set linesize 1024;
set colsize 20;
SELECT U.USER_NAME AS 'USER_NAME' -- Database user
, TBL.TABLE_NAME AS 'TABLE_NAME' -- Table name
, IDX.INDEX_NAME AS 'INDEX_NAME' -- Index name
, TBS.NAME AS 'TBS_NAME' -- Tablespace name to which the index belongs
, TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024 , '999,999,999') AS 'TBS_MAX(KB)' -- Maximum size of table space
, TO_CHAR((TBS.A_EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') AS 'ALLOC(KB)' -- Total size allocated from the index
, TO_CHAR((TBS.A_EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_FULL_COUNT )/1024, '999,999,999') AS 'USED(KB)' -- Actual usage of the index (data usage)
, TO_CHAR((((TBS.A_EXTENT_PAGE_COUNT SEG.EXTENT_TOTAL_COUNT)/TBS.TOTAL_PAGE_COUNT)100), '99.9') AS 'USAGE(%)' -- Utilization based on the maximum size of the tablespace
FROM X$SEGMENT SEG
, V$INDEX I
, SYSTEM_.SYS_INDICES_ IDX
, SYSTEM_.SYS_TABLES_ TBL
, V$TABLESPACES TBS
, SYSTEM_.SYS_USERS_ U
WHERE SEG.TABLE_OID = I.TABLE_OID
AND SEG.SEGMENT_DESC = I.INDEX_SEG_DESC
AND I.INDEX_ID = IDX.INDEX_ID
AND IDX.TABLE_ID = TBL.TABLE_ID
AND SEG.SPACE_ID = IDX.TBS_ID
AND SEG.SPACE_ID = TBS.ID
AND IDX.USER_ID = U.USER_ID
AND TBL.TABLE_TYPE = 'T'
AND SEG.SEGMENT_TYPE = 5
ORDER BY U.USER_NAME, TBL.TABLE_NAME, IDX.INDEX_NAME
;
Example of output
USER_NAME TABLE_NAME INDEX_NAME TBS_NAME TBS_MAX(KB) ALLOC(KB) USED(KB) USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS CUSTOMER __SYS_IDX_ID_136 USER_DATA 2,097,152 256 0 0.0
SYS DEPARTMENT DEP_IDX1 USER_IDX 2,097,152 256 0 0.0
SYS DEPARTMENT __SYS_IDX_ID_134 SYS_TBS_DATA 254,976 256 0 .1
SYS DISK_T DISK_T_IDX_01 USER_IDX 2,097,152 159,744 159,488 7.6
SYS EMPLOYEE EMP_IDX1 USER_IDX 2,097,152 256 0 0.0
SYS EMPLOYEE __SYS_IDX_ID_135 SYS_TBS_DATA 254,976 256 0 .1
SYS ORDERS ODR_IDX1 USER_IDX 2,097,152 256 0 0.0
SYS ORDERS __SYS_IDX_ID_137 USER_DATA 2,097,152 256 0 0.0
8 rows selected.
Reference - How to check the number of disk tables and indexes#
Disk Table count query
set linesize 1024;
set colsize 30;
SELECT 'TABLE CNT : '||COUNT(*) TABLE_COUNT
FROM V$DISKTBL_INFO D
, SYSTEM_.SYS_TABLES_ T
WHERE D.TABLE_OID = T.TABLE_OID ;
Disk Index count query
set linesize 1024;
set colsize 30;
SELECT 'INDEX CNT : '||COUNT(*) INDEX_COUNT
FROM SYSTEM_.SYS_INDICES_
WHERE TABLE_ID IN (SELECT TABLE_ID
FROM SYSTEM_.SYS_TABLES_ T
, V$DISKTBL_INFO D
WHERE T.TABLE_OID = D.TABLE_OID);
Altibase 5.1.5.x#
- This is a disk table and index usage query for Altibase version 5.1.5.
- In Altibase 5, due to a change in the structure of the disk table, only the size allocated to the table can be known and the actual usage cannot be checked.
- This means that data cannot be calculated except for the free space that occurred after DELETE.
- From Altibase 5.3.3.33, 5.3.5.15, 5.5.1.0.3, it has been improved so that the usage can be checked.
Disk table#
Altibase 5.1.5 Disk Table Usage Query
set linesize 1024;
set colsize 30;
SELECT U.USER_NAME USER_NAME -- Database user
, DECODE(TBL.IS_PARTITIONED, 'T', 'PARTITIONED', 'F', 'NON-PARTITIONED') PARTITIONED -- If a partitioned table, PARTITIONED a non-partitioned, then NON-PARTITIONED
, TBL.TABLE_NAME TABLE_NAME -- Table name
, DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE -- Partitioned table name
, TBS.NAME TABLESPACE_NAME -- Tablespace
, TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)' -- Maximum size of the tablespace to which the table belongs
, TO_CHAR((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)' -- Total size allocated to date
, TO_CHAR((((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)' -- Percentage of utilization compared to the maximum size of the tablespace
FROM (SELECT TBL.USER_ID
, DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
, TBL.TABLE_NAME
, PT.PARTITION_NAME
, DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID
, TBL.IS_PARTITIONED
FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID
) TBL
, V$SEGMENT SEG
, SYSTEM_.SYS_USERS_ U
, V$TABLESPACES TBS
, (SELECT SPACEID
, SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
, DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
FROM V$DATAFILES
GROUP BY SPACEID) D
WHERE 1=1
AND SEG.SEGMENT_TYPE = 'TABLE' / 'TABLE' :, 'INDEX' : /
AND SEG.TABLE_OID = TBL.TABLE_OID
AND U.USER_ID = TBL.USER_ID
AND D.SPACEID = TBL.TBS_ID
AND TBS.ID = TBL.TBS_ID
ORDER BY USER_NAME, PARTITIONED, TABLE_NAME, PARTITIONED_TABLE
;
Example of output
USER_NAME PARTITIONED TABLESPACE_NAME TABLE_NAME PARTITIONED_TABLE MAX(KB) ALLOC(KB) USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS NON-PARTITIONED SYS_TBS_DISK_DATA CUSTOMER - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA DEPARTMENT - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA EMPLOYEE - 2,097,152 256 .01
SYS NON-PARTITIONED SYS_TBS_DISK_DATA GOODS - 2,097,152 256 .01
SYS NON-PARTITIONED USER_DATA ORDERS - 2,097,152 256 .01
SYS PARTITIONED PART_DATA PART_T1 P1 262,144 512 .20
SYS PARTITIONED PART_DATA PART_T1 P2 262,144 512 .20
SYS PARTITIONED PART_DATA PART_T1 P3 262,144 18,176 6.93
SYS PARTITIONED PART_DATA PART_T2 P201406 262,144 9,472 3.61
SYS PARTITIONED PART_DATA PART_T2 P201407 262,144 18,944 7.23
SYS PARTITIONED PART_DATA PART_T2 P201408 262,144 4,864 1.86
SYS PARTITIONED PART_DATA PART_T2 P201512 262,144 7,168 2.73
SYS PARTITIONED PART_DATA_DEF PART_T2 PMAX 262,144 8,192 3.13
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 DEF 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q1_2014 2,097,152 1,536 .07
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q2_2014 2,097,152 768 .04
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q3_2014 2,097,152 2,304 .11
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE2 Q4_2014 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES DEF 2,097,152 256 .01
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q1_2014 2,097,152 768 .04
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q2_2014 2,097,152 1,536 .07
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q3_2014 2,097,152 1,280 .06
SYS PARTITIONED SYS_TBS_DISK_DATA RANGE_SALES Q4_2014 2,097,152 768 .04
23 rows selected.
Disk Index#
set linesize 1024;
set colsize 20;
SELECT U.USER_NAME USER_NAME -- Database user
, I_LIST.TABLE_NAME -- Table name
, DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME -- Partitioned table name. If a non-partitioned, then NON-PARTITIONED
, I_LIST.INDEX_NAME INDEX_NAME -- Index name
, DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX -- Partitioned index name
, TBS.NAME TBS_NAME -- Tablespace to which the index belongs
, TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)' -- Maximum size of table space
, TO_CHAR((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)' -- Total size allocated from the index
, TO_CHAR((((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.EXTENT_TOTAL_COUNT)/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)' -- Percentage of utilization compared to the maximum size of the tablespace
FROM (SELECT T.TABLE_NAME
, PT.PARTITION_NAME
, I.INDEX_NAME
, PI.INDEX_PARTITION_NAME
, DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
, DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
, DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
, I.INDEX_ID
, T.USER_ID
FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST
, V$SEGMENT SEG
, V$INDEX I
, V$TABLESPACES TBS
, SYSTEM_.SYS_USERS_ U
, (SELECT SPACEID
, SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
, DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
FROM V$DATAFILES
GROUP BY SPACEID) D
WHERE 1=1
AND SEG.TABLE_OID = I.TABLE_OID
AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
AND SEG.SPACE_ID = I_LIST.TBS_ID
AND I_LIST.INDEX_ID = I.INDEX_ID
AND I_LIST.TABLE_OID = I.TABLE_OID
AND I_LIST.TBS_ID = TBS.ID
AND D.SPACEID = I_LIST.TBS_ID
AND U.USER_ID = I_LIST.USER_ID
ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
;
Example of output
USER_NAME TABLE_NAME PARTITIONED_NAME INDEX_NAME PARTITIONED_INDEX TBS_NAME MAX(KB) ALLOC(KB) USAGE(%)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS CUSTOMER NON-PARTITIONED __SYS_IDX_ID_113 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS DEPARTMENT NON-PARTITIONED DEP_IDX1 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS DEPARTMENT NON-PARTITIONED __SYS_IDX_ID_111 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS EMPLOYEE NON-PARTITIONED EMP_IDX1 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS EMPLOYEE NON-PARTITIONED __SYS_IDX_ID_112 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS GOODS NON-PARTITIONED __SYS_IDX_ID_114 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS GOODS NON-PARTITIONED __SYS_IDX_ID_115 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 .01
SYS ORDERS NON-PARTITIONED ODR_IDX1 NON-PARTITIONED USER_IDX 2,097,152 256 .01
SYS ORDERS NON-PARTITIONED ODR_IDX2 NON-PARTITIONED USER_IDX 2,097,152 256 .01
SYS ORDERS NON-PARTITIONED ODR_IDX3 NON-PARTITIONED USER_IDX 2,097,152 256 .01
SYS ORDERS NON-PARTITIONED __SYS_IDX_ID_116 NON-PARTITIONED USER_DATA 2,097,152 256 .01
SYS PART_T1 P1 PART_T1_IDX P_IDX1 PART_IDX 786,432 256 .03
SYS PART_T1 P2 PART_T1_IDX P_IDX2 PART_IDX 786,432 256 .03
SYS PART_T1 P3 PART_T1_IDX P_IDX3 PART_IDX 786,432 7,424 .94
14 rows selected.
Reference - How to check the number of disk tables and indexes#
Disk table count query
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 ;
Disk index count query
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 ;
Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1#
Version#
The monitoring query introduced on this page can be used from the version that reflects BUG-31372.
- Altibase version 5.3.3.33
- Altibase version 5.3.5.15
- Altibase version 5.5.1.0.3
- Altibase version 6.1.1
- Altibase version 6.3.1
Note
In BUG-31372, the TOTAL_USED_SIZE column of X$SEGMENT has been added so that the actual usage of the disk table can be queried.
Note
In Altibase version 5.3.3, 5.3.5, 5.5.1 without BUG-31372 being modified, an error may occur when using the following query.
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 ~ AGING;, the table is locked, so other requests for the table are put in waiting for state.
Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk table usage query
SELECT U.USER_NAME USER_NAME -- Database user , TBL.TABLE_NAME TABLE_NAME -- Table name , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE -- Partitioned table name , TBS.NAME TABLESPACE_NAME -- Tablespace , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999,999') 'MAX(KB)' -- Maximum size of the tablespace to which the table belongs , TO_CHAR((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.TOTAL_EXTENT_COUNT)/1024, '999,999,999,999') 'ALLOC(KB)' -- Current allocated size , TO_CHAR(SEG.TOTAL_USED_SIZE/1024, '999,999,999,999') 'USED(KB)' -- Size of the allocated space that contains data FROM (SELECT TBL.USER_ID , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID , TBL.TABLE_NAME , PT.PARTITION_NAME , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID , TBL.IS_PARTITIONED FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID WHERE TBL.TABLE_TYPE = 'T' ) TBL , (SELECT S.TABLE_OID, SUM(S.TOTAL_EXTENT_COUNT) TOTAL_EXTENT_COUNT, SUM(S.TOTAL_USED_SIZE) TOTAL_USED_SIZE FROM X$SEGMENT S WHERE S.SEGMENT_TYPE IN (6, 7) / 6 : Table, 7 : LOB data(6.1.1 or earlier), 5 : Index / GROUP BY S.TABLE_OID) SEG , SYSTEM_.SYS_USERS_ U , V$TABLESPACES TBS , (SELECT SPACEID , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND' FROM V$DATAFILES GROUP BY SPACEID) D WHERE 1=1 AND SEG.TABLE_OID = TBL.TABLE_OID AND U.USER_ID = TBL.USER_ID AND D.SPACEID = TBL.TBS_ID AND TBS.ID = TBL.TBS_ID ORDER BY USER_NAME, TABLE_NAME, PARTITIONED_TABLE ;Example of output
USER_NAME TABLE_NAME PARTITIONED_TABLE TABLESPACE_NAME MAX(KB) ALLOC(KB) USED(KB) ------------------------------------------------------------------------------------------------------------------------------------------------------- SYS DISK_T - USER_DATA 2,097,152 316,160 316,032 SYS DISK_T2 - USER_DATA 2,097,152 256 8 SYS EMP - SYS_TBS_DISK_DATA 2,097,152 256 16 SYS PART_T1 P1 PART_DATA 1,048,576 768 520 SYS PART_T1 P2 PART_DATA 1,048,576 768 520 SYS PART_T1 P3 PART_DATA 1,048,576 126,464 126,384 SYS PART_T2 P201406 PART_DATA 1,048,576 11,520 11,280 SYS PART_T2 P201407 PART_DATA 1,048,576 22,784 22,544 SYS PART_T2 P201408 PART_DATA 1,048,576 5,888 5,648 SYS PART_T2 P201512 PART_DATA 1,048,576 8,704 8,464 SYS PART_T2 PMAX PART_DATA_DEF 1,048,576 9,728 9,592 SYS RANGE2 DEF SYS_TBS_DISK_DATA 2,097,152 256 8 SYS RANGE2 Q1_2014 SYS_TBS_DISK_DATA 2,097,152 112,896 112,688 SYS RANGE2 Q2_2014 SYS_TBS_DISK_DATA 2,097,152 56,576 56,352 SYS RANGE2 Q3_2014 SYS_TBS_DISK_DATA 2,097,152 1,792 1,704 SYS RANGE2 Q4_2014 SYS_TBS_DISK_DATA 2,097,152 768 576 SYS RANGE_SALES DEF SYS_TBS_DISK_DATA 2,097,152 256 8 SYS RANGE_SALES Q1_2014 SYS_TBS_DISK_DATA 2,097,152 5,888 5,648 SYS RANGE_SALES Q2_2014 SYS_TBS_DISK_DATA 2,097,152 11,520 11,280 SYS RANGE_SALES Q3_2014 SYS_TBS_DISK_DATA 2,097,152 8,704 8,464 SYS RANGE_SALES Q4_2014 SYS_TBS_DISK_DATA 2,097,152 4,096 3,952 21 rows selected.
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 ~ AGING, the table is locked. Therefore, other requests for the table are put in a waiting state, so be cautious when executing the ALTER INDEX ~ AGING.
Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk index usage query
-- Disk index usage column description -- USER_NAME : Database user -- TABLE_NAME : Table name -- PARTITIONED_NAME : Partitioned table name. If a non-partitioned, NON-PARTITIONED -- INDEX_NAME : Index name -- PARTITIONED_INDEX : Partitioned index name -- TBS_NAME : Tablespace to which the index belongs -- MAX(KB) : Max size of tablespace -- ALLOC(KB) : Total size allocated -- USED(KB) : Size of the allocated space that includes data -- USAGE(%) : Percentage of utilization compared to the maximum size of the tablespace set linesize 1024; set colsize 20; SELECT U.USER_NAME USER_NAME , I_LIST.TABLE_NAME , DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME , I_LIST.INDEX_NAME INDEX_NAME , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX , TBS.NAME TBS_NAME , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)' , TO_CHAR((TBS.EXTENT_PAGE_COUNT TBS.PAGE_SIZE SEG.TOTAL_EXTENT_COUNT)/1024, '999,999,999') 'ALLOC(KB)' , TO_CHAR(SEG.TOTAL_USED_SIZE/1024, '999,999,999,999') 'USED(KB)' , TO_CHAR(((SEG.TOTAL_USED_SIZE/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)' FROM (SELECT T.TABLE_NAME , PT.PARTITION_NAME , I.INDEX_NAME , PI.INDEX_PARTITION_NAME , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID , I.INDEX_ID , T.USER_ID FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID AND T.TABLE_TYPE = 'T') I_LIST , X$SEGMENT SEG , V$INDEX I , V$TABLESPACES TBS , SYSTEM_.SYS_USERS_ U , (SELECT SPACEID , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND' FROM V$DATAFILES GROUP BY SPACEID) D WHERE 1=1 AND SEG.TABLE_OID = I.TABLE_OID AND SEG.SEGMENT_PID = I.INDEX_SEG_PID AND SEG.SPACE_ID = I_LIST.TBS_ID AND I_LIST.INDEX_ID = I.INDEX_ID AND I_LIST.TABLE_OID = I.TABLE_OID AND I_LIST.TBS_ID = TBS.ID AND D.SPACEID = I_LIST.TBS_ID AND U.USER_ID = I_LIST.USER_ID ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME ;Example of output
USER_NAME TABLE_NAME PARTITIONED_NAME INDEX_NAME PARTITIONED_INDEX TBS_NAME MAX(KB) ALLOC(KB) USED(KB) USAGE(%) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SYS DISK_T NON-PARTITIONED DISK_T_IDX_01 NON-PARTITIONED USER_IDX 2,097,152 47,104 46,984 2.24 SYS DISK_T2 NON-PARTITIONED DISK_T2_IDX_01 NON-PARTITIONED USER_IDX 2,097,152 256 16 0.00 SYS EMP NON-PARTITIONED EMP_IDX_01 NON-PARTITIONED SYS_TBS_DISK_DATA 2,097,152 256 32 0.00 SYS PART_T1 P1 PART_T1_IDX P_IDX1 PART_IDX 3,145,728 256 216 .01 SYS PART_T1 P2 PART_T1_IDX P_IDX2 PART_IDX 3,145,728 256 216 .01 SYS PART_T1 P3 PART_T1_IDX P_IDX3 PART_IDX 3,145,728 46,848 46,608 1.48 SYS PART_T2 P201406 PART_T2_IDX_01 P201406 PART_IDX 3,145,728 3,840 3,808 .12 SYS PART_T2 P201407 PART_T2_IDX_01 P201407 PART_IDX 3,145,728 7,680 7,528 .24 SYS PART_T2 P201408 PART_T2_IDX_01 P201408 PART_IDX 3,145,728 2,048 1,936 .06 SYS PART_T2 P201512 PART_T2_IDX_01 P201512 PART_IDX 3,145,728 3,072 2,856 .09 SYS PART_T2 PMAX PART_T2_IDX_01 PMAX PART_IDX 3,145,728 3,328 3,232 .10 SYS PART_T2 P201406 PART_T2_IDX_02 P201406 PART_IDX 3,145,728 3,840 3,808 .12 SYS PART_T2 P201407 PART_T2_IDX_02 P201407 SYS_TBS_DISK_DATA 2,097,152 7,680 7,528 .36 SYS PART_T2 P201408 PART_T2_IDX_02 P201408 SYS_TBS_DISK_DATA 2,097,152 2,304 1,936 .09 SYS PART_T2 P201512 PART_T2_IDX_02 P201512 SYS_TBS_DISK_DATA 2,097,152 3,328 2,856 .14 SYS PART_T2 PMAX PART_T2_IDX_02 PMAX SYS_TBS_DISK_DATA 2,097,152 3,584 3,232 .15 16 rows selected.
Reference - How to check the count of disk table and index#
Error rendering macro 'code': Invalid value specified for parameter 'firstline'
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 ;
Disk index count query
set linesize 1024;
set colsize 50;
SELECT DECODE(T.IS_PARTITIONED, 'T', 'PARTITIONED INDEX CNT : '||PART_T.CNT, 'F', 'NON-PARTITIONED INDEX CNT : '||T.CNT) INDEX_COUNT
FROM (SELECT IS_PARTITIONED
, COUNT(*) CNT
FROM SYSTEM_.SYS_INDICES_
WHERE TABLE_ID IN (SELECT TABLE_ID
FROM SYSTEM_.SYS_TABLES_ T
, V$DISKTBL_INFO D
WHERE T.TABLE_OID = D.TABLE_OID)
GROUP BY IS_PARTITIONED) T
, (SELECT COUNT(*) CNT FROM SYSTEM_.SYS_INDEX_PARTITIONS_) PART_T ;