Objectives

After completing this lesson,you should be to do the following:

• List the operations that use temporary space.
• Create and monitor temporary tablespaces.
• Identify actions that use the temporary tablespace
• Describe and different disk sorts and memory sorts
• Identify the SQL operations that require sorts
• List ways to reduce total sorts and disk sorts
• Determine the number of memory sorts performed
• Set parameters to optimize sorts

整个Optimizing Sort Operations章节的核心目标，就是如何确定PGA_AGGRGATE_TARGET参数的大小;

SQL Memory Usage

• Memory-intensive SQL operators:
  • -Sort-based(sort,group-by,rollup,window,and so on)
  • -Hash-join
  • -Bitmap operators(merge and inversion)
• Concept of work area:Memory allocated by a memory-intensive operator to process its input data
• Performance impact of memory:
  • -Optimal:Input data its into the work area(cache).
  • -One-pass:Perform one extra pass over input data.
  • -Multi-pass:Perform several extra passes over input data.

PGA and SQL Memory

The Program Global Area (PGA) is a private memory region containing data and control information for a server process.Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it.An example of such information is the runtime area of a cursor.Each time a cursor is executed,a new runtime area is created for that cursor in the PGA memory region of the server process exexuting that cursor.

Note:Part of the runtime area can be located in the SGA when using shared servers.

For complex queries(for example,decision support queries),a big portion of the runtime area is dedicated to work areas allocated by memory intensive operators,such as the following:

• Sorted-based operators,such as ORDER BY,GROUP BY,ROLLUP,and window functions
• Hash-join
• Bitmap merge
• Bitmap create
• Write buffers used by bulk load operations

A sort operator uses a work area(the sort area) to perform the in-memory sort of a set of rows.Similarly,a hash-join operator uses a work area(the hash area) to build a hash table from its left input.

The size of a work area can be controlled and tuned.Generally,bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption.Ideally,the size of a work area is big enough that it can accommodeate the input data and auxiliary memory structures allocated by its associated SQL operator.This is known as the optimal size of a work area.When the size of the work area is smaller than optimal,the response time increase,because an extra pass is performed over part of the input data.This is known as the one-pass size of the work area.Under the one-pass threshold,when the size of a work area is far too small compared to the input data size,multiple passes over the input data are needed.This could dramatically increase the response time of the operator .This is known as the multi-pass size of the work area.For example,a serial sort operation that needs to sort 10GB data needs a little more than 10GB to run optimal and at least 40MB to run one-pass.If this sort gets less that 40MB,then it must perform several passes over the input data.

The goal is to have most work areas running with an optimal size(for example,more than 90% or even 100% for pure OLTP system),while a smaller fraction of them are running with a one-pass size(for example,less than 10%),Multi-pass execution should be avoided.Even for DSS systems running large sorts and hash-joins,the memory requirement for the on-pass executions is relatively small.A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.

Automatic PGA memory management simplifies and imporves the way PAG memory is allocated .By default,PGA memory management is enabled.In this mode,Oracle dynamically adjusts the size of peration of the PGA memory dedicated to work areas,based on 20%,of the SGA memory size,The minimum value is 10MB.

 

*一个PGA尺寸设置大小合适、恰当的系统，不管是OLAP系统，还是OLTP系统或者是DSS系统，都不应该或者发生多次扫描的情况，在进行排序或者分组查询的情况下.

*Automatic  PGA memory management管理思想:通常情况下，每个用户连接到Oracle 数据库，oracle server 都会分配一个work area，假设为10M，那么100个用户连接上来就会分配1G.1000个用户连接上来就会分配10G的Work area.显然这样随着连接Oracle database 的用户越来越多,oracle server 分配的内存也会越来越多，这样明显是不合理的.Automatic PGA memory management的管理方式解决这一问题:DBA只需要设定一个最大上限值， 不管你多少用户连接上来，分配的work area总大小都会在设定参数值尺寸之下.通过这样，可以有效的管理work area大小分配管理.

要不然，DBA就会不断的设置*_AREA_SIZE参数值，来不断满足、适应work area尺寸大小.现在通过设置automatic PGA memory management这样一个尺寸的大小，来自动管理各个work area尺寸的大小.

Auto Sort Area Management

• Dynamically adapts the SQL memory allocation based on:
  • -PGA memory available
  • -SQL operator needs
  • -System workload
• Imporves manageability:
  • -No need to set *_AREA_SIZE parameters
  • -DBA sets a memory target:PGA_AGGREGATE_TARGET
• Improves performance:
  • -PGA memory is realy returned to the OS.(使用完操作系统内存后，会立即返还给操作系统)
  • -Memory is allocated to the operation to maximize throughout.
  • -Overall memroy utilization is maximized by dynamically adapting memory with workload variation.
  • -An operation adapts its memroy usage during execution.

Performance Impact