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

select b.username username,a.disk_reads reads,

a.executionsexec,a.disk_reads/decode(a.executions,0,1,a.executions)rds_exec_ratio,

a.sql_textStatement,

a.CPU_TIME,a.CPU_TIME/decode(a.executions,0,1,a.executions)

fromv$sqlareaa,dba_usersb

wherea.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-------查询当前会话

selectcasewhenr.session_idisnotNULLandr.status='SUSPENDED'then'Suspended'when

max(decode(s.serial#,l.serial#,1,0))=1then'LongOperations'else'Regular'end,s.sid,s.status,

s.username,RTRIM(s.osuser),b.spid,s.machine,s.program,s.resource_consumer_group,r.timeout

fromv$sessions,v$session_longopsl,v$processb,dba_resumabler

where(s.sid=l.sid(+))and(s.sid=r.session_id(+))ands.paddr=b.addr

ands.status='ACTIVE'

groupbyr.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值

selectdecode(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

selectdistinctpiece,sql_text,command_typefromv$sqltext_with_newlineswhere rawtohex(address)='070000012D441468' and hashvalue='3018797201';

4----查询对话SERIAL号

SELECTserial#,username,schemaname,osuser,terminal,machine,program,status,type,saddr,

logon_time,sysdate-last_call_et/86400,resource_consumer_group

FROMv$sessionWHEREsid=26;

5----终止对话

ALTERSYSTEMKILLSESSION'26,35851';(sid,serial#)