ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

查询Oracle正在执行的sql语句及当前被锁对象

内容发布:风哥| 发布时间:2015-11-12 21:51:40
--查询Oracle正在执行的sql语句及执行该语句的用户
[sql] view plaincopy

  • 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的发起者的发放程序

[sql] view plaincopy


  • SELECT OSUSER 电脑登录身份,  
  •        PROGRAM 发起请求的程序,  
  •        USERNAME 登录系统的用户名,  
  •        SCHEMANAME,  
  •        B.Cpu_Time 花费cpu的时间,  
  •        STATUS,  
  •        B.SQL_TEXT 执行的sql  
  • FROM V$SESSION A  
  • LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS  
  •                    AND A.SQL_HASH_VALUE = B.HASH_VALUE  
  • ORDER BY b.cpu_time DESC  

  • --查出oracle当前的被锁对象

[sql] view plaincopy


  • SELECT l.session_id sid,  
  •        s.serial#,  
  •        l.locked_mode 锁模式,  
  •        l.oracle_username 登录用户,  
  •        l.os_user_name 登录机器用户名,  
  •        s.machine 机器名,  
  •        s.terminal 终端用户名,  
  •        o.object_name 被锁对象名,  
  •        s.logon_time 登录数据库时间  
  • FROM v$locked_object l, all_objects o, v$session s  
  • WHERE l.object_id = o.object_id  
  •    AND l.session_id = s.sid  
  • ORDER BY sid, s.serial#;  

--kill掉当前的锁对象可以为
alter system kill session 'sid, s.serial#‘;

查看具体pid的执行sql语句:

SELECT sql_text FROM v$sqlarea a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = 29276));


查看具体pid的机器,用户和状态:

select b.server,b.username,b.sid,b.machine FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = 29306);
监测正在运行的SQL运行状态
select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;

select a.username,a.sid,b.sql_text from v$session a,v$sqlarea b where a.sql_address=b.address;

select
b.sql_text, --SQL内容
a.MACHINE, --哪台机器运行的SQL
a.USERNAME,--哪个用户运行的SQL
a.MODULE,--是哪运行方式
c.sofar/totalwork*100,--工作进行了百分之多少
c.elapsed_seconds, --己经用了多少时间(秒)
c.time_remaining --还剩多少时间(秒)
from v$session a, v$sqlarea b,v$session_longops c
where a.sql_hash_value=b.HASH_VALUE
and a.sid=c.sid and a.SERIAL#=c.SERIAL#;

如有个运行时候长的SQL,可通过此SQL查看到底还要运行多久,完成了多少工作等,对于SQL的性能调试还是挺有用的.尤其是对那些性能问题比较严重的SQL,就不必等太久就知道它的性能到底怎么样.
通过很多字典可以查看那些过程是否在正在运行中,比如v$access,dba_ddl_locks,v$db_object_cache等,当然通过v$session+v$sql/sqlarea等也是可以做到的

查看前五个最占用CPU的Oracle会话进程.以及他们正在执行的sql语句

#!/bin/bash

ps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5r
spid=`ps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`
for i in $spid
do
sqlplus -S /nolog << EOF
conn / as sysdba
set feedback off
set linesize 200
set pagesize 70
column spid format 99999
column sid format 99999
column module format a20
column username format a8
column sql_text format a60
select distinct c.spid,b.sid,b.username,a.module,a.hash_value,sql_text
from v$sql a,v$session b,v$process c
where a.hash_value=b.sql_hash_value and a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;
exit
EOF
done

查看造成等待事件的具体SQL语句




先查看存在的等待事件:
col event for a40
col WAIT_CLASS format a20
select sid,WAIT_CLASS,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait
where event not like 'SQL%' and event not like 'rdbms%';

复制上面查到的等待事件,替换下面红色字体。即可找到具体的SQL语句
col objn format a26
col otype format a10
select b.* ,a.sql_fulltext from v$sqlarea a,
(select * from (select
    count(*),
    sql_id,
    nvl(o.object_name,ash.current_obj#) objn,
    substr(o.object_type,0,10) otype,
    CURRENT_FILE# fn,
         CURRENT_BLOCK# blockn
   from  v$active_session_history ash
       , all_objects o
   where event like 'latch: cache buffers chains'
     and o.object_id (+)= ash.CURRENT_OBJ#
   group by sql_id, current_obj#, current_file#,
                  current_block#, o.object_name,o.object_type
   order by  count(*) desc )where rownum <=15) b
where a.sql_id=b.sql_id;
这是查看造成 latch: cache buffers chains  等待事件的前15条记录。

查看oracle死锁进程并结束死锁

查看锁表进程SQL语句1:
select sess.sid,
   sess.serial#,
   lo.oracle_username,
   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
   from v$locked_object lo,
   dba_objects ao,
   v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

查看锁表进程SQL语句2:
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀掉锁表进程:
如有記錄則表示有lock,記錄下SID和serial# ,將記錄的ID替換下面的738,1429,即可解除LOCK
alter system kill session '738,1429';

用这个可以查:  
select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode, 'ALTER  SYSTEM  KILL  SESSION  '''||s.sid||',  '||s.serial#||''';'   Command  from  v$locked_object  l,v$session  s,all_objects  o  where  l.session_id=s.sid  and  l.object_id=o.object_id  

可以查看哪台机器哪个用户锁了记录, 其中command是用来杀掉锁住记录的session
******************************************************************************************************************
SELECT        A.OBJECT_ID,        B.OBJECT_NAME,        A.SESSION_ID,        A.ORACLE_USERNAME,        A.OS_USER_NAME,        A.PROCESS,        A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE A.OBJECT_ID = B.OBJECT_ID;
SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID ORDER BY T2.LOGON_TIME;
ALTER SYSTEM KILL SESSION 'sid, serial#';
********************************************************************************************************************

select   V$SESSION.sid,v$session.SERIAL#,v$process.spid,   
  rtrim(object_type)   object_type,rtrim(owner)   ||   '.'   ||   object_name   object_name,   
  decode(lmode,       0,   'None',   
  1,   'Null',   
  2,   'Row-S',   
  3,   'Row-X',   
  4,   'Share',   
  5,   'S/Row-X',   
  6,   'Exclusive', 'Unknown')   LockMode,   
  decode(request,   0,   'None',   
  1,   'Null',   
  2,   'Row-S',   
  3,   'Row-X',   
  4,   'Share',   
  5,   'S/Row-X',   
  6,   'Exclusive',   'Unknown')   RequestMode   
  ,ctime,   block   b,   
  v$session.username,MACHINE,MODULE,ACTION,   
  decode(A.type,   
  'MR',   'Media   Recovery',   
  'RT','Redo   Thread',   
  'UN','User   Name',   
  'TX',   'Transaction',   
  'TM',   'DML',   
  'UL',   'PL/SQL   User   Lock',   
  'DX',   'Distributed   Xaction',   
  'CF',   'Control   File',   
  'IS',   'Instance   State',   
  'FS',   'File   Set',   
  'IR',   'Instance   Recovery',   
  'ST',   'Disk   Space   Transaction',   
  'TS',   'Temp   Segment',   
  'IV',   'Library   Cache   Invalida-tion',   
  'LS',   'Log   Start   or   Switch',   
  'RW',   'Row   Wait',   
  'SQ',   'Sequence   Number',   
  'TE',   'Extend   Table',   
  'TT',   'Temp   Table',   
  'Unknown')   LockType   
  from   (SELECT   *   FROM   V$LOCK)   A,   all_objects,V$SESSION,v$process   
  where   A.sid   >   6   
  and   object_name<>'OBJ$'   
  and   A.id1   =   all_objects.object_id   
  and   A.sid=v$session.sid   
  and   v$process.addr=v$session.paddr;

同样也是通过写sql从数据字典里查出来。
// SELECT-SQL1
// 功能:检查被加锁的对象
//select obj.OWNER||'.'||obj.OBJECT_NAME as OBJ_NAME, // 对象名称(已经被锁住)
//   obj.SUBOBJECT_NAME as SUBOBJ_NAME,     // 子对象名称(已经被锁住)
//   obj.OBJECT_ID as OBJ_ID,        // 对象ID
//   obj.OBJECT_TYPE as OBJ_TYPE,       // 对象类型
//   lock_obj.SESSION_ID as SESSION_ID,     // 会话SESSION_ID
//   lock_obj.ORACLE_USERNAME as ORA_USERNAME,  // ORACLE系统用户名称
//   lock_obj.OS_USER_NAME as OS_USERNAME,    // 操作系统用户名称
//   lock_obj.PROCESS as PROCESS       // 进程编号
//from
//  ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,
//  v$locked_object lock_obj
//where obj.object_id=lock_obj.object_id;
//// SELECT-SQL2
//// 功能:检查被加锁的对象以及加锁的会话信息
////  如果需要手工解除锁,请对照要解锁的对象,记下SESSION_ID,SERIAL#
//// 项,然后运行下面的ALTER-SQL1
//select LOCK_INFO.OWNER||'.'||LOCK_INFO.OBJ_NAME as OBJ_NAME, // 对象名称(已经被锁住)
//   LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME,       // 子对象名称(已经被锁住)
//   SESS_INFO.MACHINE as MACHINE,         // 机器名称
//   LOCK_INFO.SESSION_ID as SESSION_ID,       // 会话SESSION_ID
//   SESS_INFO.SERIAL# as SERIAL#,         // 会话SERIAL#
//   LOCK_INFO.ORA_USERNAME as ORA_USERNAME,      // ORACLE系统用户名称
//   LOCK_INFO.OS_USERNAME as OS_USERNAME,       // 操作系统用户名称
//   LOCK_INFO.PROCESS as PROCESS,         // 进程编号
//   LOCK_INFO.OBJ_ID as OBJ_ID,          // 对象ID
//   LOCK_INFO.OBJ_TYPE as OBJ_TYPE,         // 对象类型
//   SESS_INFO.LOGON_TIME as LOGON_TIME,       // 登录时间
//   SESS_INFO.PROGRAM  as PROGRAM,         // 程序名称
//   SESS_INFO.STATUS as STATUS,          // 会话状态
//   SESS_INFO.LOCKWAIT as LOCKWAIT,         // 等待锁
//   SESS_INFO.ACTION as ACTION,          // 动作
//   SESS_INFO.CLIENT_INFO as CLIENT_INFO       // 客户信息
//
//from
// (
//  select obj.OWNER as OWNER,
//     obj.OBJECT_NAME as OBJ_NAME,
//     obj.SUBOBJECT_NAME as SUBOBJ_NAME,
//     obj.OBJECT_ID as OBJ_ID,
//     obj.OBJECT_TYPE as OBJ_TYPE,
//     lock_obj.SESSION_ID as SESSION_ID,
//     lock_obj.ORACLE_USERNAME as ORA_USERNAME,
//     lock_obj.OS_USER_NAME as OS_USERNAME,
//     lock_obj.PROCESS as PROCESS
//  from
//   ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,
//   v$locked_object lock_obj
//  where obj.object_id=lock_obj.object_id
// )  LOCK_INFO,
// (
//  select SID,
//     SERIAL#,
//     LOCKWAIT,
//     STATUS,
//     PROGRAM,
//     ACTION,
//     CLIENT_INFO,
//     LOGON_TIME,
//     MACHINE
//  from v$session
// ) SESS_INFO
//where LOCK_INFO.SESSION_ID=SESS_INFO.SID ;

//// 看清楚了 下面就可以 杀死它了。
// ALTER-SQL1
// 功能:杀死会话(SESSION_ID,SERIAL#),可以手工解除锁
//    请手工修改SESSION_ID,SERIAL#为相应值
// 注意:本功能慎重使用,有一定的破坏性,该SQL可以断开客户机和服务器的连接
//ALTER SYSTEM KILL SESSION 'SESSION_ID,SERIAL#';





上一篇:Oracle 11g spatial组件的删除与重建过程
下一篇:使用dbms_rectifier_diff來比較兩個表格資料
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

内容发布:ddwl| 发布时间:2015-11-18 21:39:27
非常好。收藏了,正好需要啊
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表