oracle一体机诊断内存,Oracle内存诊断

--当前内存分配情况

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;