[20180124] Strange SQL * Net message from dblink.txt,20180124dblink.txt

[20180124] Strange SQL * Net message from dblink.txt,20180124dblink.txt

[20180124] Strange SQL * Net message from dblink.txt

-- // There are a large number of SQL * Net message from dblink events in the production system. Let's analyze them.

1. Environment:
-- // This environment is where servers are located. Sometimes dblink is used to access the general hospital database.

Xxxx> @ ver1
PORT_STRING VERSION BANNER
----------------------------------------------------------------------------------------------------------------------------
X86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

Xxxx> @ ev_name. SQL "SQL * Net message from dblink"
EVENT # EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS # WAIT_CLASS
-----------------------------------------------------------------------------------------------------------
356 4093028837 SQL * Net message from dblink driver id # bytes 2000153315 7 Network

-- // The P2 parameter indicates the number of transmitted bytes. I found a strange phenomenon:

Xxxx> SELECT * from v $ ACTIVE_SESSION_HISTORY WHERE event = 'SQL * Net message from dblink' AND p2> 1;
No rows selected

Xxxx> SELECT count (*) from v $ ACTIVE_SESSION_HISTORY WHERE event = 'SQL * Net message from dblink' AND p2 = 1;
COUNT (*)
----------
181068

-- // If all the errors occur are in 1 byte, the SQL * Net message from dblink event will not occur during such transmission. Although the branch and General Institute lease the telecom line,
-- // The bandwidth should be at least 10 MB. I wanted to contact the network administrator for the traffic diagram at the network exit. I was not expected to monitor it. However, if the transmission volume is so small, the network should be less problematic.

-- // Ping the branch server from a machine on the Intranet of the General Institute.
# Ping-s 9000 branch Server
...
9 packets transmitted, 9 bytes ed, 0% packet loss, time 8007 ms
Rtt min/avg/max/mdev = 1.034/1.255/2.776/0.539 MS

# Ping-s 9000 General Hospital Server
10 packets transmitted, 10 bytes ed, 0% packet loss, time 9000 ms
Rtt min/avg/max/mdev = 0.303/0.342/0.402/0.039 MS

-- // There should be no problem between the Intranet and the Intranet within 1 ms, but I will execute the following:

/* Formatted on 9:24:37 (QP5 v5.269.14213.20.69 )*/
SELECT machine
, Port
, Session_id
, Session_serial #
, MIN (sample_time) MIN
, MAX (sample_time) MAX
, MAX (sample_time)-MIN (sample_time) delta
From v $ ACTIVE_SESSION_HISTORY
WHERE event = 'SQL * Net message from dblink'
Group by machine
, Port
, Session_id
, Session_serial #
Order by 5 DESC;

-- // I found a unique rule that most delta intervals are + 00 00:15:24. 369000, with a difference of 1 second. 15*60 + 25 = 925. In addition, I use the following query:

SELECT SQL _id, count (*) from v $ ACTIVE_SESSION_HISTORY WHERE event = 'SQL * Net message from dblink' group by SQL _id order by 2 desc;

-- // Check the SQL statement and find that the first few are out of the dblink access. Some of the statements are executed as follows:
Select sysdate from dual;

-- // It is obvious that a problem occurs in the dblink link.

2. Analysis:
-- // Analysis I am already at the link:
Http://blog.itpub.net/267265/viewspace-2150434/
Http://blog.itpub.net/267265/viewspace-2150431/

-- // The problem is that the SQLNET. EXPIRE_TIME parameter is set on the general hospital server to regularly monitor the connection between the server and the client. The dblink connection is monitored here.
-- // However, the state firewall is configured between the General Hospital and the branch, so that such a package is not allowed to pass, so that the test connection initiated by the general hospital server is blocked, and according to the previous test
-- // Once a test package is sent, the connection to the dblink fails. In this way, if your application only accesses the server database of the branch, no problem will occur.
-- // The link is open, but it will be suspended once the transaction is committed (note that using dblink will produce a small amount of redo). This is the problem we encountered in our current production system.

-- // Note that using dblink will generate transactions. Many developers do not know how to disable dblink for database sessions. For more information, see the link.
-- // Http://blog.itpub.net/267265/viewspace-2123710/
SCOTT @ book> @ & r/ver1

PORT_STRING VERSION BANNER
----------------------------------------------------------------------------------------------------------------------------
X86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

SCOTT @ book> @ & r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------

No rows selected

SCOTT @ book> select sysdate from dual @ loopback;
SYSDATE
-------------------
09:43:27

SCOTT @ book> @ & r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.21.000078

-- // Note xid. SQL script:
Select dbms_transaction.local_transaction_id () XIDUSN_XIDSLOT_XIDSQN from dual;

-- // A special case. If you open the database as read-only on the dg, dblink is no problem under 11 GB (10 Gb may have problems ), however, if the SQL statement involves two dblink links
-- // Answer, this problem can refer to my previous link: http://blog.itpub.net/267265/viewspace-2138879/

-- // This time is about 925 seconds, link http://blog.itpub.net/267265/viewspace-2150434/,http://blog.itpub.net/267265/viewspace-2150431/ with my front
-- // It is consistent. Add 1 point. The last test time is less than 120 in the previous test.

3. Access solution:
-- // The best method is to cancel the State firewall settings. This is the best solution.
-- // Or add the SQLNET. EXPIRE_TIME parameter to avoid server-initiated monitoring. However, this parameter is meaningless.
-- // After accessing dblink, it is better to add a commit even if select is used. in this way, although the dblink connection will not be disconnected, at least some applications will not make errors. because some applications submit transactions generated by accessing dblink.
-- // If a problem occurs in dblink, the user's operations will also be suspended and rollback will be dropped.

-- // Finally, I once again admire our users. Even this kind of problem can be tolerated. I only pay attention to this issue if someone else tells me this way off work.
-- // Even more admired our team. This problem has existed for so long, and our database team has put the problem on the Internet, so no one has calmed down and thought about solving the problem. disappointed ....

4. I also repeat the test myself:
Xxxx> select sysdate from dual @ xhlis;
SYSDATE
-------------------
10:05:52

Xxxx> host sleep 900

Xxxx>
Xxxx> select sysdate from dual;
SYSDATE
-------------------
10:23:39

-- // Accessible locally. If you execute dml to submit a problem

Xxxx> commit;
-- // Suspend...