[PL/SQL] oracle sql语句 存储过程执行慢,单独执行快

一、执行计划情况

当存储过程挂住的时候，看看V$SESSION里面的 SQL_ID, SQL_CHILD_NUMBER

再根据这两个信息用DBMS_XPLAN.DISPLAY_CURSOR把计划拿出来看看。

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

二、锁表情况

Oracle:

select  ao.owner,ao.object_name,lo.LOCKED_MODE,lo.OS_USER_NAME,LO.PROCESS,SESSION_ID,LO.ORACLE_USERNAME

from  v$locked_object lo, dba_objects ao 
where  ao.object_id = lo.object_id

Sybase:

select l.spid, locktype=convert(char(12),name),dbname=convert(char(15),db_name(l.dbid)),  'table'=convert(char(25),object_name(l.id,l.dbid)), page,  

       class=convert(char(15),class),  hostname, cmd  
from master..syslocks l,master..spt_values v,master..sysprocesses p  
where l.type = v.number and  
v.type = 'L' and  
      l.spid = p.spid  
order by spid 

Oracle杀掉进程

1、进程对应SID,SERIAL#

select SID,SERIAL#,USERNAME,SCHEMANAME,OSUSER,MACHINE,PROGRAM,SQL_ID, SQL_CHILD_NUMBER,SQL_EXEC_START,ACTION 
from V$SESSION 
where machine ='XXX';

2、Oracle杀掉

ALTER SYSTEM KILL SESSION '1089,16019' ;

3、被标记为Killed后，无法kill，在系统找对应进程spid并杀掉:

select spid, osuser, s.program
 from v$session s, v$process p
 where s.paddr = p.addr
   and s.sid =1089;

kill xxxxx

三、查存储过程正在运行的SQL运行情况

  SELECT a.username,a.machine, b.sql_id, b.SQL_TEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
    AND a.SQL_HASH_VALUE = b.HASH_VALUE;

四、索引是否创建