Oracle优化

Oracle内存诊断

时间:2013-9-7 20:40:39  作者:www.solgle.com  来源:说歌社区  查看:340  评论:0
内容摘要:--当前内存分配情况select * from v$memory_dynamic_components order by current_size desc;---总内存量建议select * from v$memory_target_advice order by memory...
--当前内存分配情况
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;
 
 
 
标签:Oracle内存诊断 

solgle.com 版权所有,欢迎分享!!!

相关文章
    相关评论
       Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
    Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1