ANBOB™

A Logon storm is a sudden spike in the number of client connection requests. Logon storms can occur due to a variety of factors. They could be malicious like a DoS attack. Or they could occur due to administrative actions – such as a middle tier coming online.
The process of starting a database session has inherent CPU costs associated with it – from the creation of a new OS process to attaching to the SGA. Every system has a limit above which the spawning of new database sessions can starve existing sessions of CPU resources. This could result in many unexpected consequences including application timeouts, increased response times, un-responsiveness and other cascading effects.

Shell: analyze listener.log (分析oracle监听日志连接频率)

The connection process includes several steps. The first part involves the resolution of the connect descriptor. The next steps contributing to the time taken for Client connection include:
1. The time taken for the network protocol to connect the Client host to the Listener host
2. For the Listener to create either a dedicated server process or determine which Dispatcher is to be used
3. The time it takes for the Client process to complete the TNS connect to the Server process.

There are few places where database Listener can be optimized. In addition to the application-side connection storm, the connection performance of the database is mainly in the following two aspects:

• Slow or busy server
• Underlying Network

The next bottleneck the Listener could encounter is too many concurrent connection requests. The Listener process, it is constrained by CPU speed and available memory. there are techniques that can assist in scaling the Listener. The first is to adjust the request queue-size (QUEUESIZE) of the Listener process, the second is to increase the number of Listeners.

QUEUESIZE

The QUEUESIZE parameter represents the maximum possible number of concurrent connections the Listener may receive – this does not refer the number of requests per second the Listener may handle. The Listener queue-size is dependent on corresponding underlying network protocol parameters (e.g. SOMAXCONN, SNS_MAXCONN, TCP_CONN_REQ_MAX_Q, etc.) that define the maximum, concurrent connections per port or socket. Therefore, the Listener queue-size value cannot exceed the underlying protocol parameter value.

-- hpux ia
$ ndd -get /dev/tcp tcp_conn_request_max
4096

Oracle Net protocol adapters (e.g. IPC, TCP) maintain their own queue-sizes, for which the default values differ depending on protocol adapter, version and platform. For TCP, the default queue-size for Unix and Windows NT Workstations is in the order of 20, whereas on Windows NT Server/Enterprise it is 50. By analyzing the Listener log file, if the number of requests per second exceeds the queue-size values, increasing the queue-size recommended.

Sometimes a large volume of simultaneous connection requests from clients may overwhelm a listener service. To keep the listener from failing, you can use the QUEUESIZE parameter in the listener.ora configuration file to specify how many concurrent connection requests can be made.

The QUEUESIZE parameter determines the number of concurrent connection requests the Listener can store while it works to establish those connections.If the number of incoming requests exceeds the size of the buffer, the client requesting the connection will receive failures. Ideally, the size of this buffer should be equal to the maximum number of expected simultaneous connection requests.

LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NodeA)(PORT = 1521)(QUEUESIZE = 300))
)

* See Document 214122.1 on how to confirm queue size for TNS listener

MULTIPLE LISTENERS

The next method is to increase the number of Listener processes. This also has the advantage of increasing Listener availability. Many large systems have multiple CPU’s and the operating systems take up the responsibility of placing processes on each CPU. This increases the possibility for two Listener processes to act simultaneously, thus places an upper bound of twice the connect throughput then one Listener.

This is a very good solution when the system architecture consists of truly independent Clients. Client-side connection load balancing is covered in the Oracle Net Administrators Manual. This Client side configuration allows the Oracle Net Client to randomly choose between two or more possible Listener addresses. As long as these addresses represent independent Listener processes, then overloading a single Listener process is reduced.

Connection Rate Limiter

The Listener, in 11g, supports throttling of incoming connections. With the Connection Rate Limiter feature, a DBA can specify limits on the number of new connections handled by the listener. When a limit is specified, the Listener imposes an artificial limit on the number of new connections it handles every second. This slows down the rate of database session creation, which lowers the the amount of CPU resources used for servicing incoming connections.

The Connection Rate Limiter can be an effective tool in maintaining Quality –ofService guarantees during logon storms. With a suitable rate limit value, the Listener can mitigate some of the negative effects of a DoS attack no penalty under normal operating conditions.

RATE_LIMIT
This parameter indicates that a particular listening endpoint is rate limited.
This is specified in the ADDRESS section of the listener endpoint
configuration. For example,

LISTENER=(ADDRESS=
             (PROTOCOL=tcp)
             (HOST=sales-server)(PORT=1521)
             (RATE_LIMIT=yes)
         )

This parameter can be configured in two ways:
1. When the parameter is set to “yes”, the endpoint is included in the enforcement of a listener-wide connection rate. This has to be used in conjunction with the CONNECTION_RATE_LISTENER parameter.
2. When a value greater than 0 is specified, the rate limit is enforced at the endpoint level.

Listener logfile disk filesystem busy or slow io

try to tun off listener log

lsnrctl> set cur xxx
lsnrctl >set log_status off

or change logfile location for oracle 11g+

# Add two parameters in listener.ora.
DIAG_ADR_ENABLED_LISTENER=OFF.
LOG_DIRECTORY_LISTENER=/newpath/logfile/trace
# reload the listener: 
$ lsnrctl reload 
--
if DIAG_ADR_ENABLED_LISTENER=OFF. then you can using the flowing command change  logfile localtion or name online,
No longer need to reload listener, to avoid short-term listener without DB service.

lsnrctl> set LOG_DIRECTORY 'logfile location e.g. /log/listeners'
lsnrctl> set log_file  'logfile name e.g. listener.log1'

FAQ:

1, “I’m using TNSping to test my network performance and…”

TNSping should never be used to test network performance. TNSping’s only function is to send a Connect Packet (NSPTCN) to the listener; the listener replies with a Refuse Packet (NSPTRF) and a round trip time is computed.

2, SQL*Net query performance is slow but TCP/IP PING is fast, therefore SQL*Net is slow.”

This is a false statement. Ping only uses the ICMP/IP protocol (not the TCP protocol) to test whether another host is reachable. Since the TCP protocol is not used, a socket is not created, therefore data is not transferred between 2 nodes (except for 8+[optional] bytes needed for the echo request, but again, that is not TCP data), and none of the rules of TCP are being applied. If Ping is fast, that only means that IP was able to quickly find a route to the destination server.

others

Setting the SDU Size for the Database

you can set the SDU size for the database server or client,Oracle Net encapsulates data into buffers the size of the session data unit (SDU) before sending the data across the network. Oracle Net sends each buffer when it is filled, flushed, or when an application tries to read data. Adjusting the size of the SDU buffers relative to the amount of data provided to Oracle Net to send at any one time can improve performance, network utilization, and memory consumption. When large amounts of data are being transmitted, increasing the SDU size can improve performance and network throughput. more Configuring Session Data Unit

Configuring I/O Buffer Space

Reliable network protocols, such as TCP/IP, buffer data into send and receive buffers while sending and receiving to or from lower and upper layer protocols. The sizes of these buffers affect network performance by influencing flow control decisions.

References Oracle Net “Connect Time” Performance Tuning (文档 ID 214022.1)

打赏

微信扫一扫，打赏作者吧～