根据占用cpu高的进程号来查询者个进程执行的sql语句：

@H_404_2@SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,a.address) IN (SELECT DECODE(sql_hash_value,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,prev_sql_addr,sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC;

例：查询31968进程对应的sql语言：

@H_404_2@SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '31968')) ORDER BY piece ASC;

通过pid查看程序执行对应的sql:

@H_404_2@select sql_text from v$process pr,v$session ss,v$sqlarea sl where pr.addr=ss.PADDR and ss.sql_HASH_VALUE=sl.HASH_VALUE and pr.spid=10840;

查看当前正在执行的sql：

@H_404_2@select a.program,b.spid,c.sql_text,c.sql_ID from v$session a,v$process b,v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null;

1，查看cpu占用高的进程号

2，根据进程号查看该进程在做什么

@H_404_2@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(PID);

3，看看数据库的等待事件都有些什么

@H_404_2@select sid,event,p1,p1text from v$session_wait;

看看等待事件由什么进程造成的

@H_404_2@select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));

利用一下脚本可以由已知session的SID来获得sql语句

@H_404_2@select sql_text from v$sql_text a where a.hash_value=( select sql_hash_value from v$session b where b.SID=’&sid’) order by piect ASC;

查看当前会话sql_id

@H_404_2@select sql_id,username,status,event from v$session;

根据sql_id查看sql语句

@H_404_2@select sql_text from v$sql where sql_id='cx7sxk891r782';

ORACLE查询当前执行效率低的sql

@H_404_2@--cpu高的sql select sql_text 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; @H_404_2@select * from ( select sql_text,sql_id,cpu_time from v$sql order by cpu_time desc) where ownum<=10 order by rownum asc; select * from ( select sql_text,cpu_time from v$sqlarea order by cpu_time desc) where rownum<=10 order by rownum asc; --这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。 --列出使用频率最高的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,buffer_gets,dense_rank() over (order by buffer_gets desc) buffer_gets_rank from v$sql) where buffer_gets_rank<=5;