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;
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/266281/viewspace-495462/