IBM AIX系统上的Oracle数据库调优指南

需积分: 9 192 浏览量更新于2024-08-01 收藏 638KB PDF 举报

"Oracle_tuning_AIX_Jan09.pdf 是一份关于在IBM AIX 5.3和6.1操作系统上优化Oracle数据库的详细指南。由Dennis Massanari和Arun Sar等人编写，发布于2009年1月，IBM Power Systems Performance团队。该文档涵盖了处理器性能调优、内存调优以及I/O性能调优等多个关键领域，旨在帮助用户提升Oracle数据库在AIX环境下的运行效率和性能。" 正文： 1. **处理器性能调优** - **Simultaneous Multithreading (SMT)**: SMT技术允许单个物理处理器同时执行多个线程，提高处理器的利用率和并发性。在AIX上，通过调整SMT级别可以优化Oracle数据库的并行执行能力。 - **进程调度和优先级**: 进程调度策略影响了CPU时间片的分配，而进程优先级则决定了哪个进程会优先获取处理器资源。在Oracle RAC环境中，正确配置进程优先级至关重要，以确保集群中的数据一致性。 - **处理器亲和性(Processor Affinity)**: 这种技术将进程绑定到特定的CPU核心，减少上下文切换，提升性能。在Oracle环境下，合理绑定数据库进程可以降低竞争，提高响应速度。 - **处理器绑定(Processor Binding)**: 类似于处理器亲和性，但更具体地控制进程在特定处理器上的运行，以减少缓存失效和增加性能。 - **资源集(Resourcesets)**: AIX的资源集功能允许系统管理员限制和控制特定进程或进程组的资源使用，包括CPU和内存。在Oracle环境中，这有助于优化资源分配，避免资源争抢。 - **逻辑分区(Logical Partitioning, LPAR)和微分区(Micro-Partitioning)**: LPAR允许多个独立的操作系统实例在同一硬件平台上运行，而微分区则进一步细化了资源分配，适合多租户环境下的Oracle部署。 2. **内存调优** - **Oracle过程内存足迹**: Oracle数据库的运行会消耗大量内存，包括SGA（System Global Area）、PGA（Program Global Area）等。理解并调整这些区域的大小对于性能至关重要。 - **虚拟内存(VMM)调优**: AIX的VMM管理内存分配，优化VMM参数可以提高Oracle数据库的内存使用效率。 - **多页面大小支持**: AIX支持不同大小的页面（如16MB大页面和64KB中页面），优化页面大小可以减少TLB（Translation Lookaside Buffer）查找开销，提升性能。 - **监控内存使用的工具**：例如`vmstat`、`svmon`和`ps`命令可以帮助系统管理员监控和分析内存使用情况。 3. **I/O性能调优** - **AIX V6.1的异步I/O**: 异步I/O允许进程在等待I/O完成时继续执行其他任务，提高了整体系统吞吐量。 - **异步I/O服务器**: 使用专门的I/O服务器处理数据库的读写操作，可减轻CPU负担，提高I/O性能。 - **maxreqs**: 调整`maxreqs`参数来控制异步I/O队列深度，平衡延迟和吞吐量。 - **文件系统和原始I/O**: 对于性能敏感的应用，如Oracle数据库，使用原始I/O可以直接访问磁盘，绕过文件系统的开销。 - **pbufs**: 调整`pbufs`以优化I/O缓冲区的数量，提升I/O性能。 - **lvm_bufcnt命令**: 用于查看和调整逻辑卷管理器（LVM）的缓冲区计数，以适应Oracle数据库的I/O需求。以上内容是文档中概述的主要调优方面，通过深入理解和实践这些技巧，可以在IBM AIX系统上显著提升Oracle数据库的性能和稳定性。

Processor binding

A process can be forced to run on only one particular processor, which is called binding a process to

a processor. The benefit is usually an increase in the cache hit ratio (the ratio of the number of times

referenced data is found in cache compared to the total number of references). The main

disadvantage is that the process then cannot be dispatched to an unused processor. The AIX

scheduler does a good job of optimizing between running threads on the same processor and

rebalancing the load across processors; therefore, it is best not to use processor binding.

Resource sets

Since AIX 5.2, you can also use the Resource Set API to restrict the resources used by a process to

a resource set. A resource set is a bundle of system resources that can consist of one or more

processors, one or more memory pools, or a mixture of memory pools and processors. Just as with

processor binding, the AIX scheduler does a good job of optimizing between running threads on the

same processor and rebalancing the load across processors; therefore, you should not directly use

resource sets to restrict a process to a particular processor or group of processors.

Many improvements have been made regarding processor affinity to improve performance, and workload

management has been introduced. Therefore, binding processes to processes or resource sets is hardly

of benefit any more in the AIX 5 and AIX 6.1 operating systems.

Logical partitioning and Micro-Partitioning

Logical partitioning (LPAR) is a feature that is available on POWER5 (or newer) processor-based systems

that run the AIX 5 (or newer) operating systems. This capability was introduced to create a partition of a

computer’s processors, memory and hardware-resources environment such that each partition can be

operated not only independently but also with its own operating system and applications.

Dynamic logical partitioning (dynamic LPAR or DLPAR) extends the capability of LPAR by providing the

ability to attach and detach a managed system’s resources logically to and from a logical hardware

partition’s operating system without rebooting. You can release processor, memory and I/O adapters into

a free pool that has been acquired from the same pool, or you can move them to another partition

dynamically.

From a performance perspective, LPARs and dynamic LPARs behave as if they are hardware

partitioning, yet they rquire minimal overhead. However, it is a good practice to try to create an LPAR or

dynamic LPAR partition based on processors belonging to the same dual-chip module (DCM) or multichip

module (MCM) — because it can increase the data locality of the Oracle data in shared cache and

minimize the memory-access latency. For more information on affinity and migration statistics, consult two

books that are listed under the IBM Redbooks™ section of the

Resources section of this paper (AIX 5L

Differences Guide Version 5.3 Edition and Partitioning Implementations for IBM eServer pSeries Servers)

regarding LPAR and dynamic LPAR configuration with MCM affinity.

Micro-Partitioning technology is a feature of the advanced IBM PowerVM™ (formerly know as IBM

Virtualization Engine™) and is only available on POWER5 and POWER6 processor-based systems. This

feature allows multiple partitions to share the processing power of a set of physical processors. A partition

that uses the Micro-Partitioning feature can be assigned as little as 1/10th of a physical processor’s

resource. In most cases, a shared-processor pool that contains multiple physical processors is shared

Tuning IBM AIX 5L V5.3 and AIX 6.1 for Oracle Database on POWER systems

among the partitions, and you can configure a partition to cede its processor resource for a short period of

time when idle. In this way, spare capacity can be reallocated among the partitions (with a granularity of

1/100

of a processor, within 10 milliseconds). This can be beneficial for the performance of a big high-

priority workload that shares a server with small lower-priority jobs that run in different partitions.

PowerVM requires only 1/10th of a processor entitlement to configure a partition, allowing up to 10 virtual

processors (these could even be from 10 different partitions) on a single physical processor. However,

when 10 partitions share a single physical processor, an increased latency results from dispatching the

10 virtual processors on a single physical processor. If the workloads on these 10 partitions do not have

sustained high load levels and do not have strict response-time criteria, then those workloads can share a

single processor by using a small fraction of a processor. However, if the load increases, response time

goes up. Therefore, it is better to use proper sizing tools such as IBM Workload Estimator (WLE) to size

processor capacity accurately for partitions. For workloads such as Oracle, depending on application

performance requirements such as response time and load levels, it is recommended that you use a

higher fraction processor entitlement, which increases the entitlement if response time needs to improve.

(Refer to the IBM PowerVM Redbook documents that are listed in the

Resources section of this paper for

details on how to select the proper number of virtual processors, processor folding and SMT snooze-

delay tuning values.) In cases where you need to allocate a whole processor to a partition, there are still

benefits in using shared-processor LPAR (SPLPAR) instead of a dedicated processor partition, because

sharing processors maximizes processor utilization in a consolidated environment. You need to study

each case carefully to decide if the Micro-Partitioning feature, SPLPAR or dedicated LPAR best suit your

workload-performance requirements. Using SPLPAR uncapped mode creates benefits by giving up

cycles when not needed and from getting more cycles than entitled cycles if free cycles are available in

the shared-processor pool.

Micro-Partitioning configuration and tuning, as well as related features (such as virtual I/O and virtual local

area network [virtual LAN]), can be complex and are covered in the Advanced POWER Virtualization on

IBM eServer p5 Servers: Introduction and Basic Configuration and Advanced POWER Virtualization on

IBM eServer p5 Servers Architecture and Performance Considerations Redbook documents that are

listed in the

Resources section of this paper.

Oracle Database 10g and 11g are capable of detecting a change in the number of processors that are

dynamically added to or removed from an LPAR, and will change the CPU_COUNT variable accordingly.

Subsequent operations, for example parallel query, can take advantage of the additional processor

resources after increasing the number of processors. This works well in a Micro-Partitioning environment,

as long as the capacity that is available to the LPARs is not excessively limited.

For more information on LPARs and the Micro-Partitioning feature, consult the IBM Redbook documents

that are listed in the

Resources section of this paper (AIX 5L Differences Guide Version 5.3 Edition and

Partitioning Implementations for IBM eServer pSeries Servers) regarding LPAR and dynamic LPAR

configuration.

Tools to monitor processor performance

The AIX operating system provides commands to help investigate and solve problems where the system

is processor-bound or suffers from abnormal processor usage. These commands include: vmstat, iostat

and sar.

Tuning IBM AIX 5L V5.3 and AIX 6.1 for Oracle Database on POWER systems

剩余36页未读，继续阅读

koalaqiang2008

粉丝: 0
资源: 3

IBM AIX系统上的Oracle数据库调优指南

优化ORACLE SQL性能：多用COMMIT-ORACLE_SQL_tuning策略

Python库chess_tuning_tools 0.7.0b2版本发布

Python库chess_tuning_tools-0.8.2：国际象棋调优工具

AIX_tuning_for_Oracle_DB.pdf

oracle_sql_tuning.rar_oracle

Oracle_OBIEE_12c__Tuning_Guide_-_v3.pdf

Oracle_Performance_Tuning_01_Performance_Tuning_Methodology

Hailey_Delphix_NFS_Tuning.pdf

OPDG_Query_Tuning.rar_has2md_oracle

最新资源