Hello Everyone,
in our production database when we commit a transaction we call a remote procedure over dblink.
usually the call succeeds ,but every now and then a couple of sessions hang up,
when I use the session browser of Toad I notice that these sessions are waiting with the event SQL*Net more data to dblink
below are some queries and their results:
select sid,event,wait_class,wait_time,seconds_in_wait,state from gv$session_wait where sid=225
rslt:
225 SQL*Net more data to dblink Network -1 18279 WAITED SHORT TIME
select * from gv$session_wait_history where sid=225
rslt:
INST_ID SID SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO
2 225 1 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8144 0 0 8 41
2 225 2 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8143 0 0 13 39
2 225 3 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8149 0 0 7 37
2 225 4 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8145 0 0 8 40
2 225 5 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8145 0 1 11394 37
2 225 6 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8143 0 0 7 37
2 225 7 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8145 0 0 7 36
2 225 8 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8138 0 0 8 37
2 225 9 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8149 0 0 8 38
2 225 10 344 SQL*Net more data to dblink driver id 1413697536 #bytes 8149 0 1 11476 37
I'm not sure but from the above results ,is it safe to conclude that I get stuck because I am caught in infinite loop trying to write to dblink?
additional notes:
<li>some times when I look at the current statement I find that the statement is a query or insert into a local table.
<li>there were some network outages.
<li>when viewing the database log files I found:
Error 3135 trapped in 2PC on transaction 7.6.306086. Cleaning up.
Error stack returned to user:
ORA-03135: connection lost contact
ORA-02063: preceding line from MPF//where MPF is the name of dblink
even though we use the DBLink to execute the procedure only without any changes on the remote DB, and we don't use 2PC.
<li> the local DB is a RAC
select * from dba_blockers
rslt:
no rows
select * from dba_waiters
rslt:
no rows
select * from gv$lock where sid=225
rslt:
INST_ID ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
2 0000000199D54F60 0000000199D54FB8 225 AE 100 0 4 0 20152 2
2 000000018EA18108 000000018EA18180 225 TX 1114138 251539 6 0 19654 2
select * from gv$session where sid=225
rslt:
INST_ID SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE PORT TERMINAL PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER PREV_EXEC_START PREV_EXEC_ID PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# TOP_LEVEL_CALL# LOGON_TIME LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_SESSION_STATUS FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION SEQ# EVENT# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO SERVICE_NAME SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS SQL_TRACE_PLAN_STATS SESSION_EDITION_ID CREATOR_ADDR CREATOR_SERIAL# ECID
2 00000001993E4F58 225 445 1353611 0000000198E2FA10 198 <schema> 47 2147483644 000000018EA18108 ACTIVE DEDICATED 198 <schema> oracle 1234 <cluster name> 49993 unknown JDBC Thin Client USER 00000001968A1250 3198676106 72y8ztfzagv4a 2 02/04/2013 11:18:22 ص 33554852 00000001968A18E0 3992616824 03mm4u3qznzvs 0 02/04/2013 11:18:22 ص 33554730 158207 1 158207 1 JDBC Thin Client 2546894660 0 12206 122409 8 49354 0 94 02/04/2013 10:53:20 ص 19559 NO NONE NONE NO DISABLED ENABLED ENABLED 0 NOT IN WAIT NOT IN WAIT 42844 344 SQL*Net more data to dblink driver id 1413697536 0000000054435000 #bytes 8144 0000000000001FD0 0 00 2000153315 7 Network -1 19553 WAITED SHORT TIME 8 19553325216 SYS$USERS DISABLED FALSE FALSE FIRST EXEC 100 0000000198E2FA10 2 004qLk^iPyp0bqw5wFDCiW0002fR000B^f