PGA、临时表空间、direct path

Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.


PGA中只有Work Area受参数PGA_AGGREGATE_TARGET影响，Work Area就是包含sort, group-by, hash-join,bitmap merge, and bitmap create这些操作，当这些操作使用的内存大于Work Area范围时就会使用临时表空间了


如果sort排序操作超过Work Area范围怎么办？
1.查看排序字段是否有索引，没有索引增加索引即可
2.如果排序字段有索引，但是行数特别大比如10亿行，没有办法，只能去优化逻辑，减少select的结果行
3.就算排序操作超过4G，数据库也能执行下去，不过这个时候会把结果行拿到temp表空间去排序操作，如果temp表空间也不足了，除了增加temp表空间外真没辙了

sort排序是对select的结果行进行排序，不是对select语句中对象涉及到的所有逻辑读物理读进行排序，所以涉及到排序不会特别大。当排序占用内存特别大说明结果行特别多而且排序字段没有索引，这样就只能扫描所有结果行来确定没行的排序字段大小，类似咱们全表扫描的概念。

索引是顺序的，所以排序字段有索引的话，排序其实就是直接通过顺序的索引键值去找一个个的rowid对应的行

所以，出现temp表空间不足，原因可能是pga不足引起的，pga不足，可能是sort排序消耗太多内存，排序消耗太多内存可能是排序字段没有索引



临时表空间主要用途是为存储临时表、排序、管理索引、访问视图等操作时提供临时的运算空间。当PGA中sort_area_size大小不够时，一些消耗Work Area的操作就会放入临时表空间里进行操作，同时如果有异常情况的话，也会被放入临时表空间，正常来说使用完TEMP表空间后，Oracle是会自动释放掉临时段的。这里的释放只是标记为空闲、可以重用，其实实际占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。临时表空间跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间是NOLOGGING模式，不会存储永久类型的对象，所以它不会也不需要备份，即使数据库损毁，Recovery也不需要恢复临时表空间。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。

消耗临时表空间的主要SQL动作：
temp table、index create、view、index rebuild、Order by、group by、Distinct、Union、intersect、minus、Sort-merge、hash join、analyze、create table as、move tablespace、direct path
以上动作如果产生数据过大，那么就会 消耗非常大的临时表空间




direct path read主要发生在大表直接读取到PGA时和以上消耗Work Area的sort, group-by, hash-join,bitmap merge, and bitmap create操作，大表读取是指服务器进程直接读取数据文件时直接加载到服务器进程的PGA内中当中，不会再进入SGA的buffer cache中以供后续的process使用。当PGA不足时就会使用临时表空间， 磁盘的读写效率是远远不及内存的，这样容易产生direct path read和direct path write等待。发生 direct path read 和direct path write等待说明PGA不足，数据从PGA中引入到了临时表空间，导致临时表空间正在大量使用而产生的等待

11GR2 direct path read 认为达到buffer cache的2% 就是big table了,相关隐含参数_small_table_threshold

direct path read的好处是大表全表扫描不再消耗SGA，但是一张大表全表的读取次数太多时（多个session都对该大表全表扫描），就会不断消耗PGA，消耗IO，导致IO直接崩溃

关闭direct path read可以使用event 10949



direct path read and direct path read temp
When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event.

Check the following V$SESSION_WAIT parameter columns:
■P1: File_id for the read call
■P2: Start block_id for the read call
■P3: Number of blocks in the read call

Causes
This situation occurs in the following situations:
■The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
■Parallel slaves are used for scanning data.
■The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.

Actions
The file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves.

Sorts to Disk：Examine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.

Full Table Scans：If tables are defined with a high degree of parallelism, then this setting could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is adequate for the degree of parallelism. Consider using disk striping if you are not already using it or Oracle Automatic Storage Management

Hash Area Size：For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET


direct path read演示案例

把p1、p2的值代入到DBA_EXTENTS的file_id、block_id字段可以查出是哪个对象，通过AWR的top sql或v$sql.sql_text查看是否有该对象的语句，检查该语句的执行计划就可以查出问题所在

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2134429/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2134429/