转载

通过等待事件学习Oracle 体系结构：（二）谈谈PGA和临时表空间

三、通过direct path read/write temp 谈谈PGA和临时表空间：

官方描述：

When a process is writing buffers directly from PGA (as opposed to the DBWR writing them from the buffer cache), the process waits on this event for the write call to complete. Operations that could perform direct path writes include sorts on disk, parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations.

Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and cannot continue work until an I/O request completes.

当一个进程正从PGA直接写入缓冲到磁盘中，相对于DBWR进程从buffer cache 写入，这个进程要等待这个写入调用的事件完成。这些能执行直接路径写入的操作包含了，磁盘排序，并行DML操作，直接路径插入，并行create table as select 以及一些LOB相关的操作。

像直接路径读一样，如果I / O子系统支持异步写入，则等待的数量不等于写入调用发起的数量。会话会等待是否已经处理了PGA中所有的缓冲区，除非I / O请求完成，否则将不能继续工作。

其实direct path write temp是oracle 数据块直接被server process 直接从PGA写入到临时文件的一种直接路径访问的过程。

而direct path read temp这是便直接从磁盘中读取数据块到PGA中的一个过程。

在以上的两种过程中发生的等待时，oracle便会产生direct path read temp和direct path write temp等待事件。

以上的官方说明也说了，产生这些等待的操作，包括了磁盘排序，并行DML操作，直接路径插入，并行create table as select 以及一些LOB相关的操作这些，这也是一个I/O的过程。

那么为什么磁盘排序和并行等与PGA有关系呢？临时表空间文件，pga，buffer cache这三者在Oracle的运行中又是怎样的关系呢？

我想我们是该先了解清楚了。

PGA是什么？

是否还记得一.ORACLE 体系结构简述中提到的server process，PGA（Program Global Area程序全局区）便是数据库在为一个客户端启动一个server process 时分配的一个非共享的内存区域，这个区域是用来存放私有数据和共享信息等的，一个PGA也只能被拥有它的那个服务进程所访问，只有这个进程中的Oracle代码才能读写它。

PGA中有什么？

PGA由两组区域组成：固定PGA和可变PGA，固定PGA的大小时固定的，包含了大量原子变量、小的数据结构和指向可变PGA的指针。可变PGA包括私有SQL区（私有SQL区包含了绑定变量值和运行时期内存结构信息等数据），游标和SQL区和会话内存(一段用于保存会话变量（如登录信息）和其他预会话相关信息的内存)。

oracle 提供了两种模式：专用服务器连接模式和共享服务器连接模式。

在专用服务器连接模式下，UGA在PGA中分配。共享服务器连接模式，UGA在SGA中的Shared Pool或Large Pool中分配。

再看一下：

PGA（Process Global Area），即程序全局区，一个进程的专用的内存区。（可以理解成面向进程）

UGA（User Global Area），即用户全局区，由用户会话数据、游标状态和索引区组成。（相当于面向会话，专用服务器模式下一个PGA 对一个UGA,共享服务器模式下一个PGA可能对应多个UGA。）

CGA (CALL Global Area），即调用全局区，如排序区，HASH JOIN区，位图合并区等。（存在是瞬间的。它只存在于一个调用过程中，是PGA的subheap）。

回到前面的一条sql :select first_name,salary from HR.EMPLOYEES where first_name='Shelli'; 此时，我想查询公司所有员工的工资，并按高到低顺序排序，于是我会这样写，select first_name,salary from HR.EMPLOYEES order by salary desc;

在最开始数据库获取了first_name='Shelli' 的数据在数据库缓冲区中，但此时，我们想查询所有人的工资，于是，Oracle从磁盘中获取所有人的工资数据到数据库缓冲区中，在正常情况下，Oracle便可以把取到的数据返回给你，但别忘了，我们要的是工资按高到低顺序排序，这个排序便是在PGA中完成了，更确切的说，是在UGA 的sort_area中。

疑问：如果查询的表特别大，是否pga便无限量容纳这些排序的结果？答案否。

再看看，我们现在学习的等待事件是什么？

direct path read temp和direct path write temp，其实这是一个I/O相关的等待事件，当数据库出现大量的排序操作时，pga无足够空间存放这些数据时，便会通过读写磁盘来分担，而这个读写的对象便是临时表空间。

说说临时表空间：

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实际占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。（摘录）

而大家应该都知道，在计算机硬件中，磁盘的读写效率是远远不及内存的，在以上的过程，在Oracle 在频繁的读写临时表空间文件时，便容易产生了等待，这便是direct path read temp和direct path write temp等。

不仅仅是排序，还有其他几类操作也会引起direct path read temp和direct path write temp等待。

Causes for the direct path read temp wait

To reduce the direct path read wait event and direct path read temp wait event:

High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.

Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.

Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.

Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system

Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.

Hash area size – For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small.

1.大量的磁盘排序操作，order by, group by, union, distinct, rollup,max() 无法在PGA中完成排序，

需要利用temp表空间进行排序,当从临时表空间中读取排序结果时，会产生direct path read.

　　2. SQL语句的并行处理

　　3. 直接路径加载。

4.服务进程先于I/O，服务器进程处理buffer的速度快于I/O返回buffer的速度。

　　5. 数据仓库，经常大数据量的排序也会引起等待。

6.大量的Hash Join操作，利用temp表空间保存hash区。

面对这种等待事件，如何优化：

（摘录）

1、应用程序层
检查需要排序的sql语句是否已经最优化。不必要的排序操作会导致CPU浪费、PGA区域浪费、磁盘I/O浪费。从UNION和UNION ALL的性能差异上可以得知，只靠减少不必要的排序操作，也能解决许多问题。
2、oracle内存层
在进程上分配的工作区大小内一次性实现的排序称为One pass sort。与此相反的情况称为Multi pass sort。发生Multi pass sort时，排序工作过程中将排序结果读写到排序段（sort segment）区域，因此发生direct path read temp、direct path write temp等待。如果该等待大量发生，就可以适当提高pga_aggregate_target值，以此消除问题。

在Oracle 目前的版本都是使用自动 pga管理，因此针对pga_aggregate_target调整很重要，在pga是手动管理的年代，有三个参数对PGA影响最大。

SORT_AREA_SIZE：排序工作区，用于进程排序时存放排序数据的内存区域，它属于PGA

SORT_AREA_RETAINED_SIZE：排序完成后用于保存已排序数据的内存总量，它属于UGA

HASH_AREA_SIZE：存储散列列表所用的内存量。

3、系统层

提升I/O性能。

==============================================================================================

《通过等待事件学习Oracle 体系结构》系个人日常学习笔记积累，存在偏差的描述，请指出，相互学习。
==============================================================================================