Oracle核心内部指南：DBA与开发者必备

需积分: 10 178 浏览量更新于2024-07-23 收藏 5.33MB PDF 举报

《Oracle Core Essential Internals for DBAs and Developers》是由Jonathan Lewis编著的一本专为数据库管理员（DBA）和开发人员设计的权威指南。该书深入剖析了Oracle数据库的核心内部机制，帮助读者理解并掌握关键概念和技术。第1章"Getting Started"主要介绍了Oracle数据库在进程中的运行方式和实际操作，让读者对Oracle的基础有初步了解。章节内容涵盖了Oracle在操作系统中的定位、基本操作流程以及总结要点。第2章"Redo and Undo"聚焦于重做日志和撤销段，这是事务处理的关键组成部分。章节讲解了数据变化的基本原理，探讨了Oracle如何通过重做记录和撤销记录来保持数据一致性。这部分详细阐述了ACID特性，讨论了重做操作的简单性与撤销操作的复杂性，以及读一致性、回滚等概念。第3章"Transactions and Consistency"深入研究了交易和一致性问题，包括冲突解决策略、事务与撤销的关系，以及事务开始和结束的处理。还介绍了事务表的作用以及如何通过Undo块来跟踪数据修改，涉及并发操作和提交顺序控制。第4章"Locks and Latches"剖析了锁定和信号量（latches）在并发控制中的作用，包括数据结构如数组、指针、链表和哈希表，以及它们在Oracle系统中的逻辑。章节讲解了死锁的概念，不同类型的锁定模式，以及latches和locks的交互。第5章"Caches and Copies"详细讨论了内存管理、缓冲区和副本的使用。内容涵盖内存管理原则、数据缓存、多级缓存、缓冲池的运作，以及读一致性副本的创建和物理I/O。第6章"Writing and Recovery"涉及数据写入和恢复过程，包括目标系统、日志写入器、ACID异常以及数据库写入器和检查点的管理。此外，还有媒体恢复、备用数据库和闪回数据库等恢复技术的介绍。第7章"Parsing and Optimizing"讲解SQL解析和优化，包括解析过程、库缓存、共享池结构和SQL执行的细节，帮助读者理解性能调优技巧。第8章"RAC and Ruin"探讨了Real Application Clusters（RAC）架构，强调高可用性和可扩展性，深入剖析网格资源分布、主备角色、故障恢复以及序列管理的内部机制。最后的附录"Dumping and Debugging"提供了数据库调试和问题排查的相关工具和技术，如oradebug，以及数据文件和日志文件的分析方法。本书对于想要深入了解Oracle数据库内部工作原理的专业人员来说是一本不可或缺的参考资料，它通过翔实的解释和实例，揭示了Oracle的核心机制，无论你是DBA还是开发者，都能从中获益良多。

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页未读，继续阅读

jasonlyang

粉丝: 0

Oracle核心内部指南：DBA与开发者必备

Oracle Core Essential Internals for DBAs and Developers.zip

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.pdf

Oracle core essential internals for dbas

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

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

Oracle Core Essential Internals for DBAs and Developers-colour

Oracle Core Essential Internals for DBAs and Developers

Apress - Oracle Core Essential Internals for DBAs and Developers

最新资源