Oracle数据库核心机制解析

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

"Oracle Core Essential Internals" 是一本专注于Oracle数据库核心机制的书籍，主要讨论了数据回滚、重做、缓存以及共享SQL等关键内部运作原理。这本书旨在简化复杂的11g手册，集中讲解数据库引擎的核心部分，而不是涵盖所有细节。作者在描述过程中，以逻辑I/O操作为例，指出虽然有1164种不同的函数执行这个操作，但书中提供的是通用的描述，而非详尽的分解，以保持内容的可读性。【Oracle核心知识详解】 1. 数据回滚（Undo）与重做（Redo）： - 数据回滚：Oracle使用Undo表空间来记录事务对数据的修改，在事务回滚或进行一致性读取时使用这些信息。 - 重做：当系统崩溃或介质故障发生时，Oracle利用Redo日志中的信息来恢复数据，确保事务的持久性。 2. 事务与一致性（Transactions and Consistency）： - 事务管理是数据库的关键特性，保证了数据的一致性和完整性。Oracle通过事务开始、提交和回滚命令来管理事务状态。 - 一致性约束是确保数据遵循特定规则的机制，例如外键约束、唯一性约束等。 3. 锁定与闩锁（Locks and Latches）： - 锁用于控制多个用户对同一数据的并发访问，防止数据不一致。Oracle支持多种锁类型，如行级锁、表级锁等。 - 闩锁是轻量级的同步机制，用于保护内存结构，防止竞态条件。 4. 缓存与副本（Caches and Copies）： - 数据缓冲区缓存（Buffer Cache）存储了数据库的数据块，减少磁盘I/O，提高性能。 - 重做日志缓冲区（Redo Log Buffer）存储即将写入到重做日志文件的更改。 - 一致性副本（Shadow Pages）用于在后台进程执行操作时，保持数据一致性。 5. 写入与恢复（Writing and Recovery）： - 写入过程涉及将数据从缓存写入数据文件，并更新Redo日志。 - 恢复机制是Oracle在系统故障后重建数据库的关键步骤，依赖于Redo日志来恢复未提交的事务。 6. 解析与优化（Parsing and Optimizing）： - SQL解析是将SQL语句转化为执行计划的过程，Oracle使用解析器来完成此任务。 - 查询优化器是Oracle的核心组件，它选择最佳执行路径来运行SQL语句，基于统计信息和成本模型。 7. 分布式数据库（RAC and Ruin）： - RAC（Real Application Clusters）允许数据库跨多台服务器运行，提供高可用性和负载均衡。 - 书中可能讨论了RAC的挑战和潜在的问题，以及如何避免或解决这些问题。 8. 调试与诊断（Dumping and Debugging）： - 附录可能涵盖了如何使用Oracle的调试工具，如跟踪文件、警告日志等来诊断和解决问题。此外，书中还包含了术语表和索引，方便读者查找和理解专业概念。对于DBA和开发者来说，这本书提供了一个深入理解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.

www.it-ebooks.info

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.

www.it-ebooks.info

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

www.it-ebooks.info

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

www.it-ebooks.info

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:

www.it-ebooks.info

剩余276页未读，继续阅读

iamarat

粉丝: 0
资源: 8

Oracle数据库核心机制解析

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.-带书签超清文字版

AttributeError: module 'pandas.core.internals.blocks' has no attribute 'new_block'

File D:\Anaconda3\lib\site-packages\pandas\core\internals\managers.py:1724 in as_array arr = arr.astype(dtype, copy=False)

最新资源