oracle的性能查询sql

查看数据库正在执行的sql

SELECT b.sid oracleID, 
b.username Oracle用户, 
b.serial#, 
spid 操作系统ID, 
paddr, 
sql_text 正在执行的SQL, 
b.machine 计算机名 
FROM v$process a, v$session b, v$sqlarea c 
WHERE a.addr = b.paddr 
AND b.sql_hash_value = c.hash_value;

查询执行最慢的sql

select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;

查看数据库锁表

select p.spid,
a.serial#,
c.object_name,
b.session_id,
b.oracle_username,
b.os_user_name
from v$process p, v$session a, v$locked_object b, all_objects c
where p.addr = a.paddr
and a.process = b.process
and c.object_id = b.object_id;

干掉所有锁死表

declare cursor mycur is
select b.sid,b.serial#
　　from v$locked_object a,v$session b
　　where a.session_id = b.sid group by b.sid,b.serial#;
 
 
begin
  for cur in mycur
    loop  
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');
     end loop;
 
end;