作者: momy 分类: 数据库 0 Comment »
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;
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);
select sql_text,cpu_time from v$sql order by cpu_time desc
select * from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc) where rownum<=30;
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<81;
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;
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
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;

标签: Oracle 数据库优化 oracle优化 阅读: 1631
上一篇: 设置oracle数据库为归档模式 - 1849次
下一篇: Oracle环境变量NLS_LANG - 1300次