Oracle DBA必读：核心内部详解

需积分: 5 201 浏览量更新于2024-06-20 收藏 5.33MB PDF 举报

《Oracle核心关键内部机制指南》是专为DBA设计的一本Oracle数据库管理经典著作，由经验丰富的作者撰写，旨在帮助读者深入理解Oracle数据库的复杂体系结构和核心操作。本书主要围绕Oracle数据库的关键领域展开，包括但不限于： 1. **入门与基础**（Chapter 1: Getting Started）：章节概述了Oracle数据库的基本安装、配置和管理流程，为读者提供了一个扎实的起点。 2. **重做日志与撤销段**（Chapter 2: Redo and Undo）：这是数据恢复的核心，介绍了如何跟踪事务更改并确保在系统故障后的数据一致性，以及undo表空间的作用。 3. **事务与一致性**（Chapter 3: Transactions and Consistency）：深入讲解了ACID属性（原子性、一致性、隔离性和持久性）在Oracle中的实现，以及如何管理并发控制和事务管理。 4. **锁与闩锁**（Chapter 4: Locks and Latches）：剖析Oracle的并发控制机制，包括共享锁、排他锁和各种类型的闩锁，以及它们对性能的影响。 5. **缓存与复制**（Chapter 5: Caches and Copies）：讲解了数据库缓存（如library cache和shared pool）的工作原理，以及实例管理和分布式数据库环境中的数据复制技术。 6. **写入与恢复**（Chapter 6: Writing and Recovery）：涉及数据块的写入策略、归档日志的管理以及故障恢复过程中的关键步骤。 7. **解析与优化**（Chapter 7: Parsing and Optimizing）：探讨SQL语句的解析过程和Oracle的查询优化器如何选择执行计划，以提高查询性能。 8. **RAC（实时应用集群）与挑战**（Chapter 8: RAC and Ruin）：针对RAC环境下多节点协作处理的特性，讨论潜在问题和优化策略，确保高可用性和性能。 9. **附录：备份与调试**（Appendix: Dumping and Debugging）：提供了实用的工具和技术，如导出数据和诊断模式，用于故障排查和维护。尽管本书未涵盖所有Oracle 9i及后续版本的新功能，但它仍然是一份宝贵的资源，适合希望深入了解Oracle核心内部机制的DBA和系统管理员。随着Oracle技术的不断演进，读者可能需要结合官方文档和其他最新资料来补充学习。然而，本书提供的基础知识和理论框架无疑对理解和管理当前的Oracle数据库系统至关重要。

CHAPTER 2 ■ REDO AND UNDO

So far I’ve shown you our two change vectors only as individual entities; if I had shown you the

complete picture of the way these change vectors went into the redo log, you would have seen how they

were combined into a single redo record:

REDO RECORD - Thread:1 RBA: 0x00036f.00000005.008c LEN: 0x00f8 VLD: 0x01

SCN: 0x0000.03ee485a SUBSCN: 1 03/13/2011 17:43:01

CHANGE #1 TYP:0 CLS:36 AFN:2 DBA:0x0080009a SCN:0x0000.03ee485a SEQ: 4 OP:5.1

…

CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0018a SCN:0x0000.03ee485a SEQ: 2 OP:11.5

…

It is a common (though far from universal) pattern in the redo log that change vectors come in

matching pairs, with the change vector for an undo record appearing before the change vector for the

corresponding forward change.

While we’re looking at the bare bones of the preceding redo record, it’s worth noting the LEN: figure

in the first line—this is the length of the redo record: 0x00f8 = 248 bytes. All we did was change xxxxxx to

YYYYYYYYYY in one row and it cost us 248 bytes of logging information. In fact, it seems to have been a

very expensive operation given the net result: we had to generate two redo change vectors and update

two database blocks to make a tiny little change, which looks like four times as many steps as we need to

do. Let’s hope we get a decent payback for all that extra work.

Summary of Observations

Before we continue, we can summarize our observations as follows: in the data files, every change we

make to our own data is matched by Oracle with the creation of an undo record (which is also a change

to a data file); at the same time Oracle puts into the redo log a description of how to make our change

and how to make its own change.

You might note that since data can be changed “in place,” we could make an “infinite” (i.e.,

arbitrarily large) number of changes to our single row of data, but we clearly can’t record an infinite

number of undo records without growing the data files of the undo tablespace, nor can we record an

infinite number of changes in the redo log without constantly adding more redo log files. For the sake of

simplicity, we’ll postpone the issue of infinite changes and simply pretend for the moment that we can

record as many undo and redo records as we need.

ACID

Although we’re not going to look at transactions in this chapter, it is, at this point, worth mentioning the

ACID requirements of a transactional system and how Oracle’s implementation of undo and redo gives

Oracle the capability of meeting those requirements. Table 2-1 lists the ACID requirements.

CHAPTER 2 ■ REDO AND UNDO

Table 2-1. The ACID Requirements

Atomicity A transaction must be invisible or complete.

Consistency The database must be self-consistent at the start and end of each transaction.

Isolation A transaction may not see results produced by another incomplete transaction.

Durability A committed transaction must be recoverable after a system failure.

The following list goes into more detail about each of the requirements in Table 2-1:

• Atomicity: As we make a change, we create an undo record that describes how to

reverse the change. This means that when we are in the middle of a transaction,

another user trying to view any data we have modified can be instructed to use the

undo records to see an older version of that data, thus making our work invisible

until the moment we decide to publish (commit) it. We can ensure that the other

user either sees nothing of what we’ve done or sees everything.

• Consistency: This requirement is really about constraints defining the legal states

of the database; but we could also argue that the presence of undo records means

that other users can be blocked from seeing the incremental application of our

transaction and therefore cannot see the database moving from one legal state to

another by way of a temporarily illegal state—what they see is either the old state

or the new state and nothing in between. (The internal code, of course, can see all

the intermediate states—and take advantage of being able to see them—but the

end-user code never sees inconsistent data.)

• Isolation: Yet again we can see that the availability of undo records stops other

users from seeing how we are changing the data until the moment we decide that

our transaction is complete and commit it. In fact, we do better than that: the

availability of undo means that other users need not see the effects of our

transactions for the entire duration of their transactions, even if we start and end

our transaction between the start and end of their transaction. (This is not the

default isolation level in Oracle, but it is an available isolation level; see the

“Isolation Levels” sidebar.) Of course, we do run into confusing situations when

two users try to change the same data at the same time; perfect isolation is not

possible in a world where transactions have to take a finite amount of time.

CHAPTER 2 ■ REDO AND UNDO

• Durability: This is the requirement that highlights the benefit of the redo log. How

do you ensure that a completed transaction will survive a system failure? The

obvious strategy is to keep writing any changes to disc, either as they happen or as

the final step that “completes” the transaction. If you didn’t have the redo log, this

could mean writing a lot of random data blocks to disc as you change them.

Imagine inserting ten rows into an order_lines table with three indexes; this could

require 31 randomly distributed disk writes to make changes to 1 table block and

30 index blocks durable. But Oracle has the redo mechanism. Instead of writing an

entire data block as you change it, you prepare a small description of the change,

and 31 small descriptions could end up as just one (relatively) small write to the

end of the log file when you need to make sure that you’ve got a permanent record

of the entire transaction. (We’ll discuss in Chapter 6 what happens to the 31

changed data blocks, and the associated undo blocks, and how recovery might

take place.)

ISOLATION LEVELS

Oracle offers three isolation levels: read committed (the default), read only, and serializable. As a brief

sketch of the differences, consider the following scenario: table

t1 holds one row, and table t2 is identical

t1 in structure. We have two sessions that go through the following steps in order:

1. Session 1: select from t1;

2. Session 2: insert into t1 select * from t1;

3. Session 2: commit;

4. Session 1: select from t1;

5. Session 1: insert into t2 select * from t1;

If session 1 is operating at isolation level read committed, it will select one row on the first select, select

two rows on the second select, and insert two rows.

If session 1 is operating at isolation level read only, it will select one row on the first select, select one row

on the second select, and fail with Oracle error “ORA-01456: may not perform insert/delete/update

operation inside a READ ONLY transaction.”

If session 1 is operating at isolation level serializable, it will select one row on the first select, select one

row on the second select, and insert one row.

Not only are the mechanisms for undo and redo sufficient to implement the basic requirements of

ACID, they also offer advantages in performance and recoverability.

The performance benefit of redo has already been covered in the comments on durability; if you

want an example of the performance benefits of undo, think about isolation—how can you run a report

that takes minutes to complete if you have users who need to update data at the same time? In the

absence of something like the undo mechanism, you would have to choose between allowing wrong

results and locking out everyone who wants to change the data. This is a choice that you have to make

with some other database products. The undo mechanism allows for an extraordinary degree of

CHAPTER 2 ■ REDO AND UNDO

concurrency because, per Oracle’s marketing sound bite, “readers don’t block writers, writers don’t

block readers.”

As far as recoverability is concerned (and we will examine recoverability in more detail in Chapter

6), if we record a complete list of changes we have made to the database, then we could, in principle,

start with a brand-new database and simply reapply every single change description to reproduce an up-

to-date copy of the original database. Practically, of course, we don’t (usually) start with a new database;

instead we take regular backup copies of the data files so that we need only replay a small fraction of the

total redo generated to bring the copy database up to date.

Redo Simplicity

The way we handle redo is quite simple: we just keep generating a continuous stream of redo records

and pumping them as fast as we can into the redo log, initially into an area of shared memory known as

the redo log buffer. Eventually, of course, Oracle has to deal with writing the buffer to disk and, for

operational reasons, actually writes the “continuous” stream to a small set of predefined files—the

online redo log files. The number of online redo log files is limited, so we have to reuse them constantly

in a round-robin fashion.

To protect the information stored in the online redo log files over a longer time period, most

systems are configured to make a copy, or possibly many copies, of each file as it becomes full before

allowing Oracle to reuse it: the copies are referred to as the archived redo log files. As far as redo is

concerned, though, it’s essentially write it and forget it—once a redo record has gone into the redo log

(buffer), we don’t (normally) expect the instance to reread it. At the basic level, this “write and forget”

approach makes redo a very simple mechanism.

■ Note Although we don’t usually expect to do anything with the online redo log files except write them and

forget them, there is a special case where a session can read the online redo log files when it discovers the in-

memory version of a block to be corrupt and attempts to recover from the disk copy of the block. Of course, some

features, such as Log Miner, Streams, and asynchronous Change Data Capture, have been created in recent years

to take advantage of the redo log files, and some of the newer mechanisms for dealing with Standby databases

have become real-time and are bound into the process that writes the online redo. We will look at such features in

Chapter 6.

There is, however, one complication. There is a critical bottleneck in redo generation, the moment

when a redo record has to be copied into the redo log buffer. Prior to 10g, Oracle would insert a redo

record (typically consisting of just one pair of redo change vectors) into the redo log buffer for each

change a session made to user data. But a single session might make many changes in a very short

period of time, and there could be many sessions operating concurrently—and there’s only one redo log

buffer that everyone wants to access.

It’s relatively easy to create a mechanism to control access to a piece of shared memory, and

Oracle’s use of the redo allocation latch to protect the redo log buffer is fairly well known. A process that

needs some space in the log buffer tries to acquire (get) the redo allocation latch, and once it has

exclusive ownership of that latch, it can reserve some space in the buffer for the information it wants to

write into the buffer. This avoids the threat of having multiple processes overwrite the same piece of

CHAPTER 2 ■ REDO AND UNDO

memory in the log buffer, but if there are lots of processes constantly competing for the redo allocation

latch, then the level of competition could end up “invisibly” consuming lots of resources (typically CPU

spent on latch spinning) or even lots of sleep time as sessions take themselves off the run queue after

failing to get the latch on the first spin.

In older versions of Oracle, when the databases were less busy and the volume of redo generated

was much lower, the “one change = one record = one allocation” strategy was good enough for most

systems, but as systems became larger, the requirement for dealing with large numbers of concurrent

allocations (particularly for OLTP systems) demanded a more scalable strategy. So a new mechanism

combining private redo and in-memory undo appeared in 10g.

In effect, a process can work its way through an entire transaction, generating all its change vectors

and storing them in a pair of private redo log buffers. When the transaction completes, the process

copies all the privately stored redo into the public redo log buffer, at which point the traditional log

buffer processing takes over. This means that a process acquires the public redo allocation latch only

once per transaction, rather than once per change.

■ Note As a step toward improved scalability, Oracle 9.2 introduced the option for multiple log buffers with the

log_parallelism parameter, but this option was kept fairly quiet and the general suggestion was that you didn’t

need to know about it unless you had at least 16 CPUs. In 10g you get at least two public log buffers (redo threads)

if you have more than one CPU.

There are a number of details (and restrictions) that need to be mentioned, but before we go into

any of the complexities, let’s just take a note of how this changes some of the instance activity reported

in the dynamic performance views. I’ve taken the script in core_demo_02.sql, removed the dump

commands, and replaced them with calls to take snapshots of v$latch and v$sesstat (see

core_demo_02b.sql in the code library). I’ve also modified the SQL to update 50 rows instead of 5 rows so

that differences in workload stand out more clearly. The following results come from a 9i and a 10g

system, respectively, running the same test. First the 9i results:

Latch Gets Im_Gets

----- ---- -------

redo copy 0 51

redo allocation 53 0

Name Value

---- -----

redo entries 51

redo size 12,668

Note particularly in the 9i output that we have hit the redo copy and redo allocation latches 51 times

each (with a couple of extra gets on the allocation latch from another process), and have created 51 redo

entries. Compare this with the 10g results:

剩余276页未读，继续阅读

niudake

粉丝: 0
资源: 8

Oracle DBA必读：核心内部详解

Oracle Core Essential Internals

Oracle.Core：Essential.Internals.for.DBAs.and.Developers

Oracle Core Essential Internals for DBAs and Developers

Oracle Core - Essential Internals for DBAs and Developers.pdf

Oracle Core- Essential Internals for DBAs and Developers 无水印pdf

Oracle Core - Essential Internals for DBAs and Developers.(Jonathan Lewis)

Oracle.Core：Essential.Internals.for.DBAs.and.Developers.-带书签超清文字版

Oracle Core Essential Internals for DBAs and Developers.zip

Oracle Core Essential Internals for DBAs and Developers-colour

Oracle Core Essential Internals for DBAs and Developers ■ ■

最新资源