SQL之结束篇

最新推荐文章于 2024-04-10 19:38:46 发布

ciyinzhi8788

最新推荐文章于 2024-04-10 19:38:46 发布

阅读量279

点赞数

处理一个查询语句的三个阶段以及相关后台进程的简单介绍[@more@]

sql之28 sql的执行

处理一个查询语句的三个阶段

第一阶段：Parse:

Search for identical statement in the shared pool

Check syntax, object names, and privileges

Lock objects used during parse

Create and store execution plan

第二阶段：Execute: Identify rows selected

第三阶段：Fetch: Return rows to user process

对shared pool的理解

The shared pool has two primary components：Library cache and Data dictionary cache

在shared pool中主要有两种区域：一是library cache 二是data dictionary cache

对library cache的解释

The library cache stores information about the most recently used SQL statements in a memory structure called a

shared SQL area. The shared SQL area contains:

The text of the SQL statement

The parse tree: A compiled version of the statement

The execution plan: The steps to be taken when executing the statement

The optimizer is the function in the Oracle Server that determines the optimal execution plan.

在library cache中保存的有SQL语句、解析路径、执行计划

If a SQL statement is reexecuted and a shared SQL area already contains the execution plan for the statement, the server process does not need to parse the statement. The library cache improves the performance of applications that reuse SQL statements by reducing parse time and memory requirements. If the SQL statement is not reused, it is eventually aged out of the library cache.

如果重复执行一个SQL语句，在library cache中保存的仍有这个SQL，ORACLE不需要再解析，library cache由此提高性能。如果library cache中的SQL语句得不到重用，它就会最后老去。

对data dictionary cache的解释

The data dictionary cache, also known as the dictionary cache or row cache, is a collection of the most recently used definitions in the database. It includes information about database files, tables, indexes, columns, users, privileges, and other database objects.

During the parse phase, the server process looks for the information in the dictionary cache to resolve the object names specified in the SQL statement and to validate the access privileges. If necessary, the server process initiates the loading of this information from the data files.

在data dictionary cache中保存的有数据库对象的定义及对象的权限信息，在SQL解析阶段，服务器进程在数据字典缓存中查找对象名称，以确认对象存在，及相应的权限存在。如果需要，服务器进程开始从数据文件中加载这些数据。

对db buffer cache的理解

When a query is processed, the server process looks in the database buffer cache for any blocks it needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the buffer cache. Because subsequent requests for the same block may find the block in memory, the requests may not require physical reads. The Oracle Server uses a least recently used algorithm to age out buffers

that have not been accessed recently to make room for new blocks in the buffer cache.

当一个查询执行时，oracle从db buffer cache中查找它需要的块，如果没有找到所需要的块，它从数据文件中读取，并把读取的块在db buffer cache中放一个copy，此时发生物理读。Oracle使用最少最近使用算法来老化不使用的块，以使新块能放入buffer cache中。

对PGA的理解

In a dedicated server configuration, the PGA of the server includes these components:

• Sort area: Used for any sorts that may be required to process the SQL statement

• Session information: Includes user privileges and performance statistics for the session

• Cursor state: Indicates the stage in the processing of the SQL statements that are currently used by the session

• Stack space: Contains other session variables

The PGA is allocated when a process is created and deallocated when the process is terminated.

在常规的专用服务器的配置下，PGA包括以下内容：

Sort area排序区、session的信息如用户权限及性能统计信息、cursor说明、stack space包含其它session变量。

PGA在一个process创建时分配，在这个process终止时释放

DML Execute Phase

To execute a DML statement:

• If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.

• The server process places locks on the rows that are to be modified.

• In the redo log buffer, the server process records the changes to be made to the rollback and data.

• The rollback block changes record the values of the data before it is modified. The rollback block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.

• The data blocks changes record the new values of the data.

执行一个DML语句时，如果数据和回退块不在db buffer cache中，服务器要从数据文件读入到db buffer cache中；

锁定那些修改的行；

服务器记录对回退段及数据段的改变，都记录到redo log buffer中；

前映像记录在回退段中，用以回滚操作；

新内容记录在数据块中；回退块和数据块都在db buffer cache中。

The server process records the before image to the rollback block and updates the data block. Both of these changes are done in the database buffer cache.

Any changed blocks in the buffer cache are marked as dirty buffers: that is, buffers that are not the same as the corresponding blocks on the disk.

The processing of a DELETE or INSERT command uses similar steps. The before image for a

DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.

Because the changes made to the blocks are only recorded in memory structures and are not written immediately to disk, a computer failure that causes the loss of the SGA can also lose these changes.

在这个过程中，产生SGA中的数据块与数据文件中的块不一致。

在delete和insert过程中，前映像选择比update更简单的保存方式。

Redo Log Buffer

• Has its size defined by LOG_BUFFER

• Records changes made through the instance

• Is used sequentially

• Is a circular buffer

COMMIT Processing与系统改变号

System Change Number

Whenever a transaction commits, the Oracle Server assigns a commit system change number (SCN) to the transaction. The SCN is monotonically incremented and is unique within the database. It is used by the Oracle Server as an internal time stamp to synchronize data and to provide read consistency when data is retrieved from the data files. Using the SCN enables the Oracle Server to perform consistency checks without depending on the date and time of the operating system.

每一次commit，都会产生一个SCN，SCN作为一个时间戳用来同步数据，以及当从数据文件读取数据时保证读一致性。SCN使数据库能够一致性检查而不依赖操作系统的日期时间。

Steps in Processing COMMITs

When a COMMIT is issued, the following steps are performed:

• The server process places a commit record, along with the SCN, in the redo log buffer.

• LGWR performs a contiguous write of all the redo log buffer entries up to and including

the commit record to the redo log files. After this point, the Oracle Server can guarantee that the changes will not be lost even if there is an instance failure.

• The user is informed that the COMMIT is complete.

• The server process records information to indicate that the transaction is complete and that

resource locks can be released.

Flushing of the dirty buffers to the data file is performed independently by DBW0 and can occur either before or after the commit.

提交以后，LGWR会将重做缓存区的所有条目（可能包括未发commit的记录）写入重做日志文件中。写完之后，数据库能保证即使实例失败也不会丢失数据。之后，通知用户提交完成。服务器释放所持有的锁。提交不会造成立即写数据文件。

LGWR进程

1 LGWR何时写日志文件？

• When a transaction commits

• When the redo log buffer is one-third full

• When there is more than a megabyte of changes recorded in the redo log buffer

• Before DBW0 writes modified blocks in the database buffer cache to the data files

2 一个实例最少需要哪几个后台进程？