通过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; @H_404_2@select sql_text from v$sql where sql_id='cx7sxk891r782'; @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;