Oracle RAC性能诊断与优化实战

需积分: 10 167 浏览量更新于2024-07-22 收藏 24.45MB PDF 举报

"Expert.Oracle.RAC.Performance.Diagnostics" 是一本深入探讨Oracle Real Application Clusters (RAC) 性能诊断的专业书籍。作者通过多年在不同行业和RAC项目中的经验，分享了如何在生产环境中解决性能问题的方法。本书内容涵盖了从理论到实践的全方位指南，包括： 1. **方法论**：第一章介绍了处理RAC性能问题的基本步骤和策略，为后续章节提供了基础框架。 2. **容量规划与架构**：第二章深入讨论了如何进行RAC环境的容量规划，以及如何设计适合的架构以确保高性能和高可用性。 3. **可用性测试**：第三章关注的是如何验证RAC系统的可用性，确保在故障情况下仍能保持服务连续性。 4. **可扩展性测试**：第四章讲解如何测试RAC环境的可扩展性，以评估系统在负载增加时的响应能力。 5. **Real Application Testing (RAT)**：第五章介绍了Oracle的RAT工具，用于模拟真实工作负载，预测和优化RAC性能。 6. **工具与实用程序**：第六章列出了用于RAC性能监控和诊断的各种工具，如OEM、AWR、ASH等。 7. **SQL调优**：第七章专门讨论SQL查询优化，以减少资源消耗，提高查询效率。 8. **并行查询调优**：第八章详细介绍了如何调整并行查询以充分利用RAC的并行执行特性。 9. **数据库调优**：第九章涵盖了数据库级别的调优技术，包括参数调整、索引优化等。 10. **恢复调优**：第十章讲述了如何优化恢复过程，以缩短故障恢复时间。 11. **Oracle Net调优**：第十一章讨论了网络层面的性能问题及其解决方法，确保数据传输高效稳定。 12. **存储子系统调优**：第十二章关注存储配置对RAC性能的影响，提供优化存储策略的建议。 13. **全局缓存调优**：第十三章讲解了如何优化全局缓存（例如，redo log和cache fusion）以提高并发性能。 14. **集群互连调优**：第十四章分析了集群节点间通信的性能瓶颈，并提供改善互连性能的策略。 15. **优化分布式工作负载**：第十五章针对跨节点的负载分布和协调进行优化，确保整体系统性能。 16. **Oracle Clusterware诊断**：第十六章专注于Oracle Clusterware的诊断技巧，帮助识别和解决问题。 17. **等待事件、队列和锁**：第十七章详细解析了等待事件、队列和锁的概念，这些是诊断性能问题的关键。 18. **问题诊断**：最后，第十八章介绍了如何系统地诊断和解决各种性能问题，提供了实际案例和解决步骤。这本书不仅适合Oracle RAC的管理员、DBA，也对任何希望深入了解和优化Oracle RAC性能的技术人员提供了宝贵的参考资料。通过阅读和应用书中的知识，读者可以提升RAC环境的性能和稳定性，有效应对生产环境中可能出现的性能挑战。

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.

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)?•

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.

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.

剩余689页未读，继续阅读

jerrylees2006

粉丝: 2
资源: 64

Oracle RAC性能诊断与优化实战

Apress.Expert.Oracle.RAC.12c

Apress.Expert.Oracle.Database.Architecture.2nd.Edition

双节点rac数据库oracle运行在linux上 ，192.168.1.21 为rac1,192.168.1.22为rac2 虚拟一个192.168.1.25，如何进入到这个虚拟后台进行配置

srvctl 重启oracle rac操作命令

oracle 12.2.0.1 rac for oracle linux 7.9

Oracle rac

oracle rac的优势

oracle 单机升级oracle rac

CRS-2674: Start of 'ora.orcl.db' on 'rac1' failed

oracle rac安装

最新资源

双节点rac数据库oracle运行在linux上，192.168.1.21 为rac1,192.168.1.22为rac2 虚拟一个192.168.1.25，如何进入到这个虚拟后台进行配置