--查看终端会话连接数
set linesize 150
set colsize 50
select comm_name, db_username,count(*) from v$session group by comm_name,db_username order by 1 desc;

--查看本库所有内存使用信息
select name,alloc_size,max_total_size from v$memstat order by 3 desc;

--查看内存表空间使用大小，接近 MEM_MAX_DB_SIZE 要及时扩大
select Alloc_size from v$memstat where name='Storage_Memory_Manager';

--查看阻塞会话
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;

--统计服务线程状态
select type, state, count(*) from v$service_thread group by type, state;

--语句锁
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;