一般来说,这类错误是因为在包或过程被频繁调用的过程中,DDL语句引起的,那我们该怎么处理呢?其实我们可以这样来操作来查原因,老板要的一般都是为什么产生这个故障和谁操作导致的这个故障,特别是故障处理好后,这个问题就要回答老板了。
1、预先在数据库中建立DDL级的触发器,我认为这个是必要的,因为这个对生产影响不大,但是却可以让我们监控到不少有用的信息.,比如记录在abc表中,可以记录登陆用户,操作语句,操作时间等等信息。
2、在数据库中出现大量的libriary cache lock 的等待事件的时候,系统出现严重的问题了,我们可以立即从这个时间点左右着手,比如12日21日中午12点到12点半之间出问题,如下语句
select * from dba_objects where
last_ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND last_ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and (object_type like '%PACK%' or object_type like 'FUNCTION' OR object_type='PROCEDURE')
AND STATUS='INVALID'
order by last_ddl_time desc
其实通过这个基本上就发现是什么问题了,基本上就只会有一两个对象比如包BBB失效
3、然后找包关联的对象,是否在我们的触发器记录的表中有记录,接着执行如下语句(切记,这个记录DDL动作的语句发挥作用了)
select * from abc where ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and schema_object in (SELECT referenced_name FROM DBA_DEPENDENCIES WHERE NAME='BBB' )
ORDER BY DDL_time desc
(请注意,这个BBB就是上面我查出来的,举例说比如失效的包)
这样查出来的,绝对就是引起这次事故的罪魁祸首的动作了。(ddl_time和 schema_object 是abc表的字段,记录了登陆者操作DDL的时间和对象)
当 然我上面并没有说明解决问题的方法,解决问题的方法是如下。但是有的时候发现问题原因,追究问题原因是非常非常重要的,可以避免下次再发生,当然通过 DUMP systemstate等方式,比较复杂,我的这个思路操作起来应该比较简便,很明了。另外,建立DDL级的触发器,个人认为是必须的!所以上面的方法我 想说出来,希望对大家有用!
解决问题的方法步骤
1、查看具体产生library cache lock 的对象,比如不哪些包和存储过程
SELECT KGLNAOWN,KGLNAOBJ
FROM x$kglob
WHERE kglhdadr in( select P1RAW from v$session_wait where event like 'library cache%');
2、 查看具体是那些用户做了这个操作导致 library cache lock
select sid, program ,machine from v$session where paddr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in ('library cache pin','library cache lock' ,'library cache load lock') ) );
3、、以下语句用来杀掉会话(前面查看,然后到这步是决定是否要杀掉进程解决这个问题)
select 'kill -9 '||spid from v$process where addr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr --AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in (' library cache pin','library cache lock' ) ) );
附:DDL触发器的语句
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
SQL_TEXT ORA_NAME_LIST_T;
STATE_SQL VARCHAR2(4000); --DDL$TRACE.DDL_SQL%TYPE;
V_ERR_INFO VARCHAR2(200);
BEGIN
FOR I IN 1 .. ORA_SQL_TXT(SQL_TEXT) LOOP
STATE_SQL := STATE_SQL || SQL_TEXT(I);
END LOOP;
INSERT INTO SYSTEM.ABC
(LOGIN_USER,
AUDSID,
IPADDRESS,
SCHEMA_USER,
SCHEMA_OBJECT,
DDL_TIME,
DDL_SQL)
VALUES
(ORA_LOGIN_USER,
USERENV('SESSIONID'),
SYS_CONTEXT('userenv', 'ip_address'),
ORA_DICT_OBJ_OWNER,
ORA_DICT_OBJ_NAME,
SYSDATE,
STATE_SQL);
EXCEPTION
WHEN OTHERS THEN
V_ERR_INFO := SUBSTRB(SQLERRM, 1, 198);
END TR_TRACE_DDL;
3、、以下语句用来杀掉会话(前面查看,然后到这步是决定是否要杀掉进程解决这个问题)
目的找出spid ,也可以用下面的sql:
SELECT spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.SID IN (SELECT sid
FROM v$session_wait b
WHERE b.EVENT in (' library cache pin','library cache lock' ) )
前提是sesion 没有被 killed 掉, 如果被killed , v$session.paddr <> v$process.addr.
原文地址 http://www.itpub.net/thread-915008-1-4.html
select event,count(*) from v$session_wait group by event;
SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status, c.piece, c.sql_text
FROM v$session a, v$process b, v$sqltext c
WHERE b.addr=a.paddr AND a.sql_address=c.address(+)
and a.sid in (select sid from v$session_wait where event = 'db file sequential read')
and a.sid =2646
ORDER BY a.sid,c.piece;
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event like '%library cache lock%'
and s.paddr=o.addr;
1、预先在数据库中建立DDL级的触发器,我认为这个是必要的,因为这个对生产影响不大,但是却可以让我们监控到不少有用的信息.,比如记录在abc表中,可以记录登陆用户,操作语句,操作时间等等信息。
2、在数据库中出现大量的libriary cache lock 的等待事件的时候,系统出现严重的问题了,我们可以立即从这个时间点左右着手,比如12日21日中午12点到12点半之间出问题,如下语句
select * from dba_objects where
last_ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND last_ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and (object_type like '%PACK%' or object_type like 'FUNCTION' OR object_type='PROCEDURE')
AND STATUS='INVALID'
order by last_ddl_time desc
其实通过这个基本上就发现是什么问题了,基本上就只会有一两个对象比如包BBB失效
3、然后找包关联的对象,是否在我们的触发器记录的表中有记录,接着执行如下语句(切记,这个记录DDL动作的语句发挥作用了)
select * from abc where ddl_time>to_date('20071221 12:00:00','yyyymmdd hh24:mi:ss')
AND ddl_time<=to_date('200712 12:30:00','yyyymmdd hh24:mi:ss')
and schema_object in (SELECT referenced_name FROM DBA_DEPENDENCIES WHERE NAME='BBB' )
ORDER BY DDL_time desc
(请注意,这个BBB就是上面我查出来的,举例说比如失效的包)
这样查出来的,绝对就是引起这次事故的罪魁祸首的动作了。(ddl_time和 schema_object 是abc表的字段,记录了登陆者操作DDL的时间和对象)
当 然我上面并没有说明解决问题的方法,解决问题的方法是如下。但是有的时候发现问题原因,追究问题原因是非常非常重要的,可以避免下次再发生,当然通过 DUMP systemstate等方式,比较复杂,我的这个思路操作起来应该比较简便,很明了。另外,建立DDL级的触发器,个人认为是必须的!所以上面的方法我 想说出来,希望对大家有用!
解决问题的方法步骤
1、查看具体产生library cache lock 的对象,比如不哪些包和存储过程
SELECT KGLNAOWN,KGLNAOBJ
FROM x$kglob
WHERE kglhdadr in( select P1RAW from v$session_wait where event like 'library cache%');
2、 查看具体是那些用户做了这个操作导致 library cache lock
select sid, program ,machine from v$session where paddr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr(+) AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in ('library cache pin','library cache lock' ,'library cache load lock') ) );
3、、以下语句用来杀掉会话(前面查看,然后到这步是决定是否要杀掉进程解决这个问题)
select 'kill -9 '||spid from v$process where addr in (
SELECT s.paddr
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr --AND p.kglpnmod <> 0
and kglpnhdl in ( select p1raw from v$session_wait where event in (' library cache pin','library cache lock' ) ) );
附:DDL触发器的语句
CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
SQL_TEXT ORA_NAME_LIST_T;
STATE_SQL VARCHAR2(4000); --DDL$TRACE.DDL_SQL%TYPE;
V_ERR_INFO VARCHAR2(200);
BEGIN
FOR I IN 1 .. ORA_SQL_TXT(SQL_TEXT) LOOP
STATE_SQL := STATE_SQL || SQL_TEXT(I);
END LOOP;
INSERT INTO SYSTEM.ABC
(LOGIN_USER,
AUDSID,
IPADDRESS,
SCHEMA_USER,
SCHEMA_OBJECT,
DDL_TIME,
DDL_SQL)
VALUES
(ORA_LOGIN_USER,
USERENV('SESSIONID'),
SYS_CONTEXT('userenv', 'ip_address'),
ORA_DICT_OBJ_OWNER,
ORA_DICT_OBJ_NAME,
SYSDATE,
STATE_SQL);
EXCEPTION
WHEN OTHERS THEN
V_ERR_INFO := SUBSTRB(SQLERRM, 1, 198);
END TR_TRACE_DDL;
3、、以下语句用来杀掉会话(前面查看,然后到这步是决定是否要杀掉进程解决这个问题)
目的找出spid ,也可以用下面的sql:
SELECT spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.SID IN (SELECT sid
FROM v$session_wait b
WHERE b.EVENT in (' library cache pin','library cache lock' ) )
前提是sesion 没有被 killed 掉, 如果被killed , v$session.paddr <> v$process.addr.
原文地址 http://www.itpub.net/thread-915008-1-4.html
select event,count(*) from v$session_wait group by event;
SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status, c.piece, c.sql_text
FROM v$session a, v$process b, v$sqltext c
WHERE b.addr=a.paddr AND a.sql_address=c.address(+)
and a.sid in (select sid from v$session_wait where event = 'db file sequential read')
and a.sid =2646
ORDER BY a.sid,c.piece;
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event like '%library cache lock%'
and s.paddr=o.addr;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11976525/viewspace-702532/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11976525/viewspace-702532/