查找消耗CPU较大的sql语句
一、使用unix系统命令查看资源
#sar 1 9
12:15:27 73 27 0 0
12:15:28 70 30 0 0
12:15:29 80 20 0 0
12:15:30 84 16 0 0
12:15:31 21 5 1 73
#top
TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
? 23093 oracle 154 20 29748K 3112K run 20:53 4.59 4.58 oraclebv
? 23087 oracle 154 20 29636K 3016K run 24:18 0.93 0.93 oraclebv
二、使用oracle数据字典查找sql
SQL>set line 240
SQL>set verify off
SQL>column sid format 999
SQL>column pid format 999
SQL>column S_# format 999
SQL>column username format A9 heading "ORA User"
SQL>column program format a29
SQL>column SQL format a60
SQL>COLUMN OSname format a9 Heading "OS User"
SQL>SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,a.sql_text SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
Enter value for 1: 23209（系统消耗较大进程id）找到结果：
select * from cc_por where status=1 and deleted=0
使用autotrace分析语句。
sql>set timing on
sql>set autotrace on
sql>select * from cc_por where status=1 and deleted=0；
根据结果进行分析和改进。