[bbk2191] 第32集 - Chapter 09-Optimizing Sore Perations(01)

Auto Sort Area Managementk

Parameter for automatic sort area management:
- -PGA_AGGREGATE_TARGET(Ranges from 10MB to 4000GB)
- -WORKAREA_SIZE_POLICY （AUTO | MANUAL）(Oracle 推荐使用自动化管理)
Replaces all *_AREA_SIZE parameters

SQL> show parameter PGA_AGGREGATE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> show parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

正常情况下，oracle server 对workarea_size_policy参数的设置是AUTO的，同时还可以根据实际需要，将AUTO修改为MANUAL,以满足对某个特定用户的操作(比如说某个特定用户，需要对大概10GB的数据进行排序;那么就可以将参数值修改MANULA,专为此特定用户给予满足要求的尺寸设置).

Setting Initially

The value of the PAG_AGGREGATE_TARGET initializaiton parameter(for example 100000KB,2500MB,or 50GB)should be set based on the total amount of memory available for the Oracle instance.This value can then be turened and dynamically modified at the instance level.Example 7-2 illustrates a typical situation.

Leave 20% of the available memory to other applications.
Leave 80% of memory to the Oracle instance.
For OLTP:

PGA_AGGREGATE_TARGET=(total_mem*80%)*20%

For DSS:

PGS_AGGREGATE_TARGET=(total_mem*80*)*50%

Setting Initially Example

Assume that an Oracle instance is configured to run on a system with 4GB of physical memory.Part of that memory should be left for the operating system and other non-Oracle applciations running on the same hardwares system .You might decide to dedicate only 80%(3.2GB) of the available memory to the Oracle instance.

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems,the PGA memory typically accounts for a small fraction of the total memory available(for example,20%),leaving 80% for the SGA.
For DSS systems running large,memory-intensive queries,PGA memory can typically use up to 70% of that total(up to 2.2GB in this example).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

For OLTP:PGA_AGGREGATE_TARGET=(total_mem*80%)*20%
For DSS:PGA_AGGREGATE_TARGET=(total_mem*80%)*50%

where total_mem is the total amount of physical memory available on the system.

通过上述公式可以，进行初始值的设定；但是仅仅是初始值，在以后的业务发展过程中，可能随着workload的加大，当初的初始值可能会修改，那是必然的.

PGA Management Resources

Statistics to manage the PAG_AGGREGATE_TARGET initialization parameter

Views for monitoring the PGA work area include:
- -v$sql_workarea_histogram
- -v$pgastat
- -v$sql_workarea_active
- -v$sql_workarea
- -v$tempseg_usage
Views to assist in sizing the PGA work area are:
- -v$pga_target_advice(advice->oracle server根据日常使用情况,计算出的推荐值;advice是个好东西)
- -v$pga_target_advice_histogram

要仔细分析上图中各个动态性能试图之间的逻辑关系（关联关系）.

V$PGASTAT重点掌握cache hit percentage参数以及其计算公式.

V$SQL_WORKAREA_ACTIVE里面记录着当前正在活动的

instance启动之后，凡是进行排序的操作，通过V$SQL_WORKAREA_HISTOGRAM都可历史的记录，多少工作区是工作在optimal状态？多少工作区是工作在one-pass状态？多少工作区是工作在multi-pass状态.通过这些数据，就可以判断出PGA的设置是大还是小

SQL> select * from v$pgastat;

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                     1308622848 bytes
aggregate PGA auto target                          1056033792 bytes
global memory bound                                 130856960 bytes
total PGA inuse                                     135250944 bytes
total PGA allocated                                 159670272 bytes
maximum PGA allocated                               248479744 bytes
total freeable PGA memory                            16252928 bytes
process count                                              29
max processes count                                        39
PGA memory freed back to OS                        2343960576 bytes
total PGA used for auto workareas                           0 bytes

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
maximum PGA used for auto workareas                  29012992 bytes
total PGA used for manual workareas                         0 bytes
maximum PGA used for manual workareas                  541696 bytes
over allocation count                                       0
bytes processed                                    6328429568 bytes
extra bytes read/written                             34598912 bytes
cache hit percentage                                    99.45 percent
recompute count (total)                                114860

19 rows selected.