不了解,不过把metalink上的贴一下 Troubleshooting an 'ORA-02049: timeout: distributed transaction waiting for lock' Error: How to Obtain a System State Trace BEFORE the Error Occurs, While Still Experiencing the Contention 文档 ID: 789517.1 类型: HOWTO 上次修订日期: 17-AUG-2009 状态: PUBLISHED In this Document Goal Solution--------------------------------------------------------------------------------Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 Information in this document applies to any platform. Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 Goal This document is intended to explain under what conditions an ORA-02049 'timeout: distributed transaction waiting for lock' error can be signaled, and to provide a tool to diagnose situations when this occurs. The challenge with troubleshooting these types of errors is that by the time the error is thrown, the contention no longer is present. A type of trace used by Oracle Support called a system state dump is useful for finding the session holding the resources that the session(s) signaling ORA-02049 are waiting on, but it must be obtained before the error is signaled. Therefore the contention cannot be captured even by cascading a system state trace off of an ORA-02049 errorstack trace, which is automatically written when the ORA-02049 error occurs.An ORA-02049 'timeout: distributed transaction waiting for lock' error occurs when a session that is involved in a distributed transaction waits for another session's TX enqueue (row lock) for longer than the database distributed_lock_timeout. What constitutes a 'distributed transaction' is what generates confusion on this topic. Any operation that uses a database link - even a query - starts a distributed transaction. For example, consider the following: Session 1: ----------- SQL> select * from global_name; GLOBAL_NAME --------------------------------------------------------------------------- ORCL102A.WORLD SQL> update scott.dept set loc='NOME' where deptno=10; 1 row updated. Session 2: ---------- SQL> select * from global_name; GLOBAL_NAME --------------------------------------------------------------------------- ORCL102A.WORLD SQL> select 1 from [email protected]; 1 ---------- 1SQL> update scott.dept set loc='NOME' where deptno=10; update scott.dept set loc='NOME' where deptno=10 * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock If a 'commit' is issued in session 2 after the distributed query but before the update operation, no ORA-02049 error is encountered. Common methods of dealing with this problem are: Increase the database initialization parameter distributed_lock_timeout. This will enable sessions involved in a distributed transaction to wait longer for a row lock, in hopes that the session holding the row lock will release it in the increased span of time. Code the application so that the error is a 'try again' exception - if it hits the error, keep trying the action until it succeeds. Eventually the session holding the contended row lock should release it and the waiting session should be able to proceed. However, it is a common desire for customers to want to identify the session holding the resources. The procedure presented below allows us to do this five seconds before an ORA-02049 will be signaled.Solution The procedure below monitors v$session for any sessions waiting on 'enq: TX - row lock contention' for seconds_in_wait. If seoonds_in_wait for a session waiting on 'enq: TX - row lock contention' advances to five seconds before distributed_lock_timeout will expire, and the session is involved in a distributed transaction, it will write a system state trace to background_dump_dest (10g) or diagnostic_dest (11g). It will stop running after either writing three system state traces, or the time specified for the procedure to run expires, which ever occurs first.To implement the procedure:1) To prepare for running the procedure, the schema it will be created in needs privileges to be able to select from v$ views, for creating and running procedures, etc. CREATE USER oerr IDENTIFIED BY oerr; GRANT CONNECT, RESOURCE, CREATE PROCEDURE, ALTER SESSION TO oerr; GRANT SELECT ON SYS.V_$SESSION TO oerr; GRANT SELECT ON SYS.V_$LOCK TO oerr; GRANT SELECT ON SYS.V_$PARAMETER TO oerr; GRANT EXECUTE ON DBMS_LOCK TO oerr; 2) Create the following table in the schema that owns the procedure. It allows the procedure to be terminated at any time. Otherwise the procedure must be killed administratively from another session.create table on_off (run varchar2(1)); insert into on_off values ('Y'); commit; To stop the procedure before the time specified for the procedure to run expires, execute:update on_off set run = 'N'; commit; 3) Create the procedure in the appropriate schema:CREATE OR REPLACE PROCEDURE OERR_2049_SSTATE (run_minutes number) IS v_start_date date := sysdate; dist_lock_timeout number; flag integer := 1; v_wait number; v_run on_off.run%type := 'Y'; cursor sess_wait is select sid, saddr, seconds_in_wait from v$session where event = 'enq: TX - row lock contention'; v_seconds_in_wait number; v_sid number; v_lock_type varchar2(2); NODATA BOOLEAN := FALSE; BEGIN execute immediate 'update on_off set run = ''Y'''; execute immediate 'commit'; dbms_output.put_line(' '); execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS'''; dbms_output.put_line('Procedure started at ' || v_start_date); select to_number(value) into dist_lock_timeout from v$parameter where name = 'distributed_lock_timeout'; dbms_output.put_line('Value of distributed_lock_timeout = ' || dist_lock_timeout); if dist_lock_timeout < 10 then dbms_output.put_line('Value of distributed_lock_timeout is too small, exiting...'); v_run := 'N'; else v_wait := dist_lock_timeout - 5; end if; while flag < 4 and sysdate - v_start_date < (run_minutes/1440) and v_run = 'Y' loop for r in sess_wait loop v_seconds_in_wait := r.seconds_in_wait; if v_seconds_in_wait > v_wait then v_sid := r.sid; begin select type into v_lock_type from v$lock where sid = v_sid and type = 'DX'; dbms_output.put_line('DX lock found...'); EXCEPTION WHEN NO_DATA_FOUND THEN NODATA := TRUE; end; if NODATA then NODATA := FALSE; end if; if v_lock_type = 'DX' then dbms_output.put_line('Dumping systemstate #' || flag || '...'); execute immediate 'alter session set tracefile_identifier=''SSTATE'''; execute immediate 'alter session set events ''immediate trace name SYSTEMSTATE level 10'''; flag := flag + 1; dbms_lock.sleep(10); end if; end if; end loop; select run into v_run from on_off; commit; end loop; dbms_output.put_line('Procedure ended at ' || sysdate); end; / 4) To run the procedure, specify the time in minutes for the procedure to run as input. For example, to run the procedure for 300 minutes (5 hours), execute:exec oerr_2049_sstate(300);The traces will have 'SSTATE' as part of the file name. As stated previously, the output will be written to background_dump_dest (10g) or diagnostic_dest (11g). The procedure will stop running after either writing three system state traces, or the time specified for the procedure to run expires, which ever occurs first. Keywords ENQUEUE ; DISTRIBUTED_LOCK_TIMEOUT ; DX ; ORA-02049
2.用户使用的高峰期和夜间数据库在使用的不同情形下分别对比试一下，可能的话，尽可能按排在夜间。
Troubleshooting an 'ORA-02049: timeout: distributed transaction waiting for lock' Error: How to Obtain a System State Trace BEFORE the Error Occurs, While Still Experiencing the Contention
文档 ID: 789517.1 类型: HOWTO
上次修订日期: 17-AUG-2009 状态: PUBLISHED In this Document
Goal
Solution--------------------------------------------------------------------------------Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7
Goal
This document is intended to explain under what conditions an ORA-02049 'timeout: distributed transaction waiting for lock' error can be signaled, and to provide a tool to diagnose situations when this occurs. The challenge with troubleshooting these types of errors is that by the time the error is thrown, the contention no longer is present. A type of trace used by Oracle Support called a system state dump is useful for finding the session holding the resources that the session(s) signaling ORA-02049 are waiting on, but it must be obtained before the error is signaled. Therefore the contention cannot be captured even by cascading a system state trace off of an ORA-02049 errorstack trace, which is automatically written when the ORA-02049 error occurs.An ORA-02049 'timeout: distributed transaction waiting for lock' error occurs when a session that is involved in a distributed transaction waits for another session's TX enqueue (row lock) for longer than the database distributed_lock_timeout. What constitutes a 'distributed transaction' is what generates confusion on this topic. Any operation that uses a database link - even a query - starts a distributed transaction. For example, consider the following: Session 1:
-----------
SQL> select * from global_name; GLOBAL_NAME
---------------------------------------------------------------------------
ORCL102A.WORLD SQL> update scott.dept set loc='NOME' where deptno=10; 1 row updated.
Session 2:
----------
SQL> select * from global_name; GLOBAL_NAME
---------------------------------------------------------------------------
ORCL102A.WORLD SQL> select 1 from [email protected]; 1
----------
1SQL> update scott.dept set loc='NOME' where deptno=10;
update scott.dept set loc='NOME' where deptno=10
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock If a 'commit' is issued in session 2 after the distributed query but before the update operation, no ORA-02049 error is encountered. Common methods of dealing with this problem are: Increase the database initialization parameter distributed_lock_timeout. This will enable sessions involved in a distributed transaction to wait longer for a row lock, in hopes that the session holding the row lock will release it in the increased span of time. Code the application so that the error is a 'try again' exception - if it hits the error, keep trying the action until it succeeds. Eventually the session holding the contended row lock should release it and the waiting session should be able to proceed. However, it is a common desire for customers to want to identify the session holding the resources. The procedure presented below allows us to do this five seconds before an ORA-02049 will be signaled.Solution
The procedure below monitors v$session for any sessions waiting on 'enq: TX - row lock contention' for seconds_in_wait. If seoonds_in_wait for a session waiting on 'enq: TX - row lock contention' advances to five seconds before distributed_lock_timeout will expire, and the session is involved in a distributed transaction, it will write a system state trace to background_dump_dest (10g) or diagnostic_dest (11g). It will stop running after either writing three system state traces, or the time specified for the procedure to run expires, which ever occurs first.To implement the procedure:1) To prepare for running the procedure, the schema it will be created in needs privileges to be able to select from v$ views, for creating and running procedures, etc.
CREATE USER oerr IDENTIFIED BY oerr;
GRANT CONNECT, RESOURCE, CREATE PROCEDURE, ALTER SESSION TO oerr;
GRANT SELECT ON SYS.V_$SESSION TO oerr;
GRANT SELECT ON SYS.V_$LOCK TO oerr;
GRANT SELECT ON SYS.V_$PARAMETER TO oerr;
GRANT EXECUTE ON DBMS_LOCK TO oerr; 2) Create the following table in the schema that owns the procedure. It allows the procedure to be terminated at any time. Otherwise the procedure must be killed administratively from another session.create table on_off (run varchar2(1));
insert into on_off values ('Y');
commit;
To stop the procedure before the time specified for the procedure to run expires, execute:update on_off set run = 'N';
commit;
3) Create the procedure in the appropriate schema:CREATE OR REPLACE PROCEDURE OERR_2049_SSTATE (run_minutes number)
IS v_start_date date := sysdate;
dist_lock_timeout number;
flag integer := 1;
v_wait number;
v_run on_off.run%type := 'Y'; cursor sess_wait is select sid, saddr, seconds_in_wait
from v$session where event = 'enq: TX - row lock contention'; v_seconds_in_wait number;
v_sid number;
v_lock_type varchar2(2);
NODATA BOOLEAN := FALSE; BEGIN execute immediate 'update on_off set run = ''Y''';
execute immediate 'commit'; dbms_output.put_line(' '); execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS''';
dbms_output.put_line('Procedure started at ' || v_start_date); select to_number(value) into dist_lock_timeout
from v$parameter where name = 'distributed_lock_timeout'; dbms_output.put_line('Value of distributed_lock_timeout = ' || dist_lock_timeout); if dist_lock_timeout < 10 then
dbms_output.put_line('Value of distributed_lock_timeout is too small, exiting...');
v_run := 'N';
else
v_wait := dist_lock_timeout - 5;
end if; while flag < 4 and sysdate - v_start_date < (run_minutes/1440) and v_run = 'Y' loop
for r in sess_wait loop
v_seconds_in_wait := r.seconds_in_wait;
if v_seconds_in_wait > v_wait then v_sid := r.sid;
begin
select type into v_lock_type from v$lock where sid = v_sid and type = 'DX';
dbms_output.put_line('DX lock found...');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NODATA := TRUE;
end;
if NODATA then
NODATA := FALSE;
end if; if v_lock_type = 'DX' then
dbms_output.put_line('Dumping systemstate #' || flag || '...');
execute immediate 'alter session set tracefile_identifier=''SSTATE''';
execute immediate 'alter session set events
''immediate trace name SYSTEMSTATE level 10''';
flag := flag + 1;
dbms_lock.sleep(10);
end if;
end if;
end loop;
select run into v_run from on_off;
commit;
end loop;
dbms_output.put_line('Procedure ended at ' || sysdate);
end;
/
4) To run the procedure, specify the time in minutes for the procedure to run as input. For example, to run the procedure for 300 minutes (5 hours), execute:exec oerr_2049_sstate(300);The traces will have 'SSTATE' as part of the file name. As stated previously, the output will be written to background_dump_dest (10g) or diagnostic_dest (11g). The procedure will stop running after either writing three system state traces, or the time specified for the procedure to run expires, which ever occurs first. Keywords
ENQUEUE ; DISTRIBUTED_LOCK_TIMEOUT ; DX ; ORA-02049