Oracle architecture 翻译

此翻译由ioracle实验室完成,旨在方便大家学习。我们在翻译的过程中对原文中的一些语言进行了更加本土化的调整。如果英文较好,建议直接读英文文档或是忽略字幕。如有任何建议,请联系我。BTW 老史说的太快了,如果感情再投入点就好了

本篇内容简介:

1. 数据库开启过程中到底发生了什么(可先参阅站内文章:数据库启动

2. 客户机如何连接数据库

3. SGA/PGA

4. 一点点锁机制的内容

5. 更新、删除数据时数据库内部探究(涉及数据文件、数据高速缓存、重做日志缓存、撤销段、SCN、CKPT、LGWR等)

(以下是中英对照翻译,打包下载和视频版本随后奉上,敬请期待。文字版比视频版多一部分内容,请各位自行对照。以下文字版本皆为从Stéphane Faroult先生处获取的原始文档)

Hello,My name is Stéphane Faroult, I am a database performance consultant. The first Oracle database I ever installed was a beta version of Oracle 5, an awful number of years ago. I have published two books with O’Reilly, « The Art of SQL » and more recently « Refactoring SQL Applications ».

大家好,我是Stéphane Faroult,一名数据库性能咨询师.

我安装第一个Oracle数据库系统已经是很久之前的事情了,那时还是Oracle5测试版.

我通过O’Reilly出版公司出版过两本书,一本是《SQL语言艺术》,另一本是最近出版的《SQL应用重构》.

For me, there is a clear link between performances and the understanding of what happens inside the database. I’ve had the opportunity to witness that for many developers «the « persistence layer » is kind of cloudy. I’d like to take you behind the scene, and explain without getting too much into details how everything works.

在我看来,数据库性能和数据库内部构造是有必然的联系的。

我曾目睹过无数开发人员为因搞不懂持久层而内牛满面。接下来,我会在不过分涉及细节的情况下,带你看看数据库在后台如何工作,帮助你解开谜团。

I’ll start by explaining what happens when you start the database, something which is most often automatically performed by scripts when a machine boots. I’ll talk more precisely about the Unix implementation of Oracle, but it’s not massively different under Windows.

首先,我来解释下数据库启动过程中到底发生了什么,有一些东西会在这一过程中通过脚本自动执行。

我的Oracle数据库是在Unix下工作的,别担心,这与Windows下的没什么太大区别。

An administrator who wants to manually start a database (called a server by some other products) must have at least two important variables set in his environment or in the registry : a variable that says where the distribution of Oracle has been installed, called ORACLE_HOME, and a variable that identifies the database, called ORACLE_SID. You can have on the same machine several different databases running, that may be running under different versions of Oracle.

作为一名数据库管理员,如果你想手动启动数据库(有些产品中称之为服务),你必须在你的运行环境或是机器注册表中配置过以下两个重要的变量:ORACLE_HOME,用来说明oracle具体安装在哪;ORACLE_SID,用于标识你要运行的数据库。这样你就可以在一台机器上运行多个数据库了,这些数据库可以是不同的Oracle版本。

The administator will run the sqlplus program, and « connect » – actually the connection is dummy at this stage, it’s just a check that the program is invoked from a Unix or Windows account with the right privileges. You can then run the magical startup command.

数据库管理员可以运行sqlplus程序或者使用connect命令,注意这个时候的连接其实是伪连接(注:对于DBA的验证是系统验证),它仅仅是对程序是否被Unix或者windows中拥有正确权限用户调用的核查.登陆sqlplus后你才能使用startup命令开启数据库.

The program starts by reading a file called spfile<ORACLE_SID>.ora that contains the parameters for the database identified by ORACLE_SID. It’s a binary file, and you may hear about the init.ora file that is a text file that historically predates the binary file.

程序启动的时候会读取一个叫做spfile<ORALCE_SID>.ORA的文件,他包含了用于唯一识别各个数据库的ORALCE_SID,它是一个2进制文件。在它之前使用的是init.ora文本文件

With a binary file, parameters are modified using sqlplus, and many of them can by dynamically changed when the database is up and running.

如果使用spfile作为参数文件启动,那么其中的很多参数可以在数据库运行的过程中,通过sqlplus进行动态的修改。

Database parameters will give the size of a shared memory area that is allocated at this stage and which is called the System Global Area, or SGA.

在这个阶段,数据库参数会决定在系统全局区中(简称SGA)共享内存的大小.

This is where Oracle works.

数据库的多数工作在SGA中完成

A number of utility processes will also be started.

一些后台进程也在这一阶段开启.

The number increases with each version of Oracle, some of them are mandatory, others are optional.

它们的数量随着版本的升级而增加,其中一些是强制性运行的,而另一些则是可选的.

Under Windows, they aren’t processes but threads. All the processes are running the same oracle program and have the SGA in their address-space.

在WINDOWS下它们不是进程而是线程.所有的进程都会运行同一个oracle程序,它们在各自的地址空间中会有自己的SGA.

It sometimes look like they are consuming a lot of memory, but actually most of this memory is shared

从表面看起来这些进程会消耗很多的内存,而实际上这些内存绝大多数是共享使用的.

When memory is allocated and processes started, SQL*Plus displays the total SGA size as well as the size of four subparts, the fixed part, the variable part, the database buffers and the redo buffers.

当内存已被分配并且进程启动之后,SQL*Plus会显示SGA的总值和4个子区域的大小:固定区、可变区、数据库缓冲区和重做缓冲区

At this point, Oracle is going to open and read a file that is so critical that it’s always duplicated : the control file, found at a location that is specified in the parameter file.

在这时,ORALCE将会读取一个非常重要的文件,重要到它总是被拷贝以防丢失:它就是控制文件,他的位置在参数文件中被具体指明了.

This is where Oracle finds the names of all the data files, temporary files and log files that together make up the database.

在控制文件中可以找到所有数据文件、临时文件和日志文件的名称,它们3者便组成了数据库

The control file will also allow to check global consistency, whether something needs to be restored and whether the database was properly shutdown or if some unfinished transactions will have to be rolled back.

控制文件也会去检查全局的一致性,去判断是否有一些东西需要被恢复(译者注:通过对比控制文件与数据文件的SCN),数据库之前是否被正确的关闭或者是否之前存在一些尚未完成的事务需要回滚.

Without a control file, your database is dead

没有了控制文件,你的数据库就杯具了,如果你搞不定,就去蹲墙角,画圈圈去吧。

After passing this stage, SQL*Plus will display that the database is mounted, which mostly means that all files are identified. The only step that remains is the opening of the various files for read/write operations.

这个阶段以后,SQL*Plus会显示数据库已被加载,这意味着所有的文件都被确认了.最后一步就是打开文件进行读写操作。

When files are opened, then the database is open and users who aren’t database administrators can connect.

当文件打开之后数据库才处于真正的打开状态,非DBA的用户才可以连接.

That is, they can connect if they are logged onto the machine that hosts the database.

也就是说,非DBA用户只有先通过装有数据库的机器的主机验证后,才能连接数据库

If you want to enable users or an application server to connect from another machine, which is the most common case, you have to launch with the lsnrctl start command another program called tnslsnr, which is the “listener” that waits for incoming connections. After displaying a lot of intimate details about its configuration, found in the listener.ora file, the program runs in the background and everything is in place.

如果您想授权一个用户或者程序从另一个主机上连接,最常用的方法是你需要通过lsnrctl命令启动另一个叫做tnslsnr程序,它是一个’监听器’,用来监听各个连接请求.在屏幕上显示了listener.ora中一些许多关于配置的隐信息并提示命令运行成功之后,tnslsnr便在开始在后台执行工作。

Let’s now contemplate a client program that runs on another machine.

接下来让我们看看在客户端应该如何配置。

If it wants to access the database, it needs to provide, first of all, three pieces of information: the name of the host on which Oracle runs, the port on which tnslsnr is listening, and the service, that is in practice the identifier of the database you want to connect to.

如果客户端想去连接数据库,它就至少要首先提供3个信息:1.运行ORALCE的主机名称2.tnslsnr监听的端口3.服务,实际上就是你想要连接的数据库标识符

There are several ways to provide these pieces of information.

有以下几种方式可提供这些信息。

You can directly provide everything in the function that performs the connection request; this is for instance what you do with JDBC and a pure java driver.

对于使用JDBC和Java驱动的实例,可以在请求连接时直接提供以上信息。

If your client is using the Oracle client libraries, you can also provide an alias, and your client will transparently fetch the associated information from a file called tnsnames.ora.

如果是您使用ORALCE的客户端,你可以提供别名,然后您的客户端会从一个叫做tnsnames.ora的文件中读取连接信息.

These two methods are by far the most common ones, but there are other possibilities, such as using an LDAP directory or system utilities such as the Network Information Services as a substitute to the tnsnames.ora file.

这就是目前最常用的两种方法,当然还有其他一些可行方法,例如使用LDAP目录或者系统应用,例如使用网络信息服务(Network Information Services)代替tnsnames.ora文件.

Obviously Oracle will not give you uncontrolled access to the data.

显然,ORALCE不会允许毫无约束的访问数据.

You must sign-on, so that you are identified and the database clearly knows what you are allowed to do. Usually, you will provide a username and a password.

您必须“签名”,这样数据库才能确认您的身份,数据库也能清楚的知道您的权限有哪些.通常需要通过用户名和密码来验证

 Authentification through an LDAP directory is also sometimes possible.

有时LDAP的直接认证也行.

In practice, your client issues a connection request that is handled by the listener.

实际上,您的客户端申请的链接是受监听器的监管的.

The listener will either spawn a sub-process that will run the oracle code and become the client’s dedicated server, or redirect the connection to an already existing shared server, which is a much less common configuration.

监听器或者会产生一个子程序用来运行ORACLE代码并生成客户端的专用服务,或者将链接改变指向,使它重新指向一个已经存在的共享服务,但这种方法很少使用.

The server process is, so to speak, the client’s proxy on the Oracle side.

可以这么说,服务端进程可以视作客户端在ORALCE上的代理.

It can access the SGA, that is shared between all processes.

它可以访问SGA,这个SGA在所有程序之间被共享.

It will also allocate a private memory area called PGA, where it stores what is private to the process and to the session.

数据库也会为服务器进程分配私有的内存空间,这块空间叫做PGA(程序全局区),那里存放着私有的进程和会话信息.

But let’s take a closer look to the various parts in the SGA. There isn’t much to say about the fixed system area.

然后让我们再仔细看看各种在SGA上的区域吧.在这里不过多涉及的那些固定的SGA区.

But the variable area contains the shared pool, which holds compiled versions of queries, as well as various buffer pools that will contain for instance data dictionary information.

动态区包含共享池,共享池中包含编译过的查询语句,除此以外,动态区中还有各种缓冲池,缓冲池中含有实例的数据字典信息.

The data buffer contains data from the tables but also index structures and various work areas.

在数据缓冲区中存有的数据包括表和索引结构的还有各种工作区域里的。.

Everything is split in pages or blocks; the block is the Oracle basic storage unit, and a typical size for a block is 8K.

所有事物都被分成页或者块.块是ORACLE中的基本存储单元,每一个块的默认大小是8K.

Whenever you query or change the content of a table, you operate in memory on blocks that are in this cache.

无论何时查询表还是修改表,所做的操作都会被以块的形式记录在数据缓冲区中.

Finally, a fourth area is used to store information before it is written to log files to ensure transactional consistency even in the occurrence of a crash.

最后来说一下第四个区域,它是用来存储被写入到日志文件之前的信息(重做信息),以保证即使出现突发性的崩溃,数据库的工作也能持续进行。

The parts that really matter to understand how Oracle works are the shared pool, the data buffer and the log buffer.

理解共享池,数据缓冲区和重做日志缓冲区对理解数据库如何工作非常有帮助。.

This is where the dedicated server process will operate, as well as the main utility processes started with the database.

因为这些(原文中This is应为These are)就是专用服务进程进行操作的位置,也是主后台进程在数据库程序中启动的地方.

Let’s see what happens when the client issues an SQL query.

我们通过一条SQL查询语句的执行过程来看看数据库到底如何工作.

The query arrives as text to the server process that will start by computing a hash value that is used as a checksum.

查询命令通过文本的方式到达服务端进程,服务进程会通过计算hash值来校验.

Then the server will check whether this query has already been recently executed, and will inspect the shared pool.

然后服务端会检查这个查询指令是否在最近的一段时间里被执行过进而会查询共享池.

If the query is found, the analysis stops here, this phase is called soft-parsing and the server process can execute the query.

如果在共享池中发现了这条指令,分析器就此终结,然后由进程服务执行这条指令, 这个过程叫做软解析.

If a prior query with exactly the same text cannot be found, then we have hard-parsing that is CPU intensive and locks some shared resources.

如果之前未运行过相同的语句,我们便会使用硬解析.它使CPU负担加重并且锁住部分共享资源(注:通过锁机制).

SQL syntax must be analyzed, the objects that are referred to in the query must be identified, some synonyms may have to be translated, and the best execution plan must be determined among sometimes a very large number of possible execution plans.

在此期间,SQL的句法被分析,在SQL语句中使用到的对象被确定,一些同义词被翻译并且要在多个执行方案中筛选出最优方案。.

It may cause recursive queries on the data dictionary and input/output operations.

这可能会引起数据字典和I/O操作的重复递归查询

This is why you shouldn’t concatenate to the text of a query identifiers that change with every call, but you should pass them as parameters, as if the query were a function.

这就是为什么您不应该在每次调用发生改变的时候都连接到查询标识符文本,而应该把它们作为参数一样传送的原因,就好像它们是一个函数一样.

Once the query is analyzed and compiled, it is going to be loaded into the shared pool and executed.

一旦查询指令被分析和编辑完毕,它会被放入共享区域中并被执行.

 It will then reference pieces of data that may or may not be in the data buffer.

然后它会查找一些数据片,这些数据片可以存在于数据缓冲中也可以不在那里.

If data blocks aren’t in memory, the server process fetches them from the data files and loads them.

如果数据块不在内存中,服务器进程会把它们从数据文件中读取并进行载入内存.

Blocks that have been modified are written asynchronously to the datafile by one or several database writer processes.

被改变的块区域会被一个或者多个数据库写进程异步的写入数据文件.

When you update data, mechanisms for analyzing the statement and uploading data blocks into memory are the same ones.

当您更新数据时,在内存中存在的分析和更新数块的机制是同一个.

But before modifying data, initial values are copied to a work area called “undo segment”.

但是在修改数据之前,先前的数值会被拷贝到一个叫做“撤销段”的工作区域中. 

Another process that wants to read data being modified isn’t blocked, but reads data from the undo segment instead of reading from the table block. Initial and new value are also copied to the redo buffer; if the redo buffer fills up, the utility process LGWR, or log writer, dumps its content to a log file.

如果在修改过程中有另一个进程想读取被修改的这一块的数据,它会从’撤销段’中读取,而不是从数据在表中对应的位置读取(读取的值为先前值).修改前的数据和最新的数据都会被拷贝到重做缓冲区中,如果重做缓冲区被填满,LGWR后台进程或者日志写入器会将重做缓冲区中的数据写入重做日志文件.

When you commit the transaction, Oracle records that the initial value is no longer required for rolling back the change, it records that the change is validated and the LGWR process writes the transaction to the log file.

当您提交事务的时候,ORALCE记录的原数据不再需要被用来做回滚,数据文件记录已生效的改变并且LGWR进程将事务写入日志中(.

This time the operation is synchronous, and the server process waits for the go ahead before the call returns to the client.

这一次的操作是同步进行的,并且服务器进程在客户端向其发出调用之前一直处于等待状态的.

That means that if you commit every single update in a loop, you will waste a lot of time just waiting for acknowledgment.

这就意味着如果我们总是每更新一次数据便提交一次,会因为确认信息而浪费许多时间.

This is a very quick introduction, but I hope it will allow you to understand better what happens.

这虽是一次快速的介绍,但是我希望这会帮助您更好的理解数据库如何工作.

Thank you for your attention.

感谢您的观看.

作者:Stéphane Faroult
翻译:纪晓文,苗洁明
校对:仇实

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress