oracle 很长的 SQL 连接打开时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2364588/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Very long SQL connection opening time
提问by user284534
Recently, We have migrated our Oracle 9.2.0.6 production database from Windows 32bit RAC (2 nodes) to Linux 64 bit environment. Linux 64 bit environment has 8 times more memory than Windows 32 bit environment and Linux 64 bit has faster disk ( RAID 10 compare to RAID 5 of windows).
最近，我们将我们的 Oracle 9.2.0.6 生产数据库从 Windows 32 位 RAC（2 个节点）迁移到 Linux 64 位环境。Linux 64 位环境的内存是 Windows 32 位环境的 8 倍，Linux 64 位的磁盘速度更快（RAID 10 与 Windows 的 RAID 5 相比）。
However, after this migration, we have noticed sql connection time has been increased significantly. Some time, it is taking 16 seconds to open a connection. We have used different driver (JDBC, ODBC, OCI) and SQLPlus. But, we have noticed no difference in sql connection time. We have investigated network, but noticed no problem with network.
但是，在这次迁移之后，我们注意到 sql 连接时间显着增加。有时，打开连接需要 16 秒。我们使用了不同的驱动程序（JDBC、ODBC、OCI）和 SQLPlus。但是，我们注意到 sql 连接时间没有区别。我们调查了网络，但发现网络没有问题。
Some time, it is taking around 16 seconds to open a single connection. This database has around 50000 user schema.
有时，打开一个连接大约需要 16 秒。这个数据库有大约 50000 个用户架构。
Can anybody tell me what can be done to reduce the connection opening time? Your help would be appreciated.
谁能告诉我可以做些什么来减少连接打开时间？您的帮助将不胜感激。
回答by Eric Guo
if suddenly the tnsping is become very slow(means it's a all right system before), but ping to the same database serveris fast, The most possible reason is the listener.log file is too large:
如果突然tnsping变得很慢（意味着它之前是一个很好的系统），但ping到同一个数据库服务器很快，最可能的原因是listener.log文件太大：
$ORACLE_HOME/network/log/listener.log
$ORACLE_HOME/network/log/listener.log
Some OS (SunOS in my experience) will meet writing to a very large text file performance issue(exceed 4G?), so tail/clean the log file will immediately solve the tnsping takes very long time problem.
某些操作系统（根据我的经验是 SunOS）会遇到写入一个非常大的文本文件的性能问题（超过 4G？），因此尾/清理日志文件将立即解决 tnsping 需要很长时间的问题。
回答by Zeks
It was DNS in our case. After our admin commented out all DNS entries on the side of the server the system started responding without delay.
在我们的例子中是 DNS。在我们的管理员注释掉服务器端的所有 DNS 条目后，系统立即开始响应。
回答by Andreas Kl?ber
I also experienced very slow connection establishments with SQL Developer 4.1.5.21 on Mac OS against Oracle 12.1.0.2.0.
我还遇到了在 Mac OS 上使用 SQL Developer 4.1.5.21 与 Oracle 12.1.0.2.0 建立连接非常缓慢的情况。
To analyze this I installed Oracle Instant Clientto check connectivity directly with sqlplus. When I tried to connect with sqlplusit gave me the following error:
为了分析这一点，我安装了Oracle Instant Client以直接检查与sqlplus. 当我尝试与sqlplus它连接时，出现以下错误：
$ sqlplus <USER>/<PASSWORD>@<HOST>:<PORT>/<SERVICE>
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 10:50:44 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-21561: OID generation failed
I then found blog entry Oracle ORA-21561 : OID generation failedand added the hostname (as returned by the hostnamecommand) to the 127.0.0.1-line in /etc/hosts:
然后我发现博客条目Oracle ORA-21561 : OID generation failed并将主机名（由hostname命令返回）添加到127.0.0.1-line 中/etc/hosts：
127.0.0.1 localhost MacBook-Pro.local
This fixed the ORA-21561 error in sqlplusas well as the slow connection establishment in SQL Developer.
这修复了sqlplusSQL Developer 中的 ORA-21561 错误以及连接建立缓慢。
回答by RC.
There are many possible reasons for this, and without more information its difficult to determine the root cause.
这有很多可能的原因，如果没有更多信息，很难确定根本原因。
Having said that, check your sqlnet.ora file located on the machine your connecting from (i.e. <oracle_home>/network/admin/sqlnet.ora) and see if you have a line such as:
话虽如此，请检查您连接的机器上的 sqlnet.ora 文件（即 <oracle_home>/network/admin/sqlnet.ora），看看您是否有这样的一行：
SQLNET.AUTHENTICATION_SERVICES= (NTS)
If so, try changing it to:
如果是这样，请尝试将其更改为：
SQLNET.AUTHENTICATION_SERVICES= (NONE)
Save and retry your connection via SQLPlus.
通过 SQLPlus 保存并重试连接。
Also, check the value you have set for NAMES.DIRECTORY_PATH. Make sure the method you're using to connect to your instance is the first in the list. For example, we use TNSNAMES and ours reads:
此外，请检查您为 设置的值NAMES.DIRECTORY_PATH。确保您用于连接到实例的方法是列表中的第一个。例如，我们使用 TNSNAMES 并且我们的读取：
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Hope this helps.
希望这可以帮助。
回答by DCookie
Things that might indicate problem areas:
可能表明问题区域的事情：
From the client:
来自客户：
- are all clients exhibiting the problem?
- is ping serverreasonably fast?
- is tnsping dbnameslow?
- can you connect normally with telnet?
- 是不是所有的客户都出现这个问题？
- ping服务器是否相当快？
- tnsping dbname慢吗？
- telnet可以正常连接吗？
From the server:
从服务器：
- is ping clientreasonably fast?
- is nslookup of client by name and IP fast?
- is tnsping dbnameslow?
- is sqlplus user@dbname fast or slow?
- is a direct connection on server fast? I.E., export ORACLE_SID=db;sqlplus user/pw
- ping客户端是否相当快？
- 通过名称和 IP 快速对客户端进行 nslookup 吗？
- tnsping dbname慢吗？
- sqlplus user@dbname 是快还是慢？
- 服务器上的直接连接速度快吗？IE, export ORACLE_SID=db;sqlplus user/pw
