mysql：
--查询慢sql：业务db用户


select b.time, b.host, b.id, b.state, b.user, b.db, b.info
  from information_schema.processlist b
 where b.info is not null
 order by b.time


1、查询是否锁表
show OPEN TABLES where In_use > 0;
 
2、查询进程
    show processlist
  查询到相对应的进程===然后 kill    pid
 
补充：
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
 
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

 

计算ID

select (1000+500+500+500+500+500+500+260+280)-4641.33 from dual;
---------------------

 

监控慢SQL

 

SELECT G.TARGET || ' ' || S.MACHINE || ' ' || ceil((G.LAST_UPDATE_TIME - G.START_TIME)*86400)  
FROM V$SESSION_LONGOPS G, V$SESSION S 
WHERE G.SID = S.SID 
AND G.SERIAL# = S.SERIAL# 
AND G.SOFAR <> G.TOTALWORK 
AND s.state <> 'INACTIVE' 
AND G.OPNAME NOT LIKE 'RMAN:%' 
and G.TARGET <> 'TD_USERS' 
and rownum <2;

 

每天一点点，感受自己存在的意义。
SELECT G.TARGET || ' ' || S.MACHINE || ' ' || ceil((G.LAST_UPDATE_TIME - G.START_TIME)*86400)  
FROM V$SESSION_LONGOPS G, V$SESSION S 
WHERE G.SID = S.SID 
AND G.SERIAL# = S.SERIAL# 
AND G.SOFAR <> G.TOTALWORK 
AND s.state <> 'INACTIVE' 
AND G.OPNAME NOT LIKE 'RMAN:%' 
and G.TARGET <> 'TD_USERS' 
and rownum <2;