--当前内存分配情况
select * from v$memory_dynamic_components order by current_size desc;
---总内存量建议
select * from v$memory_target_advice order by memory_size;
--查看当前有哪些用户正在查询数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
--查询数据库自启动以来最大的并发数量
select sessions_highwater from v$license;
select * from v$license;
---查看内存调整情况
select * from v$memory_resize_ops;
-------------PGA--------------------------------------
--pga系统建议
select * from v$pga_target_advice;
--pga缓存命中率 (cache hit percentage)
select * from v$pgastat order by value desc;
--查看sql执行优化度
select * from v$sql_workarea_histogram where low_optimal_size>64*1024;
---3种执行模式(最优,一次传递,多次传递)
select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage
from (select name,value cnt,(sum(value) over()) total
from v$sysstat
where name like 'workarea exec%');
select * from v$sesstat;
------------LOG_BUFFER---------------------------------------
----查看重做日志空间需求率
select round(t.value/s.value,5) "Reo log space request ratio"
from v$sysStat s,v$sysStat t
where s.name='redo log space requests'
and t.name='redo entries';
----查看日志等待空间的次数,查看重做缓冲区分配重试值
select name,value from v$sysstat where name='redo buffer allocation retries'
---系统情况
select name,value from v$sysstat order by value desc;
----获得当期数据库的会话信息
select * from v$active_session_history;
---查看15分钟内 数据库总的等待时间
select s.event,sum(s.wait_time+s.time_waited) total_wait
from v$active_session_history s
where s.sample_time between sysdate-1/24/4 and sysdate
group by s.event
order by 2 desc;