ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 怎么找出性能低的SQL,

怎么找出性能低的SQL,

原创 Linux操作系统 作者:hjgo5 时间:2019-01-29 18:18:06 0 删除 编辑
1.先在操作系统上面运行top,查看消耗大量cpu的进程ID
2、查看进程:
oracle >ps -ef | grep 22605
oracle 22605 1 31 5月 31 ? 58752:11 ora9 (LOCAL=NO)
3.查询v$session,v$processes 找到繁忙spid的一些信息
select a.sid,a.serial#,a.username,a.machine,a.program
from v$session a,v$process b
where a.paddr = b.addr
and b.spid = '&pid';
输入 pid 的值: 22605
原值 4: and b.spid = '&pid'
新值 4: and b.spid = '22605'

SID SERIAL# USERNAME PROGRAM
--------------- --------------- ------------ --------------------
110 43585 sxitismg JDBC Thin Client

4、得到SQL:

SQL> select /*+ordered*/
sql_text
from v$sqltext a
where (a.hash_value, a.address) in (
select decode (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
decode (sql_hash_value, 0, prev_sql_addr, sql_address)
from v$session b, v$process c
where b.paddr = c.addr and c.spid = '&pid')
order by piece asc;
输入 pid 的值: 22605
原值 13: WHERE c.spid = '&pid'))
新值 13: WHERE c.spid = '22605'))


top10的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 ;
select /*+ordered */ spid,s.sid,s.serial#,s.program, event , p1,p2 , sql_text
from v$process p,v$session s,v$session_wait w,v$sql sql
where 1=1
and p.spid in (159778,116992 , ... )
and s.paddr = p.addr
and s.sid = w.sid
and s.sql_hash_value = sql.hash_value(+);



select a.SID,a.SERIAL#,a.STATUS,b.SPID
from v$session a,v$process b
where a.PADDR=b.ADDR
and spid in (159778,116992 , 74066,119932,159778,198836,202152,96924,125106)
/
Rem 查看等待事件
select /*+ordered */ spid,s.sid,s.serial#,s.program, event , p1,p2
from v$process p,v$session s,v$session_wait w
where 1=1
and p.spid in (159778,116992 , 74066,119932,159778,198836,202152,96924,125106)
and s.paddr = p.addr
and s.sid = w.sid


select * from (SELECT SQL_TEXT Text,
buffer_gets,
executions,
buffer_gets/executions as AVG
FROM v$sqlarea
where executions>0
ORDER BY AVG desc ) where rownum<10;

至于如何调整就要看语句是什么语句了,数据的分布情况如何?查询方式如何?连接方式如何?索引使用情况如何....................

select address,sql_text,rows_processed,disk_reads,disk_reads/rows_processed avg
from v$sqlarea where rows_processed>0
and disk_reads/rows_processed>500
order by 5

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/266281/viewspace-495462/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DBA工具_OTop
请登录后发表评论 登录
全部评论

注册时间:2005-03-03

  • 博文量
    58
  • 访问量
    43671