网站建设| 数据库类| 图形图象| 程序设计| 现代办公| 操作系统| 考试认证| 网络技术| 软件工程| 电脑相关| 文学作品
网站开发| 网页制作| 操作系统| 图象图形| 考试认证| 数据库类| 程序设计| 硬件技术| 现代办公| 网络技术| 笑话频道
 
您的位置: 电脑书库首页-> 电脑文摘-> 数据库类-> Oracle-> 怎样快速查出Oracle数据库中的锁等待

怎样快速查出Oracle数据库中的锁等待
作者:赵华良 来源:摘自《计算机世界》 加入时间:2004-11-10
相关文章
  • 夺取月薪之冠-Oracle认证!
  • Oracle数据安全面面观(2)
  • Oracle数据安全面面观(1)
  • Oracle10g数据库的创建
  • Oracle新技术对Linux意味着什么?
  • 在Oracle里设置访问多个SQL Server
  • 深入分析Oracle数据库日志文件
  • 在ORACLE里用存储过程定期分割表
  • 用Linux完成Oracle自动物理备份
  • Oracle Rman/tivoli-TDP/下数据恢复到节点
  • 相关书籍:
  • 利用 DBCA建立 Oracle 9i 资料库
  • Oracle 数据库DBA管理手册
  • Oracle 管理员手册
  • Oracle 官方文档CHM合集-SQL参考手册
  • Oracle WebServer 中文手册
  • Oracle 9i 数据库管理员指南
  • Oracle 9i 数据库管理实务讲座
  • Oracle 9I 入门基础(第02部分)
  • Oracle 9I 入门基础(第01部分)
  • Oracle 9i 官方教材
  • ---- 在大型数据库系统中,为了保证数据的一致性,在对数据库中的数据进行操作时,系统会进行对数据相应的锁定。

    ---- 这些锁定中有"只读锁"、"排它锁","共享排它锁"等多种类型,而且每种类型又有"行级锁"(一次锁住一条记录),"页级锁"(一次锁住一页,即数据库中存储记录的最小可分配单元),"表级锁"(锁住整个表)。

    ---- 若为"行级排它锁",则除被锁住的该行外,该表中其它行均可被其它的用户进行修改(Update)或删除(delete)操作,若为"表级排它锁",则所有其它用户只能对该表进行查询(select)操作,而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后,锁住的资源便会得到释放,从而允许其它用户进行操作。

    ---- 但是,有时,由于程序中的原因,锁住资源后长时间未对其工作进行提交;或是由于用户的原因,如调出需要修改的数据后,未及时修改并提交,而是放置于一旁;或是由于客户服务器方式中客户端出现"死机",而服务器端却并未检测到,从而造成锁定的资源未被及时释放,影响到其它用户的操作。

    ---- 因而,如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。

    ---- 由于数据库应用系统越来越复杂, 一旦出现由于锁资源未及时释放的情况,便会引起对一相同表进行操作的大量用户无法进行操作,从而影响到系统的使用。此时,DBA应尽量快地解决问题。但是,由于在Oracle 8.0.x 中执行"获取正在等待锁资源的用户名"的查询语句

    select a.username, a.sid, a.serial#, b.id1
    from v$session a, v$lock b
    where a.lockwait = b.kaddr

    ---- 十分缓慢,(在 Oracle 7.3.4中执行很快),而且,执行"查找阻塞其它用户的用户进程"的查询语句
    select a.username, a.sid, a.serial#, b.id1
    from v$session a, v$lock b
    where b.id1 in
    (select distinct e.id1
    from v$session d, v$lock e
    where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and b.request = 0

    ---- 执行得也十分缓慢。因而,往往只好通过将 v$session 中状态为"inactive"(不活动)并且最后一次进行操作时间至当前已超过 20 分钟以上(last_call_et>20*60 秒)的用户进程清除,然后才使得问题得到解决。
    ---- 但是,这种方法实际上是"把婴儿与脏水一起泼掉"。因为,有些用户的进程尽管也为"inactive",并且也已有较长时间未活动,但是,那是由于他们处于锁等待状态。

    ---- 因而,我想出了一个解决办法。即通过将问题发生时的 v$lock,v$session视图中的相关记录保存于自己建立的表中,再对该表进行查询,则速度大大提高,可以迅速发现问题。经实际使用,效果非常好。在接到用户反映后,几秒钟即可查出由于锁住资源而影响其它用户的进程,并进行相应的处理。

    ---- 首先,以 dba 身份(不一定为system)登录入数据库中,创建三个基本表:my_session,my_lock, my_sqltext,并在将会进行查询的列上建立相应的索引。语句如下: rem 从 v$session 视图中取出关心的字段,创建 my_session 表,并在查询要用到的字段上创建索引,以加快查询速度

    drop table my_session;
    create table my_session
    as
    select a.username, a.sid, a.serial#,
    a.lockwait, a.machine,a.status,
    a.last_call_et,a.sql_hash_value,a.program
    from v$session a
    where 1=2 ;

    create unique index my_session_u1 on my_session(sid);
    create index my_session_n2 on my_session(lockwait);
    create index my_session_n3 on my_session(sql_hash_value);

    ---- rem 从 v$lock 视图中取出字段,创建 my_lock 表,并在查询要用到的字段上创建索引,以加快查询速度
    drop table my_lock;
    create table my_lock
    as
    select id1, kaddr, sid, request,type
    from v$lock
    where 1=2;

    create index my_lock_n1 on my_lock(sid);
    create index my_lock_n2 on my_lock(kaddr);

    ---- rem 从 v$sqltext 视图中取出字段,创建 my_sqltext 表,并在查询要用到的字段上创建索引,以加快查询速度
    drop table my_sqltext;
    create table my_sqltext
    as
    select hash_value , sql_text
    from v$sqltext
    where 1=2;

    create index my_sqltext_n1 on my_sqltext ( hash_value);

    ---- 然后,创建一个 SQL 脚本文件,以便需要时可从 SQL*Plus 中直接调用。其中,首先用 truncate table 表名命令将表中的记录删除。之所以用 truncate 命令,而不是用delete 命令,是因为delete 命令执行时,将会产生重演记录,速度较慢,而且索引所占的空间并未真正释放,若反复做 insert及delete,则索引所占的空间会不断增长,查询速度也会变慢。而 truncate命令不产生重演记录,速度执行较delete快,而且索引空间被相应地释放出来。删除记录后,再将三个视图中的相关记录插入自己创建的三个表中。最后,对其进行查询,由于有索引,同时由于在插入时条件过滤后,记录数相对来说较少,因而查询速度很快,马上可以看到其结果。
    ---- 此时,若发现该阻塞其它用户进程的进程是正常操作中,则可通知该用户对其进行提交,从而达到释放锁资源的目的;若为未正常操作,即,其状态为"inactive",且其last_call_et已为较多长时间,则可执行以下语句将该进程进行清除,系统会自动对其进行回滚,从而释放锁住的资源。

    alter system kill session 'sid, serial#';
    ---- SQL 脚本如下:
    set echo off
    set feedback off
    prompt '删除旧记录.....'
    truncate table my_session;
    truncate table my_lock;
    truncate table my_sqltext;

    prompt '获取数据.....'
    insert into my_session
    select a.username, a.sid, a.serial#,
    a.lockwait, a.machine,a.status,
    a.last_call_et,a.sql_hash_value,a.program
    from v$session a
    where nvl(a.username,'NULL')< >'NULL;

    insert into my_lock
    select id1, kaddr, sid, request,type
    from v$lock;

    insert into my_sqltext
    select hash_value , sql_text
    from v$sqltext s, my_session m
    where s.hash_value=m.sql_hash_value;

    column username format a10
    column machine format a15
    column last_call_et format 99999 heading "Seconds"
    column sid format 9999

    prompt "正在等待别人的用户"
    select a.sid, a.serial#,
    a.machine,a.last_call_et, a.username, b.id1
    from my_session a, my_lock b
    where a.lockwait = b.kaddr;

    prompt "被等待的用户"
    select a.sid, a.serial#,
    a. machine, a.last_call_et,a.username,
    b. b.type,a.status,b.id1
    from my_session a, my_lock b
    where b.id1 in
    (select distinct e.id1
    from my_session d, my_lock e
    where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and b.request=0;

    prompt "查出其 sql "
    select a.username, a.sid, a.serial#,
    b.id1, b.type, c.sql_text
    from my_session a, my_lock b, my_sqltext c
    where b.id1 in
    (select distinct e.id1
    from my_session d, my_lock e
    where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and b.request=0
    and c.hash_value =a.sql_hash_value;

    ---- 以上思路也可用于其它大型数据库系统如 Informix, Sybase,DB2中。通过使用该脚本,可以极大地提高获取系统中当前锁等待的情况,从而及时解决数据库应用系统中的锁等待问题。而且,由于实际上已取出其 program 名及相应的 sql 语句,故可以在事后将其记录下来,交给其开发人员进行分析并从根本上得到解决。

    [文章录入员:tonny]

    相关文章
  • 夺取月薪之冠-Oracle认证!
  • Oracle数据安全面面观(2)
  • Oracle数据安全面面观(1)
  • Oracle10g数据库的创建
  • Oracle新技术对Linux意味着什么?
  • 在Oracle里设置访问多个SQL Server
  • 深入分析Oracle数据库日志文件
  • 在ORACLE里用存储过程定期分割表
  • 用Linux完成Oracle自动物理备份
  • Oracle Rman/tivoli-TDP/下数据恢复到节点
  • 相关书籍:
  • 利用 DBCA建立 Oracle 9i 资料库
  • Oracle 数据库DBA管理手册
  • Oracle 管理员手册
  • Oracle 官方文档CHM合集-SQL参考手册
  • Oracle WebServer 中文手册
  • Oracle 9i 数据库管理员指南
  • Oracle 9i 数据库管理实务讲座
  • Oracle 9I 入门基础(第02部分)
  • Oracle 9I 入门基础(第01部分)
  • Oracle 9i 官方教材
  • 本站推荐内容

    近期主机类热搜关键词:
    美国服务器 美国服务器租用 海外服务器租用 国外服务器租用

    Oracle
    ACCESS
    MS SQL
    MySQL
    Oracle
    Foxpro
    PowerBuilder
    Sybase
    其它
    电脑教程阅读排行
    ·Oracle 常用技巧和脚本
    ·Oracle常用数据字典
    ·Oracle8 数据类型
    ·Oracle 8.1.6 for...
    ·Oracle 基本知识
    ·深入分析Oracle数据库日志文...
    ·安装Oracle后,经常使用的修...
    ·怎样快速查出Oracle数据库中...
    ·Oracle 8.0.4 for...
    ·Oracle10g数据库的创建