oracle 常用语句

查找前十条性能差的sql语句：

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, COMMAND_TYPE,DISK_READS,sql_text
FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;

 查看占io较大的正在运行的session：

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,
se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.
p1text,si.physical_reads,
si.block_changes FROM v$session se,v$session_wait st,
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

查看当前会话连接数

select count(*) from v$session;

 查看会话的详细信息

select sid,serial#,username,program,machine,status from v$session;

 监控表空间的 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
order by df.tablespace_name;

 监控文件系统的 I/O 比例

select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

 列出使用频率最高的5个查询

select sql_text,executions
from (select sql_text,executions,rank() over (order by executions desc) exec_rank from v$sql)
where exec_rank <=5;

 耗磁盘读取最多的sql top5

select disk_reads,sql_text
from (select sql_text,disk_reads,dense_rank() over(order by disk_reads desc) disk_reads_rank from v$sql)
where disk_reads_rank <=5;

 找出需要大量缓冲读取（逻辑读）操作的查询

select buffer_gets,sql_text
from (select sql_text,buffer_gets,dense_rank() over (order by buffer_gets desc) buffer_gets_rank from v$sql)
where buffer_gets_rank<=5;