[bbk2194] 第35集 - Chapter 09-Optimizing Sore Perations(04)

OEM

PGA Target Advice Histograms

• V$PGA_TARGET_ADVICE_HISTOGRAM predicts how histograms shown in V$SQL_WORKAREA_HISTOGRAM evolve.
• STATISTICS_LEVEL must be set to at least TYPICAL.

Auto PGA and OEM

Overview

The automatic sort area management feature is :

• Easier to set up and size than the *_AREA_SIZEP parameters
• Easier to monitor using the advisory view

通常情况下，当多用户的时候，Oracle强烈推荐使用auto方式管理PGA；但是对于少量用户或者跑一些批量程序的时候可以设置成manual模式（一般仅限于SESSION级别:例子如下）

SQL>ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
SQL>ALTER SESSION SET SORT_AREA_SIZE=your size;

 

The Sorting Process

If sort space requirement is greater than SORT_AREA_SIZE:

Sort Area and Prameters

The sort space is in:

• The PGA for a dedicated server connection

• The shared pool for Oracle Shared Server connection

如果oracle database server是dedicated server 模式，sort area是存放在PGA里面的.

如果oracle database server是shared server模式，sort area是存放在SGA里面的.

控制sort area尺寸大小的两个参数1->sort_area_size;2->sort_area_reatined_size;

 

Tuning Sorts

• Use automatic sort area management.
• Avoid sort operations whenever possible.
• Reduce swapping and paging by making sure that sorting is done in memory when possible.
• Reduce space allocation calls by allocating temporary space appropriately.

Sorting and Temp Space

 

Create a temporary tablespace by using:(临时表空间与普通表空间的创建还是有一些细微的差别)

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '$HOME/ORADATA/u06/temp01.dbf' size 200M;

 

普通表空间中，通常情况下包含多个segment.但是临时表空间，只包含一个segment.多个用户使用temporary tablespace的话，就是使用不同的extent.