2016-0318
Oracle数据库优化相关分析语句
分类: 数据库
0
Comment »
摘要：Oracle数据库优化相关分析语句
--查看session和process上限和峰值
SELECT RESOURCE_NAME,MAX_UTILIZATION,LIMIT_VALUE FROM V$RESOURCE_LIMIT;
--查看oracle缓存的命中率
select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';
--访问最多的语句
select sql_text, parse_calls, executions from v$sqlarea order by parse_calls desc;
--查看CPU使用最多的sql
select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (SPID);
--CPU高的SQL
select sql_text,cpu_time from v$sql order by cpu_time desc
--逻辑读多的SQL语句
select * from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc) where rownum<=30;
--执行次数多的SQL
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<81;
--读硬盘多的SQL
select sql_text,disk_reads from
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;
--排序多的SQL ：
select sql_text,sorts from
(select sql_text,sorts from v$sqlarea order by sorts desc)
where rownum<21;
--监控表空的I/O比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f,dba_data_files df
where f.file#=df.file_id
--找使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
最近评论