Oracle “buffer busy waits”

, ,

Buffer busy waits occur when multiple processes compete for access to specific blocks in the buffer cache of an Oracle database. In order to address this issue, it is essential to identify which blocks are causing contention and understand the reasons behind it. The most effective solution is to eliminate the root cause of the contention.

In many cases, buffer busy waits for data blocks occur because multiple processes are repeatedly accessing the same blocks. For example, if several users are scanning the same index, the first session quickly processes the blocks that are already in the buffer cache. However, when a block needs to be read from disk, other sessions scanning the same index catch up and also require that block. As a result, they have to wait for the buffer cache since another session is already reading the block from disk.

To mitigate buffer busy waits, it is important to analyze and optimize the access patterns to these blocks. This may involve techniques like tuning queries, optimizing indexing strategies, or reconsidering the nature of concurrent operations on the database. By resolving the contention and optimizing these operations, the buffer busy waits can be reduced, leading to improved performance and reduced wait times for accessing the buffer cache.

/0 Comments/by

Optimizing Oracle Net for Data Guard: Enhancing Network Throughput and Performance #JoelKallmanDay

, , , ,

Oracle Data Guard is a powerful, high-availability solution that ensures Oracle databases’ continuous availability and protection. Optimizing Oracle Net, which handles the network communication between the primary and standby systems, is essential to achieve optimal performance. This article will focus on optimizing Oracle Net for Data Guard by adjusting the TCP send and receive socket buffers, modifying session data unit (SDU) size, and disabling the TCP Nagle algorithm.

To achieve high network throughput, it is recommended to set the TCP send and receive socket buffers to the bandwidth-delay product (BDP) of the network link between the primary and standby systems. The BDP is calculated as the product of the network bandwidth and latency. For example, if the bandwidth is 622 Mbit/s per second and the latency is 30 ms, the minimum recommended size for the TCP socket buffers would be 6,997,500 bytes. This value can be set using the Oracle Net parameters RECV_BUF_SIZE and SEND_BUF_SIZE.

Oracle Net buffers data into session data units (SDUs) before sending them to the network layer. By default, the SDU size is set to 8192 bytes, which might be insufficient for Data Guard operations that involve sending redo data in larger chunks. To improve performance, increasing the SDU size to 64 KB is recommended. This ensures that Data Guard can send larger pieces of redo data without chopping them up and causing additional overhead.

To preempt delays in buffer flushing in the TCP protocol stack, it is advised to disable the TCP Nagle algorithm. This can be achieved by setting TCP.NODELAY to YES in the sqlnet.ora file on both the primary and standby systems. Disabling the Nagle algorithm helps reduce latency and improve the network communication’s responsiveness.

It is important to note that the actual values of the send and receive socket buffer sizes may be limited by the host operating system or memory constraints. The default values for these parameters are specific to the operating system. On Linux, for example, the default values are 128 KB for SEND_BUF_SIZE and 174,700 bytes for RECV_BUF_SIZE. These default values are often modified to higher when the oracle-database-preinstall-19c package is installed, but they are still lower than the recommended values for Data Guard.

To determine the default and maximum amount of receive and send socket memory, you can use the following commands on the Linux operating system:
– To check the receive socket memory:
# cat /proc/sys/net/core/rmem_default
# cat /proc/sys/net/core/rmem_max
– To check the send socket memory:
# cat /proc/sys/net/core/wmem_default
# cat /proc/sys/net/core/wmem_max

If the default values are lower than recommended, you can modify the /etc/sysctl.conf file. For example, you can set the maximum send and receive socket memory to 10 MB (10485760 bytes) with the following commands:
# echo ‘net.core.wmem_max=10485760’ >> /etc/sysctl.conf
# echo ‘net.core.rmem_max=10485760’ >> /etc/sysctl.conf

Additionally, you must set the socket memory’s minimum, initial, and maximum sizes in bytes. Use the following commands:
# echo ‘net.ipv4.tcp_rmem=10240 131072 10485760’ >> /etc/sysctl.conf
# echo ‘net.ipv4.tcp_wmem=10240 131072 10485760’ >> /etc/sysctl.conf

Once the changes are made, reload the modified settings with the command:
# sysctl -p

It is worth mentioning that starting from Oracle Database 12c Release 1 (12.1), Oracle Net supports larger session data unit (SDU) sizes, with an upper limit of 2 MB. The larger SDU size can benefit networks with high bandwidth-delay products and ample host resources, allowing for better utilization of the available network bandwidth. However, the recommended SDU size for Data Guard is 64 KB. This recommendation is subject to change based on ongoing testing and future advancements.

By optimizing Oracle Net for Data Guard, you can significantly improve the performance and efficiency of your Data Guard configuration. Implementing the recommended settings for TCP socket buffers SDU size, and disabling the TCP Nagle algorithm will ensure smoother and faster network communication between the primary and standby systems, ultimately enhancing your Oracle databases’ overall reliability and availability.

For more information, see:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/index.html

https://education.oracle.com/oracle-database-19c-data-guard-administration-workshop/courP_86809290

 

 

/0 Comments/by

Oracle Database QoS Management Recommendations

, , , , , , ,

If Performance Objectives are not being met, Quality of Service Management makes a recommendation. Each recommendation focuses on improving the highest-ranked Performance Class by exceeding its Performance Objective. Submissions may include changing consumer group mappings – and reprioritizing work within existing resource boundaries. For example, changing consumer group mappings may involve promoting a specific workload to get a more significant share of resources, or demoting a competing workload to make additional resources available to the target Performance Class. Another recommendation is to move servers between server pools and reprioritize resources between them to meet workload demands; so effectively, taking a node out of one pool and adding it to another pool gives more resources to the Performance Class running in that pool. And another recommendation is moving CPUs between databases within a server pool – reprioritize CPU resources within existing server pool boundaries. And this is called instance caging, where the CPU count parameter is set to limit the amount of CPUs an instance can use on a node.

The Quality of Service Management recommendations to improve the performance of a particular Performance Class adds more of the bottleneck resource – such as CPU time – for that Performance Class, making the bottleneck resource available more quickly to work requests in the Performance Class. Adding more resources to a Performance Class that is not performing well means taking resources away from another Performance Class. The Performance Class where the resources are removed should be less business-critical than the one being helped. So overall, the reallocation of resources should be beneficial to the business. When generating recommendations, Quality of Service Management evaluates the impact of the system performance as a whole. For example, suppose the improvement for one Performance Class is rather tiny, but the adverse effects on another Performance Class are significant. In that case, Quality of Service Management might report that the performance gain is too small and not recommended. If there is more than one way to resolve the bottleneck, Quality of Service Management advises the best overall recommendation. It is invariable, such as the calculated impact on all the Performance Classes and the predicted disruption and settling time associated with the action. And using Oracle Enterprise Manager, you can view the current and the alternative recommendations. Performance data is sent to Oracle Enterprise Manager for display on the Quality of Service Management Dashboard and Performance History pages. By default, Oracle Database QoS Management does not automatically implement recommendations. Instead, it suggests improving performance, which the administrator must then implement by clicking the Implement button. From version 12.1.0.2, Quality of Service Management allows you to specify authorized automatic actions that it can implement without the intervention of an administrator.

/0 Comments/by

Improving instance recovery performance

, , , , ,

You query V$INSTANCE_RECOVERY view and consistently receive an OPTIMAL_LOGFILE_SIZE value that is greater than the size of your smallest online redo log file. The OPTIMAL_LOGFILE_SIZE column of the V$INSTANCE_RECOVERY view can be used to determine the appropriate size for all of the online redo log files in your database. If the value of the OPTIMAL_LOGFILE_SIZE column is greater than the size of your smallest online redo log file, you should change the size of all online redo log files to be at least this value. In addition, the FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. After adjusting the size of your online redo log files, you may be able to adjust the value of this initialization parameter for better performance. This is done by rerunning the MTTR advisor after changing the size of your online redo log file to achieve more optimal results. However, running the MTTR advisor is not the best option in this situation for improving instance recovery performance.

 

/0 Comments/by

How to create a container database (CDB) resource plan

, , , , , , , ,

You use the DBMS_RESOURCE_MANAGER package to create a CDB resource plan and define the directives for the plan. Then, from the root container of your CDB connects as the SYS user. Then, create a pending area using the CREATE_PENDING_AREA procedure. After the pending area has been completed, you use the CREATE_CDB_PLAN procedure to create the CDB resource plan. Next, create the CDB resource plan directives for the PDBs using the CREATE_CDB_PLAN_DIRECTIVE procedure. Each directive specifies how resources are allocated to a specific PDB. Finally, you validate the pending area and then submit it. This is done using the VALIDATE_PENDING_AREA and SUBMIT_PENDING_AREA procedures, respectively.

 

/0 Comments/by