深入解析Oracle RAC性能诊断与优化

需积分: 9 170 浏览量更新于2024-07-22 收藏 25.14MB PDF 举报

"Expert Oracle RAC Performance Diagnostics and Tuning" 是一本专注于Oracle RAC数据库性能诊断和调优的专业书籍，由Murali Vallath撰写。本书深入探讨了通过科学分析而非猜测或盲目调整来优化RAC数据库的方法。内容涵盖从基础的调优方法、容量规划到架构的全面介绍，并逐步剖析测试实施的各个层面，包括操作系统、网络、应用、存储、实例、数据库和网格基础设施。书中还介绍了性能优化工具，并提供了大量实际案例、分析和解决方案，以提供一个实用且可复制的RAC环境调优策略。最后，书中包含故障排查指导和所有书中脚本的快速参考。本书结构清晰，章节内容包括： 1. 调优方法学：介绍调优的基本原则和步骤。 2. 容量规划与架构：讲解如何规划RAC系统的容量，以及系统架构的关键要素。 3. 可用性测试：讨论确保RAC数据库高可用性的策略和测试。 4. 扩展性测试：关注系统在负载增加时的表现和应对策略。 5. 实际应用测试：如何模拟真实工作负载来评估系统性能。 6. 工具和实用程序：介绍用于性能监控和优化的各种Oracle工具。 7. SQL调优：针对SQL查询进行性能优化的技巧和方法。 8. 并行查询调优：提高并行查询效率的策略。 9. 数据库调优：对数据库层面进行微调以提升整体性能。 10. 恢复调优：优化恢复过程，确保快速、高效的数据恢复。 11. Oracle Net调优：改善网络通信性能，减少延迟。 12. 存储子系统调优：优化存储配置以提高I/O性能。 13. 全局缓存调优：管理全局缓存，确保数据一致性。 14. 集群互连调优：优化集群节点间通信的性能。 15. 分布式工作负载优化：处理跨节点的分布式工作负载。 16. Oracle集群软件诊断：提供对Oracle Clusterware的故障诊断指南。 17. 等待事件和队列：理解等待事件和队列在性能分析中的作用。这本书对于Oracle RAC数据库管理员、DBA以及寻求提高RAC性能的IT专业人士来说是一本宝贵的资源，它提供了全面的实践指导，帮助读者掌握性能诊断和调优的科学方法。

CHAPTER 1 ■ METHODOLOGY

Methodology

Problem-solving tasks of any nature need to be approached in a systematic and methodical manner. A detailed

procedure needs to be developed and followed from end to end. During every step of the process, data should be

collected and analyzed. Results from these steps should be considered as inputs into the next step, which in turn is

performed in a similar systematic approach. Hence, methodology is the procedure or process followed from start to

finish, from identification of the problem to problem solving and documentation.

During all this analysis, the cause or reasons for a behavior or problem should be based on quantitative analysis

and not on guesswork or trial and error.

USING DBMS_ appLICatION INFO

A feature that could help during all the phases of testing, troubleshooting, and debugging of the application is

the use of the DBMS_APPLICATION_INFO package in the application code. The DBMS_APPLICATION_INFO package

has procedures that will allow modularizing performance data collection based on specific modules or areas

within modules.

Incorporating the DBMS_APPLICATION_INFO package into the application code helps the administrators to easily

track the sections of the code (module/action) that are high resource consumers. When the user/application

session registers a database session, the information is recorded in V$SESSION and V$SQLAREA. This helps in easy

identification of the problem areas of the application.

The application should set the name of the module and name of the action automatically each time a user enters

that module. The name given to the module could be the name of the code segment in an Oracle pre-compiler

application or service within the Java application. The action name should usually be the name or description of

the current transaction within a module.

Procedures

Procedure Description

SET_CLIENT_INFO Sets the CLIENT_INFO field of the session.

SET_MODULE Sets the name of the module that is currently running.

SET ACTION Sets the name of the current action within the current module.

SET_SESSION_LONGOPS Sets a row in the GV$SESSION_LONGOPS table.

When the application connects to the database using a database service name (either using a Type 4 client or a

Type 2 client) then even a granular level of resource utilization for a given service, module, and/or action could be

collected. Database service names are also recorded in GV$SESSION.

One of the great benefits of enabling the DBMS_APPLICATION_INFO package call in the application code is that the

database performance engineer can enable statistics collection or enable tracing when he/she feels it’s needed

and at what level it’s needed.

www.it-ebooks.info

CHAPTER 1 ■ METHODOLOGY

Methodologies could be different depending on the work involved. There could be methodologies for

Development life cycle•

Migration•

Testing•

Performance tuning•

Performance Tuning Methodology

The performance tuning methodology can be broadly categorized into seven steps.

Problem Statement

Identify or state the specific problem in hand. This could be different based on the type of application and the phase

of the development life cycle. When a new code is being deployed into production, the problem statement is to meet

the requirements for response time and transaction per second and the recovery time. The business analysts, as we

have discussed earlier, define these requirements. Furthermore, based on the type of requirement being validated, the

scope may require some additional infrastructure such as data guard configuration for disaster recovery.

On the other hand, if the code is already in production, then the problem statement could be made in terms of

slow response time that the users have been complaining about; a dead lock situation that has been encountered in

your production environment; an instance in a RAC configuration that crashes frequently, and so forth.

A clear definition of the tuning objective is a very important step in the methodology because it basically defines

what is going to be achieved in the testing phase or test plan that is being prepared.

Information Gathering

Gather all information relating to the problem identified in step one. This depends on the problem being addressed.

If this is a new development rollout, the information gathering will be centered on the business requirements,

the development design, entity model of the database, the database sizing, the cardinality of the entities, the SLA

requirements, and so forth. If this is an existing application that is already in production, the information-gathering

phase may be around collecting statistics, trace, log, or other information. It is important to understand the

environment, the configuration, and the circumstances around the performance problem. For instance, when a user

complains of poor performance, it may be a good idea to interview the user. The interview can consist of several levels

to understanding the issue.

What kind of functional area of the application was used, and at what time of the day was the operation

performed? Was this consistently occurring every time during the same period in the same part of the application

(it is possible that there was another contending application at that time, which may be the cause of the slow

performance)? This information will help in collecting data pertaining to that period of the day and will also help

in analyzing data from different areas of the applications, other applications that access the database, or even

applications that run on the same servers.

Once user-level information is gathered, it may be useful to understand the configuration and environment

in general:

Does the application use database services? Is the service running as • SINGLETON on one instance

or more than one instance (UNIFORM)? What other services are running on these servers?

Is the cluster configured to use server pools?•

What resource plans have been implemented to prioritize the application (if any)?•

www.it-ebooks.info

CHAPTER 1 ■ METHODOLOGY

If the issue was with the instance or a server crashing in the RAC environment, data related to specific modules,

such as the interconnect, data related to the heartbeat verification via the interconnect, and the heartbeat verification

against the voting disks have to be collected. For example, a detailed look at the data in the GRID infrastructure log

files may have to be analyzed after enabling debug (crsctl debug log css "CSSD:9") to get the clusterware to write

more data into these log files. If this is a performance-related concern, then collecting data using a trace from the user

session would be really helpful in analyzing the issue. Tools such as Lightweight Onboard Monitor (LTOM

), or at the

minimum collecting trace using event 10046, would be really helpful.

Several times instance or server crashes in a RAC environment could be due to overload on the system affecting

the overall performance of the system. In these situations, the directions could shift to availability or stability of the

cluster. However, the root cause analysis may indicate other reasons.

Area Drilldown

Drilling down further to identify the cause or area of a performance issue is probably the most critical of the steps

because with all the data collected, it’s time to drill down to the actual reason that has led to the problem. Irrespective

of whether this is an instance/server crash because of overload or poorly performing module or application, the

actual problem should be identified at this stage and documented. For example, what query in the module or

application is slowing down the process, or is there a contention caused by another application (batch) that is causing

the online application to slow down?

At this level of drilldown, the details of the application area need to be identified: what service, what module, and

what action was the reason for this slowness. To get this level of detail, the DBMS_APPLICATION_INFO package discussed

earlier is a very helpful feature.

Problem Resolution

Working to resolve the performance issue is probably the most critical step. When resolving problems, database

parameters may have to be changed, host bus adaptor (HBA) controllers or networks or additional infrastructure such

as CPU or memory may have to be added, or maybe it all boils down to tuning a bad performing structured query

language (SQL) query, or making sure that the batch application does not run in the same time frame as the primary

online application, or even better if the workload can be distributed using database services to reduce resource

contention on any one server/instance causing poor response times. It is important that when fixing problems the

entire application is taken into consideration; making fixes to help one part of the application should not affect the

other parts of the application.

Testing Against Baseline

Once the problem identified has been fixed and unit tested, the code is integrated with the rest of the application

and tested to see if the performance issue has been resolved. In the case of hardware related changes or fixes, such

a test may be very hard to verify; however, if the fix is done over the weekend or during a maintenance window, the

application could be tested to ensure it is not broken due to these changes. Depending on the complexity of the

situation and maintenance window available, it will drive how extensive these tests can be. Here is a great benefit

of using database services that allow disabling usage of a certain server or database instance from regular usage or

allowing limited access to certain part of the application functionality, which could be tested using an instance or

workload until such time as it’s tested and available for others to use.

Usage and implementation of LTOM will be discussed in Chapter 6.

www.it-ebooks.info

CHAPTER 1 ■ METHODOLOGY

Repeating the Process

Now that the identified problem has been resolved, it’s time to look at the next issue or problem reported. As

discussed, the methodology should be repeatable through all the cases. Methodology also calls for documentation

and storing the information in a repository for future review, education, and analysis.

Whereas each of the previous steps is very broad, a methodical approach will help identify and solve the problem

in question, namely, performance.

Which area of the system is having a performance problem? Where do we start? Should the tuning process

start with the O/S, network, database, instance, or the application? Probably the users of the application tier are

complaining that the system is slow. Users access the application, and the application in turn through some kind of

persistence layer communicates to the database to store and retrieve information. When the user who makes the

data request using an application does not get a response in a sufficiently fair amount of time, they complain that the

system is slow.

Although the top-down methodology of tuning the application and then looking at other components works

most of the time, sometimes one may have to adopt a bottom-up approach: that is, starting with the hardware

platform, tuning the storage subsystem, tuning the database configuration, tuning the instance, and so forth.

Addressing the performance issues using this approach could bring some amount of change or performance

improvement to the system with less or no impact to the actual application code. If the application is poorly written

(for example, a bad SQL query), it does not matter how much tuning is done at the bottom tier; the underlying issue

will remain the same.

The top-down or bottom-up methodology just discussed is good for an already existing production application

that needs to be tuned. This is true for several reasons:

1. Applications have degraded in performance due to new functionality that was not

sufficiently tuned.

2. The user base has increased and the current application does not support the extended

user base.

3. The volume of data in the underlying database has increased; however, the storage has not

changed to accept the increased I/O load.

Whereas these are issues with an existing application and database residing on existing hardware, a more

detailed testing and tuning methodology should be adopted when migrating from a single instance to a clustered

database environment. Before migrating the actual application and production enabling the new hardware, the

following basic testing procedure should be adopted.

Testing of the RAC environment should start with tuning a single instance configuration. Only when the

performance characteristics of the application are satisfactory should the tuning on the clustered configuration begin.

To perform these tests, all nodes in the cluster except one should be shut down and the single instance node should

be tuned. Only after the single instance has been tuned and the appropriate performance measurements equal to the

current configuration or more are obtained should the next step of tuning be started. Tuning the cluster should be

done methodically by adding one instance at a time to the mix. Performance should be measured in detail to ensure

that the expected scalability and availability is obtained. If such performance measurements are not obtained, the

application should not be deployed into production, and only after the problem areas are identified and tuned should

deployment occur.

Note ■ RAC cannot perform any magic to bring performance improvements to an application that is already performing

poorly on a single instance configuration.

www.it-ebooks.info

剩余689页未读，继续阅读

vanridin

粉丝: 108
资源: 1187

深入解析Oracle RAC性能诊断与优化

PERFORMANCE TUNING ORACLE RAC ON LINUX

一个不错的oracle 10g rac performance tuning 文档

expertoracleracperformancediagnosticsandtuningPDFBooks.pdf 英文原版

srvctl 重启oracle rac操作命令

Oracle rac安装部署

oracle rac运维培训

如何安装Oracle RAC

Linux 部署Oracle rac

oracle rac简单介绍,《oraclerac介绍》

oracle rac安装

最新资源