ALTIBASE常用监控语句

以下是在ALTIBASE内存数据库管理过程常用语句：

 

统计服务线程状态

select type, state, count(*) from v$service_thread group by type, state;

 

 

查看终端会话

select comm_name, count(*) from v$session group by comm_name order by 1 desc;

 

查看阻塞会话

select a.comm_name Client_IP, client_pid Client_Pid from v$session a , v$service_thread b where a.SERVICE_THREAD_ID=b.id and b.type='DEDICATED';

 

查找前5个用时最多的SQL

select query, total_time from v$statement order by 2 desc limit 5;

 

查看线程配置

select name, value1 from v$property where name like 'MULTIPLEXING%';

 

查看复制阻塞的情况

select * from v$repgap;

 

语句锁

v$lock, v$lock_statement, v$lock_wait

 

查看数据库内存分配情况

select MEM_ALLOC_PAGE_COUNT , MEM_FREE_PAGE_COUNT  from v$database;

 

查看内存数据库总的分配大小

select sum(alloc_size) from v$memstat;

 

查看表占用的空间

select a.name, b.TABLE_NAME ,d.fixed_alloc_mem+d.var_alloc_mem MEM_ALLOC_SIZE ,

       d.fixed_used_mem+d.var_used_mem USED_SIZE 

  from x$tablespaces a, system_.SYS_TABLES_ b, X$TABLE_INFO c ,V$MEMTBL_INFO d

 where b.TABLE_OID=c.TABLE_OID and c.TABLESPACE_ID=a.ID and d.TABLE_OID=c.TABLE_OID 

   and b.table_name not like 'SYS_%' and b.table_name not like 'NEXT_%'

 order by a.name desc , b.table_name asc;