Oracle性能调试指南：从基础到优化

需积分: 9 188 浏览量更新于2024-07-20 收藏 10.83MB PDF 举报

《Oracle性能调试第二版》是一本深入探讨Oracle数据库性能问题的专业书籍，它分为四个部分，旨在帮助读者理解和解决数据库性能瓶颈。第一部分是基础，包括： 1. 第一章：性能问题 - 该章强调了在适当的时间和方法论上处理性能问题的重要性，同时阐述了理解业务需求和问题对于优化的关键作用。作者揭示了常见的数据库设计误区，这些设计可能造成性能低下。 2. 第二章：关键概念 - 进一步介绍了数据库引擎解析和执行SQL语句的工作原理，以及如何对应用代码和数据库调用进行监控。此外，还引入了书中反复使用的专业术语，以便于后续章节的讨论。第二部分着重于识别性能问题： - 第三章：可重现问题的分析 - 本章指导读者如何诊断并分析可以复现的问题，提供了一套系统的排查方法。 - 第四章：实时分析不可重现问题 - 针对那些难以复制的问题，作者讲解了实时性能监控和分析技巧，以便快速定位问题。 - 第五章：不可重现问题的事后分析 - 对于无法立即重现的情况，该章探讨了如何在问题发生后进行深入调查。第三部分深入探究查询优化器： - 第六章：介绍查询优化器 - 开始讲解Oracle数据库的内部工作机制，解释查询优化器的作用和原理。 - 第七章：系统统计信息 - 讨论如何收集和解读用于优化查询的系统级数据。 - 第八章：对象统计信息 - 详细介绍了表、索引等对象的统计信息对查询优化的影响。 - 第九章：配置查询优化器 - 提供了调整和优化查询优化器参数的实用指南。 - 第十章：执行计划 - 着重讲解如何阅读和分析查询执行计划，以识别性能瓶颈。通过这四个部分的深入剖析，读者不仅能够掌握Oracle数据库性能调优的基本原则，还能学会诊断和解决实际操作中的各种性能挑战。无论是初学者还是经验丰富的数据库管理员，都能从中受益匪浅。本书语言清晰，实例丰富，是提高Oracle性能管理能力的宝贵资源。

CHAPTER 1 ■ PERFORMANCE PROBLEMS

The problem with such designs is that they are (to say the least) suboptimal from a performance point of view.

In fact, flexibility is tied to performance. When one is at its maximum, the other is at its minimum. In some situations

suboptimal performance might be good enough. But in other situations it might be catastrophic. Hence, you should

use a flexible design only when the required performance can be achieved with it.

Not Using Constraints to Enforce Data Integrity

Constraints (primary keys, unique keys, foreign keys, NOT NULL constraints, and check constraints) are not only

fundamental to guarantee data integrity, but they are also extensively used by the query optimizer during the

generation of execution plans. Without constraints, the query optimizer is not able to take advantage of a number

of optimizations techniques. In addition, checking the constraints at the application level leads to more code

being written and tested as well as to potential problems with data integrity, because data can always be manually

modified at the database level. Also, checking constraints at application level usually requires greater consumption of

resources, and leads to less scalable locking schemes (such as locking an entire table instead of letting the database

lock only a subset of rows). Therefore, I strongly advise application developers to define all known constraints at the

database level.

Lack of Physical Database Design

It is not uncommon to see projects where the logical design is directly mapped to the physical design without taking

advantage of all the features provided by Oracle Database. The most common and obvious example is that every

relation is directly mapped to a heap table. From a performance point of view, such an approach is suboptimal. In

more than a few situations, index-organized tables (IOT), indexed clusters, or hash clusters might lead to better

performance.

Oracle Database provides much more than just the usual b-tree and bitmap indexes. Depending on the situation,

compressed indexes, reverse-key indexes, function-based indexes, linguistic indexes, or text indexes might be very

valuable to improving performance.

For large databases, the implementation of the partitioning option is critical. Most DBAs recognize the option

and its usefulness. A common problem in this area is that developers think that partitioning tables has no impact

on the physical database design. Sometimes this is true, but sometimes this is not the case. As a result I strongly

recommend planning the use of partitioning from the beginning of a project.

Another common issue to deal with during the development of a new application is the definition, and

implementation, of a sound data-archiving concept. Postponing it is usually not an option, because it might impact

the physical database design (if not the logical database design).

Not Choosing the Right Data Type

In recent years, I have witnessed a disturbing trend in physical database design. This trend may be called wrong

datatype selection (such as storing dates in VARCHAR2 instead of using DATE or TIMESTAMP). At first glance, choosing

the datatype seems to be a very straightforward decision to make. Nevertheless, do not underestimate the number of

systems that are now running and suffering because wrong datatypes were selected.

There are four main problems related to wrong datatype selection:

Wrong or lacking validation of data: The database engine must be able to validate data

that is stored in the database. For example, you should avoid storing numeric values in

character datatypes. Doing so calls for an external validation, which leads to problems

similar to those described in the section “Not Using Constraints to Enforce Data Integrity.”

www.it-ebooks.info

CHAPTER 1 ■ PERFORMANCE PROBLEMS

Loss of information: During the conversion of the original (correct) datatype to the

(wrong) database datatype, information gets lost. For example, let’s imagine what happens

when the date and time of an event is stored with a DATE datatype instead of with a

TIMESTAMP WITH TIME ZONE datatype. Fractional seconds and time zone information

get lost.

Things do not work as expected: Operations and features for which the order of data is

important might result in unexpected results, because of the specific comparison semantics

associated to every datatype. Typical examples are issues related to range partitioned tables

and ORDER BY clauses.

Query optimizer anomalies: The query optimizer might generate wrong estimates and,

consequently, might choose suboptimal execution plans because of wrong datatype

selection. This is not the fault of the query optimizer. The problem is that the query

optimizer cannot do its job because information is hidden from it.

In summary, there are plenty of good reasons for selecting datatypes correctly. Doing so will likely help you to

avoid many problems.

Not Using Bind Variables Correctly

From a performance point of view, bind variables introduce both an advantage and a disadvantage. The advantage of

bind variables is that they allow the sharing of cursors in the library cache, and in doing so they avoid hard parses and

the associated overhead. The disadvantage of using bind variables in WHERE clauses, and only in WHERE clauses, is that

crucial information is sometimes hidden from the query optimizer. For the query optimizer, to generate an optimal

execution plan for every SQL statement, having literals instead of bind variables is in fact much better. Chapter 2

discusses this topic in detail.

From a security point of view, bind variables prevent the risks associated with SQL injection. In fact, it is not

possible to change the syntax of a SQL statement by passing a value through a bind variable.

Not Using Advanced Database Features

Oracle Database is a high-end database engine that provides many advanced features that can drastically reduce

development costs, not to mention debugging and bug-fixing costs, while boosting performance. Leverage your

investment by taking advantage of those features as much as possible. Especially avoid rewriting already available

features (for example, do not create your own queuing system, because one is provided for you). That said, special

care should be taken the first time a specific feature is used, especially if that feature was introduced in the very same

database version you are running. You should not only carefully test such a feature to know whether it fulfills the

requirements, but also verify its stability.

The most common argument against advanced database features is that applications using them are closely

coupled to your current database brand and cannot be easily ported to another. This is true. However, most

companies will rarely change the database engine under a specific application anyway. Companies are more likely

to change the whole application before changing just the engine.

I recommend database-independent application design only when there are very good reasons for doing it.

And if for some reason doing database-independent design is necessary, go back and reread the discussion about

flexibility versus performance in the section “Implementing Generic Tables.” That discussion applies in this case

as well.

www.it-ebooks.info

CHAPTER 1 ■ PERFORMANCE PROBLEMS

Not Using PL/SQL for Data-Centric Processing

A special case, from the point raised in the previous section, is the use of PL/SQL for implementing batches that

process lots of data. The most common example is an extract-transform-load (ETL) process. When, in such a process,

the extract and load phases are executed against the very same database, from a performance point of view it is

almost insane to not process the transform phase by taking advantage of the SQL and PL/SQL engines provided by

the database engine that manages the source and target data. Unfortunately, the architecture of several mainstream

ETL tools leads exactly to such insane behavior. In other words, data is extracted from the database (and frequently

also moved to another server), the transform phase is executed, and then the resulting data is loaded back into

the very same database from which it came. For this reason, vendors like Oracle started offering tools that perform

transformations inside the database. Such tools, to differentiate them from the ETL tools, are commonly called ELT.

For best performance, I advise performing data-centric processing as closely as possible to the data.

Performing Unnecessary Commits

Commits are operations that call for serialization (the reason is simple: there is a single process (LGWR) that is

responsible for writing data to redolog files). It goes without saying that every operation that leads to serialization

inhibits scalability. And as a result, serialization is unwanted and should be minimized as much as possible. One

approach is to put several unrelated transactions together. The typical example is a batch job that loads many rows.

Instead of committing after every insert, it is much better to commit the inserted data in batches.

Steadily Opening and Closing Database Connections

Opening a database connection that in turn starts an associated dedicated process on the database server, is not

a lightweight operation. Do not underestimate the amount of time and resources required. A worst-case scenario

that I sometimes observe is a web application that opens and closes a database connection for every request that

involves a database access. Such an approach is highly suboptimal. Using a pool of connections in such a situation

is of paramount importance. By using a connection pool, you avoid the constant starting and stopping of dedicated

services processes, thus avoiding all the overhead involved.

Do You Have Performance Problems?

There is probably a good chance that sooner or later the performance of an application will be questioned. If, as

described in the previous sections, you have carefully defined the performance requirements, it should be quite

simple to determine whether the application in question is in fact experiencing performance problems. If you have

not carefully defined them, the response will largely depend on who answers the question.

Interestingly enough, in practice the most common scenarios leading to questions regarding the performance of

an application fall into very few categories. They are short-listed here:

Users are unsatisfied with the current performance of the application.•

A system-monitoring tool alerts you that a component of the infrastructure is experiencing •

timeouts or an unusual load.

A response-time monitoring tool informs you that a service level agreement is not being •

fulfilled.

The difference between the second point and the third point is particularly important. For this reason, in the next

two sections I briefly describe these monitoring solutions. After that, I present some situations where optimization

appears to be necessary but in fact is not necessary at all.

www.it-ebooks.info

CHAPTER 1 ■ PERFORMANCE PROBLEMS

System Monitoring

System-monitoring tools perform health checks based on general system statistics. Their purpose is to recognize

irregular load patterns that pop up as well as failures. Even though these tools can monitor the whole infrastructure

at once, it is important to emphasize that they monitor only individual components (for example, hosts, application

servers, databases, or storage subsystems) without considering the interplay between them. As a result, it is difficult,

and for complex infrastructures virtually impossible, to determine the impact on the system response time when a

single component of the infrastructure supporting it experiences an anomaly. An example of this is the high usage of

a particular resource. In other words, an alert coming from a system-monitoring tool is just a warning that something

could be wrong with the application or the infrastructure, but the users may not experience any performance

problems at all (called a false positive). In contrast, there may be situations where users are experiencing performance

problems, but the system-monitoring tool does not recognize them (called a false negative). The most common and

simplest cases of false positive and false negative are seen while monitoring the CPU load of SMP systems with a lot

of CPUs. Let’s say you have a system with four quad-core CPUs. Whenever you see a utilization of about 75%, you

may think that it is too high; the system is CPU-bounded. However, this load could be very healthy if the number

of running tasks is much greater than the number of cores. This is a false positive. Conversely, whenever you see a

utilization of about 8% of the CPU, you may think that everything is fine. But if the system is running a single task that

is not parallelized, it is possible that the bottleneck for this task is the CPU. In fact, 1/16th of 100% is only 6.25%, and

therefore, a single task cannot burn more than 6.25% of the available CPU. This is a false negative.

Response-Time Monitoring

Response-time monitoring tools (also known as application-monitoring tools) perform health checks based on either

synthetic transactions that are processed by robots, or on real transactions that are processed by end-users. The tools

measure the time taken by an application to process key transactions, and if the time exceeds an expected threshold

value, they raise an alert. In other words, they exploit the infrastructure as users do, and they complain about poor

performance as users do. Because they probe the application from a user perspective, they are able to not only check

single components but, more importantly, check the whole application’s infrastructure as well. For this reason, they

are devoted to monitoring service level agreements.

Compulsive Tuning Disorder

Once upon a time, most database administrators suffered from a disease called compulsive tuning disorder.

The signs

of this illness were the excessive checking of many performance-related statistics, most of them ratio-based, and

the inability to focus on what was really important. They simply thought that by applying some “simple” rules, it was

possible to tune their databases. History teaches us that results were not always as good as expected. Why was this the

case? Well, all the rules used to check whether a given ratio (or value) was acceptable were defined independently of

the user experience. In other words, false negatives or positives were the rule and not the exception. Even worse, an

enormous amount of time was spent on these tasks.

For example, from time to time a database administrator will ask me a question like “On one of our databases I

noticed that we have a large amount of waits on latch X. What can I do to reduce or, even better, get rid of such waits?”

My typical answer is “Do your users complain because they are waiting on this specific latch? Of course not. So, do not

worry about it. Instead, ask them what problems they are facing with the application. Then, by analyzing those problems,

you will find out whether the waits on latch X are related to them or not.” I elaborate on this in the next section.

Even though I have never worked as a database administrator, I must admit I suffered from compulsive tuning

disorder as well. Today, I have, like most other people, gotten over this disease. Unfortunately, as with any bad illness,

it takes a very long time to completely vanish. Some people are simply not aware of being infected. Others are aware,

but after many years of addiction, it is always difficult to recognize such a big mistake and break the habit.

This wonderful term was first coined by Gaya Krishna Vaidyanatha. You can find a discussion about it in the book Oracle Insights:

Tales of the Oak Table (Apress, 2004).

www.it-ebooks.info

剩余717页未读，继续阅读

lc724234511

粉丝: 2
资源: 13

Oracle性能调试指南：从基础到优化

低温放大器测试详解：流程与参数设置

《MacKungFu 第二版》英文PDF电子书免费下载

Qt学习之路2：兼顾Qt4与Qt5的全面教程

prob-solved_2ndedition.pdf

The Art of Assembly Language_2ndEdition_汇编语言的艺术_assemblylanguage

Java_Swing,2ndEdition

AdvancedPetroleumReservoirSimulation_TowardsDevelopingReservoirEmulators,2ndEdition (1).pdf

ProfessionalAjax2ndEdition

ObjectiveCProgrammingTheBigNerdRanchGuide2ndEdition

PowerBICookbook2ndEdition

最新资源