sql执行cpu占用情况查询，查表是否已锁

select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement,
a.CPU_TIME,a.CPU_TIME/decode(a.executions,0,1,a.executions)
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
order by a.CPU_TIME desc;

下面是各个字段的意思：

v$sqlarea：Oracle动态性能表

disk_reads 所有子光标读取硬盘数据总和
executions  所有子光标的执行数目
sqltext         当前光标的对应的SQL语句的头1000个字符

cpu_time    该光标用于解析、执行和取数据的cpu时间，单位毫秒

dba_users:用户信息表

username 用户名
user_id     用户id号，是一个数字

这样的话，用此sql可以查看哪句sql执行太慢，可以考虑进行性能优化。下面介绍下查询是否锁表的方法：

1-------查询当前会话
select case when r.session_id is not NULL and r.status = 'SUSPENDED'then 'Suspended' when
 max(decode(s.serial#,l.serial#,1,0)) = 1 then 'Long Operations' else 'Regular' end, s.sid,s.status,
 s.username, RTRIM(s.osuser), b.spid, s.machine, s.program, s.resource_consumer_group, r.timeout
from v$session s, v$session_longops l, v$process b, dba_resumable r
where (s.sid = l.sid(+)) and (s.sid = r.session_id(+)) and s.paddr=b.addr
and  s.status='ACTIVE'
group by r.session_id, r.error_number, r.status, s.sid,
  s.status, s.username, s.osuser, b.spid, s.machine,
         s.program,s.resource_consumer_group, r.timeout;

2-----获取对话地址及hash值
select  decode(sql_address,'00',prev_sql_addr,sql_address) sql_address,decode(sql_hash_value,0,
        prev_hash_value,sql_hash_value) sql_hash_value
from v$session  where sid =26;

3-----根据对话地址及hash值查询执行SQL
select distinct piece, sql_text, command_type from v$sqltext_with_newlines
where rawtohex(address)='070000012D441468' and hashvalue='3018797201';

4----查询对话SERIAL号
SELECT serial#, username, schemaname, osuser, terminal, machine, program, status,type,saddr,
        logon_time,sysdate - last_call_et / 86400, resource_consumer_group 
FROM v$session WHERE sid =26;
5----终止对话
ALTER SYSTEM KILL SESSION '26,35851';  (sid,serial#)