Overview
This document describes the memory table and index usage query.
Memory table data usage query
The following query can be used in all versions of Altibase.
set linesize 2048;
set colsize 30;
SELECT a.user_name
,NVL(d.name,'SYS_TBS_MEMORY') AS 'TABLESPACE_NAME'
, b.table_name
, round((c.fixed_alloc_mem + c.var_alloc_mem)/(1024*1024),2) 'ALLOC(M)'
, round((c.fixed_used_mem + c.var_used_mem)/(1024*1024),2) 'USED(M)'
, round((c.fixed_used_mem + c.var_used_mem)/(c.fixed_alloc_mem + c.var_alloc_mem)*100,2) 'EFFICIENCY(%)'
FROM system_.sys_users_ a
, system_.sys_tables_ b
, v$memtbl_info c left outer join v$tablespaces d on c.tablespace_id = d.id
WHERE b.table_type = 'T'
and a.user_id = b.user_id
and b.table_oid = c.table_oid
order by 1,2,3, 4 desc ;
Example of output
Memory table index usage
The index usage of a memory table cannot be checked directly by a query. Altibase memory tables have a size of 16 bytes per index regardless of the number and type of index columns, and the index usage can be calculated as 16 bytes * number of records.
Query to check memory table index information (all available from HDB 4 to HDB 7)
The index information for each table can be checked with the following query. To check the index size, the user must check the number of records and calculate it separately, or use a query that calculates the size of the index of the memory table containing the function.
set linesize 2048;
set colsize 30;
SELECT
c.user_name
, decode(f.table_type, 'Q', 'QUEUE', 'T', 'TABLE') object_type
, table_name object_name
, e.index_name
, rpad(case2(e.index_type=1, 'b-tree', 'r-tree'),10,' ') index_type
, '16 bytes * rowcount' 'ALLOC'
FROM v$memtbl_info a
, v$index b
, system_.sys_users_ c
, system_.sys_indices_ e
, system_.sys_tables_ f
WHERE
a.table_oid = f.table_oid
and b.index_id = e.index_id
and e.user_id = c.user_id
and f.user_id = e.user_id
and f.tbs_id = a.tablespace_id
and f.table_oid = b.table_oid
and c.user_name <> 'SYSTEM_' ;
Example of output
Query to check usage per memory table index (available from HDB 5.x or later)
The usercan use the following query to check the usage per index of a memory table. Before using the query, the user must create a DB function that can get the number of records for that table.
1. Create a function that can count the number of records in the table.
CREATE FUNCTION GETCOUNT(u_name varchar(40), t_name varchar(40))
RETURN INTEGER
AS
RECORDCOUNT integer;
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || u_name||'.'||t_name INTO RECORDCOUNT ;
RETURN RECORDCOUNT;
END;
/
2. Use function to query the usage per index.
set linesize 2048;
set colsize 30;
SELECT
c.user_name
, decode(f.table_type, 'Q', 'QUEUE', 'T', 'TABLE') object_type
, table_name object_name
, e.index_name
, rpad(case2(e.index_type=1, 'b-tree', 'r-tree'),10,' ') index_type
, ROUND( 16 * GETCOUNT(c.user_name, f.table_name) / 1024/1024, 2) 'ALLOC(M)'
FROM v$memtbl_info a
, v$index b
, system_.sys_users_ c
, system_.sys_indices_ e
, system_.sys_tables_ f
WHERE
a.table_oid = f.table_oid
and b.index_id = e.index_id
and e.user_id = c.user_id
and f.user_id = e.user_id
and f.tbs_id = a.tablespace_id
and f.table_oid = b.table_oid
and c.user_name <> 'SYSTEM_' ;
Example of output
Query to check total index usage per memory table (available from HDB 5.x or later)
Query the total index usage per table used by the memory table. Before using the query, the user must create a DB function that can get the number of records for that table.
1. Create a function that can count the number of records in the table.
CREATE FUNCTION GETCOUNT(u_name varchar(40), t_name varchar(40))
RETURN INTEGER
AS
RECORDCOUNT integer;
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || u_name||'.'||t_name INTO RECORDCOUNT ;
RETURN RECORDCOUNT;
END;
/
2. Use function to look up the total index usage size per table.
select
user_name
, table_name
, count(index_name) AS 'INDEX_COUNT'
, round( SUM(alloc) /1024/1024, 2 ) as 'Alloc(M)'
from (
SELECT
c.user_name
, f.table_name
, e.index_name
, 16 * GETCOUNT(c.user_name, f.table_name) AS alloc
FROM v$memtbl_info a
, v$index b
, system_.sys_users_ c
, system_.sys_indices_ e
, system_.sys_tables_ f
WHERE
a.table_oid = f.table_oid
and b.index_id = e.index_id
and e.user_id = c.user_id
and f.user_id = e.user_id
and f.tbs_id = a.tablespace_id
and f.table_oid = b.table_oid
and c.user_name <> 'SYSTEM_'
)
group by user_name, table_name;
Query size per index of memory table (for 6.x)
The user can query more accurate memory table usage per index with the following query. Available in version 6.1.1 or later.
SELECT U.USER_NAME, T.TABLE_NAME TABLE_NAME
, B.INDEX_NAME
, LPAD(I.IS_PARTITIONED, 14) INDEX_PARTITIONED
, ROUND(((USED_NODE_COUNT+ PREPARE_NODE_COUNT) / 15 * 32768)/1024/1024, 1) AS 'SIZE(MB)'
FROM V$MEM_BTREE_HEADER B
, SYSTEM_.SYS_INDICES_ I
, SYSTEM_.SYS_TABLES_ T
, SYSTEM_.SYS_USERS_ U
WHERE 1=1
AND B.INDEX_ID = I.INDEX_ID
AND I.TABLE_ID = T.TABLE_ID
AND B.INDEX_TBS_ID <> 0
AND U.USER_ID = T.USER_ID
ORDER BY TABLE_NAME, B.INDEX_ID
;
Example of output