分类： Oracle
2010-03-31 22:09:03
在Oracle
9i之前，我们主要是通过设置sort_area_size、hash_area_size等参数值（通常都叫做*_area_size）来管理PGA的
使用，不过严格说来，是对PGA中的UGA进行管理。但是，这里有个问题，就是这些参数都是针对某个session而言的，也就是说设置的参数值对所有登
录到数据库的session都生效。在数据库实际运行过程中，总有些session需要的PGA多，而有些session需要的PGA少。如果都设置一个
很小的*_area_size，则会使得某些SQL语句运行时由于需要将临时数据交换到磁盘而导致效率低下。而如果都设置一个很大的值，又有可能一方面浪
费空间；另一方面，消耗过多内存可能导致操作系统其他组件所需要的内存短缺，而引起数据库整体性能下降。所以如何设置*_area_size的值一直都是
DBA很头疼的一个问题。
而从Oracle 9i起（当然也包括Oracle 10g）所引入的一个新的特性可以有效的解决这个问题，这个特性就是自动PGA管理。
首先，设置workarea_size_policy参数。该参数为auto（也是默认值）时，表示启用PGA自动管理；而设置该参数为
manual时，则表示禁用PGA自动管理，仍然沿用Oracle 9i之前的方式，也就是使用*_area_size对PGA进行管理。
然
后，DBA可以根据数据库的负载情况估计所有session大概需要消耗的PGA的内存总和，然后把该值设置为初始化参数
pga_aggregate_target的值即可。Oracle会按照每个session的需要为其分配PGA，同时会尽量维持整个PGA的内存总和不
超过该参数所定义的值。这样的话，Oracle就能尽量避免整个PGA的内存容量异常增长而影响整个数据库的性能。从而，就有效的解决了设
置*_area_size所带来的问题。
不过遗憾的是，Oracle 9i下的PGA自动管理只对专用连接方式有效，对共享连接方式无效。Oracle 10g以后对两种连接方式都有效。
在PGA中，对性能影响最大的就是SQL工作区了。通常来说，SQL工作区越大则对于SQL语句的执行的效率就高，从而对于用户的响应时间就越少。 理想情况下，SQL工作区应该可以容纳SQL执行过程中所涉及的所有输入数据和控制信息。当然，这只是理想情况，现实往往总是不能尽如人意，很多情况下 SQL工作区是不能容纳执行SQL所需要的内存空间的，从而不得不交换到临时表空间里。为了衡量执行SQL所需要的内存与实际分配给该SQL的SQL工作 区之间的契合程度，Oracle将所分配的SQL工作区大小分成以下三种类型。
optimal尺寸：SQL语句能够完全在所分配的SQL工作区内完成所有的操作。这时的性能最佳。
onepass尺寸：SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作。
multipass尺寸：由于SQL工作区过小，从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。
当系统整体负载不大时，Oracle倾向于为每个session的PGA分配optimal尺寸大小的SQL工作区。
而随着负载上升，比如连接的session逐渐增多导致同时执行的SQL语句越来越多时，Oracle就会倾向于为每个session的PGA分配onepass尺寸大小的SQL工作区，甚至是multipass尺寸的SQL工作区了。
我们一旦设置了pga_aggregate_target以后，所有的*_area_size就将被忽略。那么，我们该如何来设置该参数的值呢？这
依赖于数据库的用途，如果数据库为OLTP（联机事务处理）应用的，则其应用一般都是小的短的进程，所需要的PGA也相应较少，所以该值该值通常为总共分
配给Oracle实例的20%，另外的80%则给了SGA；如果数据库为OLAP（DSS）（数据仓库或决策分析）应用的，则其应用一般都是很大的，运行
时间很长的进程，因此需要的PGA就多。所以通常为PGA分配50%的内存。而如果数据库为混合类型的，则情况比较复杂，一般会先分配40%的初始值，而
后随着数据库的应用，而不断对PGA进行监控，并进行相应的调整。
比如，对于8GB物理内存的数据库服务器来说，按照Oracle推荐的，分配给Oracle实例的内存为物理内存的80%。那么对于OLTP应用来
说，pga_aggregate_target的值大约就是1310MB（（8192MB×80%）×20%）。而对于OLAP来说，则该值大约就是
3276MB（（8192MB×80%）×50%）。
当然，这里所说的都是对于一个新的数据库来说，初始设置的值。这些值并不一定正确，可能设置过大，也可能设置过小。必须随着系统的不断运行，DBA需要不断监控，从而对其进行调整。
Oracle为了帮助我们确定这个参数的值，引入了一个新的视图v$pga_target_advice。为了使用该视图，需要将初始化参数statistics_level设置为typical（默认值）或all。
SQL> select Target (M) Est. Cache Hit % Est. ReadWrite (M) Est. Over-Alloc |
该输出告诉我们，按照系统目前的运转情况，我们PGA设置的不同值所带来的不同效果。根据该输出，随着我们增加PGA的尺 寸，estd_pga_cache_hit_percentage不断增加，同时estd_extra_bytes_rw（表示onepass、 multipass读写的字节数）不断减小。从上面的结果我们可以知道，将pga_aggregate_target设置为60MB是最合理的，因为即便 将其设置为480MB，命中率也不会有所提高。
关于9i中PGA相关的一段中文解释
对于oracle9i，增加了自动“运行内存”调整的特性。
Oracle9i
为每个查询提供了一套自动的动态分配运行内存的机制。运行内存是一种在执行查询时，为特殊目的如排序和散列而分配的内存。在许多数据仓库环境中，数据仓库
服务器有 70% 以上的物理内存可被分配为运行内存。自动内存调整初看似乎就是一种可管理性。
然而，尽管自动内存调整毫无疑问地提高了可管理性，它的主要的优势却是在改善性能。自动内存调整不仅减轻了数据库管理员调整运行内存的负担，而且它的内存
分配方法远比手动调整内存参数的分配法更精确。 在 Oracle8i 中，数据库管理员用一些参数如HASH_AREA_SIZE 和
SORT_AREA_SIZE
（其它方面）来调节运行内存。这些参数控制着分配给每个查询的内存量。这些参数对于调节一个单一的查询是理想的，但若数据仓库有成百的并发查询，而每一个
查询有不同的内存需要，则对其管理员并不是最佳的。
在 Oracle9i 中，数据库管理员通过一个唯一的参数来调节运行内存，即
PGA_AGGREGATE_TARGET(需要WORKAREA_SIZE_POLICY=AUTO)。这个参数为使用运行内存建立了一个目标值。不论
在系统上正运行的并发查询是多还是少，Oracle 都将设法高效使用由这个参数指定的所有可用内存。
Alter system set pga_aggregate_target=32m;
通过自动分配运行内存，Oracle
将提高整个数据仓库的吞吐能力。在同样的性能水平上，数据仓库将支持更多的用户，因为数据仓库现在利用内存的效率更高。每个查询被分配的内存是基于它的具
体要求，并且当运行查询时，Oracle9i
会动态地调整内存分配以确保高性能。自动内存调整的特点将保证那些内存需求量多的查询能得到足够的内存，而内存需求量少的就不会分配给太多的内存。通过更
有效地使用内存，Oracle9i 提高了整体查询性能。 内部测试表明，此特性能提高内存绑定系统 20%
以上的性能。并且，此特性易于实现（因为它仅仅涉及改变一些初始化参数）。因此，对于所有要从 Oracle 的较早版本升级到 Oracle9i
的客户，它应该是一个重要的考虑事项。
来自metalink：Oracle9i 自动管理PGA内存
前两天客户端运行速度特别的慢，最后更改了pga_aggregate_target后有了显著提高。这是metalink上关于该参数的一篇文章。
Automatic PGA Memory Management
-------------------------------
Process Global Area, often known as the Program Global Area (PGA) resides in the
process private memory of the server process. It contains global variables and data
structures and control information for a server process. 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
executing that cursor.
The performance of complex long running queries, typical in a DSS environment,
depend to a large extent on the memory available in the Program Global Area (PGA).
which is also called work area.
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 accommodate the input data and auxiliary memory structures allocated by its
associated SQL operator. This is known as the optimal size of a work area (e.g.
a memory sort). When the size of the work area is smaller than optimal
(e.g. a disk sort), the response time increases, 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.
In Oracle8i administrators sized the PGA by carefully adjusting a number of
initialization parameters, such as, SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.
Oracle9i provides an option to completely automate the management of PGA memory.
Administrators merely need to specify the maximum amount of PGA memory available
to an instance using a newly introduced initialization parameter
PGA_AGGREGATE_TARGET. The database server automatically distributes this memory
among various active queries in an intelligent manner so as to ensure maximum
performance benefits and the most efficient utilization of memory. Furthermore,
Oracle9i can adapt itself to changing workload thus utilizing resources efficiently
regardless of the load on the system. The amount of the PGA memory available to an
instance can be changed dynamically by altering the value of the
PGA_AGGREGATE_TARGET parameter making it possible to add to and remove PGA memory
from an active instance online. Since the database engine itself is better equipped
to determine SQL execution memory requirements, database administrators should use
this feature and not try to tune the PGA manually. This should translate to better
throughput for large number of users on the system as well as improved response
time for queries.
The automatic SQL execution memory management feature is enabled by setting the
parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of
PGA_AGGREGATE_TARGET in the initialization file. These two parameters can also be
set dynamically using the ALTER SYSTEM command. In the absence of either of these
parameters, the database will revert to manual PGA management mode. In Oracle9i
Release 2, advisory for PGA_AGGREGATE_TARGET was introduced. Just like in Buffer
Cache Advisory, the PGA Advisory will suggest the appropriate size for PGA memory
and thus make PGA tuning an even simpler task.
How To Tune PGA_AGGREGATE_TARGET
----------------------------------
The first question we will have when we set this parameter is what is the best
value for it ?
To determine the appropriate setting for PGA_AGGREGATE_TARGET parameter I recommend
to follow the following steps
1- Make a first estimate for PGA_AGGREGATE_TARGET based on the following rule
- For OLTP systems
PGA_AGGREGATE_TARGET = (
- For DSS systems
PGA_AGGREGATE_TARGET = (
So for example if we have an Oracle instance configured on system with 16G of
Physical memory, then the suggested PGA_AGGREGATE_TARGET parameter value we should
start with incase we have OLTP system is (16 G * 80%)*20% ~= 2.5G and incase we
have DSS system is (16 G * 80%)* 50% ~= 6.5 G.
In the above equation we assume that 20 % of the memory will be used by the OS,
and in OLTP system 20 % of the remaining memory will be used for
PGA_AGGREGATE_TARGET and the remaining memory is going for Oracle SGA memory and
non-oracle processes memory. So make sure that you have enough memory for your
SGA and also for non-oracle processes
2- Second step in tuning the PGA_AGGREGATE_TARGET is to monitor performance using
available PGA statistics and see if PGA_AGGREGATE_TARGET is under sized or over
sized. Several dynamic performance views are available for this purpose:
- V$PGASTAT
This view provides instance-level statistics on the PGA memory usage and
the automatic PGA memory manager. For example:
SELECT * FROM V$PGASTAT;
NAME VALUE
--------------------------------------------------------
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 463435776 bytes
global memory bound 25600 bytes
total PGA inuse 9353216 bytes
total PGA allocated 73516032 bytes
maximum PGA allocated 698371072 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 560744448 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0 bytes
total bytes processed 4.0072E+10 bytes
total extra bytes read/written 3.1517E+10 bytes
cache hit percentage 55.97 percent
Main statistics to look at
(a) aggregate PGA auto target : This gives the amount of PGA memory Oracle can
use for work areas running in automatic mode. This part of memory represent the
tunable part of PGA memory,i.e. memory allocated for intensive memory SQL operators
like sorts, hash-join, group-by, bitmap merge and bitmap index create. This memory
part can be shrinked/expanded in function of the system load. Other parts of
PGA memory are known as untunable, i.e. they require a size that can't be negociated
(e.g. context information for each session, for each open/active cursor,
PL/SQL or Java memory).
So, the aggregate PGA auto target should not be small compared to the value of
PGA_AGGREGATE_TARGET. You must ensure that enough PGA memory is left for work areas
running in automatic mode.
(b) total PGA used for auto workarea: This gives the actual tunable PGA memory used by
the system. The 'maximum PGA used for auto workareas' gives the maximum reached
by previous statistic since instance startup.
(c) total PGA in used: This gives the total PGA memory in use. The detail of this
value can be found in the PGA_USED_MEM column of the v$process view.
Oracle92 only:
(d) over allocation count: Over-allocating PGA memory can happen if the value of
PGA_AGGREGATE_TARGET is too small to accommodate the untunable PGA memory part plus
the minimum memory required to execute the work area workload. When this happens,
Oracle cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra
PGA memory needs to be allocated. over allocation count is the number of time the
system was detected in this state since database startup. This count should ideally be
equal to zero.
Oracle92 only:
(e) cache hit percentage: This metric is computed by Oracle to reflect the
performance of the PGA memory component. It is cumulative from instance
start-up. A value of 100% means that all work areas executed by the system
since instance start-up have used an optimal amount of PGA memory. This is,
of course, ideal but rarely happens except maybe for pure OLTP systems. In
reality, some work areas run one-pass or even multi-pass, depending on the
overall size of the PGA memory. When a work area cannot run optimally, one or
more extra passes is performed over the input data. This reduces the cache
hit percentage in proportion to the size of the input data and the number of
extra passes performed. this value if computed from the "total bytes processed"
and "total extra bytes read/written" statistics available in the same view using
the following formula:
total bytes processed * 100
PGA Cache Hit Ratio = ------------------------------------------------------
(total bytes processed + total extra bytes read/written)
- V$SQL_WORKAREA_HISTOGRAM (Oracle92 only)
This view shows the number of work areas executed with optimal memory size, one-
pass memory size, and multi-pass memory size since instance start-up. Statistics
in this view are subdivided into buckets that are defined by the optimal memory
requirement of the work area. Each bucket is identified by a range of optimal
memory requirements specified by the values of the columns LOW_OPTIMAL_SIZE and
HIGH_OPTIMAL_SIZE.
Example :
The following query shows statistics for all nonempty buckets.
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
FROM v$sql_workarea_histogram
WHERE total_executions != 0;
The result of the query might look like the following:
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
8 16 156255 0 0
16 32 150 0 0
32 64 89 0 0
64 128 13 0 0
128 256 60 0 0
256 512 8 0 0
512 1024 657 0 0
1024 2048 551 16 0
2048 4096 538 26 0
4096 8192 243 28 0
8192 16384 137 35 0
16384 32768 45 107 0
32768 65536 0 153 0
65536 131072 0 73 0
131072 262144 0 44 0
262144 524288 0 22 0
The query result shows that, in the 1024 KB to 2048 KB bucket, 551 work areas used
an optimal amount of memory, while 16 ran in one-pass mode and none ran in
multi-pass mode. It also shows that all work areas under 1 MB were able to run in
optimal mode.
You can also use V$SQL_WORKAREA_HISTOGRAM to find the percentage of times work
areas were executed in optimal, one-pass, or multi-pass mode since start-up.
Example :
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024); ---- for 64 K optimal size
- V$SQL_WORKAREA_ACTIVE
This view can be used to display the work areas that are active (or executing)
in the instance. Small active sorts (under 64 KB) are excluded from the view.
Use this view to precisely monitor the size of all active work areas and to
determine if these active work areas spill to a temporary segment.
Example :
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
SID OPERATION ESIZE MEM MAX MEM PASS TSIZE
--- ----------------- --------- --------- --------- ----- -------
8 GROUP BY (SORT) 315 280 904 0
8 HASH-JOIN 2995 2377 2430 1 20000
9 GROUP BY (SORT) 34300 22688 22688 0
11 HASH-JOIN 18044 54482 54482 0
12 HASH-JOIN 18044 11406 21406 1 120000
This output shows that session 12 (column SID) is running a hash-join having its
work area running in one-pass mode (PASS column). This work area is currently
using 11406 KB of memory (MEM column) and has used, in the past, up to 21406 KB
of PGA memory (MAX MEM column). It has also spilled to a temporary segment of
size 120000 KB. Finally, the column ESIZE indicates the maximum amount of memory
that the PGA memory manager expects this hash-join to use. This maximum is dynamically
computed by the PGA memory manager according to workload.
When a work area is deallocated?that is, when the execution of its associated SQL
operator is complete?the work area is automatically removed from the
V$SQL_WORKAREA_ACTIVE view.
- [NOTE:148346.1] have some other queries we use to monitor SQL execution memory
3- The Third and last step is tuning the PGA_AGGREGATE_TARGET. In Oracle 9i
Release 2 we have 2 new views that help us in this task
- V$PGA_TARGET_ADVICE
- V$PGA_TARGET_ADVICE_HISTOGRAM
By examining these two views, you will be able to determine how key PGA statistics
will be impacted if you change the value of PGA_AGGREGATE_TARGET.
To enable automatic generation of PGA advice performance views, make sure the
following parameters are set:
- PGA_AGGREGATE_TARGET
- STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting this
parameter to BASIC turns off generation of PGA performance advice views.
The content of these PGA advice performance views is reset at instance start-up
or when PGA_AGGREGATE_TARGET is altered.
V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and
over allocation count in V$PGASTAT will be impacted if you change the value of
the initialization parameter PGA_AGGREGATE_TARGET.
The following select statement can be used to find this information
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
The output of this query might look like the following:
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
1000 61 0
1500 67 0
2000 76 0
3000 83 0
4000 85 0
From the above results we should set the PGA_AGGREGATE_TARGET parameter to a
value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value
we can set is 375 ( where ESTD_OVERALLOC_COUNT is 0)
After eliminating over-allocations, the goal is to maximize the PGA cache hit
percentage, based on your response-time requirement and memory constraints.
V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how the statistics displayed
by the performance view V$SQL_WORKAREA_HISTOGRAM will be impacted if you
change the value of the initialization parameter PGA_AGGREGATE_TARGET. You can
use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view detailed
information on the predicted number of optimal, one-pass and multi-pass work
area executions for the set of PGA_AGGREGATE_TARGET values you use for the
prediction.
Common issues
---------------
1- When we set the PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to auto
then the *_area_size parameter are automatically ignored and oracle will
automatically use the computed value for these parameters.
2- In Oracle 8i and earlier, the PGA memory was static, once the process started
and started to allocate memory for it's PGA area then it will not release it
back to the OS unless the process exits or dies. But the OS and under heavy
memory pressure will decide to page out unused memory pages belongs to a process
PGA to the swap space.
In Oracle 9i and under the automatic PGA memory management, Oracle will be able
to unallocate memory from a process PGA which is not using it any more so
another process can use it, also it can adjust the different work areas size
to accommodate the current workload and the amount of memory can be used.
3- Using automatic PGA memory management feature will help limiting resources
used by oracle process, and will use it more efficiently.
4- Using automatic PGA memory management will help also reducing the possibility
of getting ora-4030 errors unless we hit a OS limit, because work area sizes
will be controlled and adjusted automatically based on the PGA_AGGGREGATE_TARGET
parameter first and then the current work load.
5- If column ESTD_OVERALLOCATION_COUNT in the V$PGA_TARGET_ADVICE VIEW is nonzero,
It indicates that PGA_AGGREGATE_TARGET is too small to even meet the minimum
PGA memory needs. If PGA_AGGREGATE_TARGET is set within the over allocation
zone, the memory manager will over-allocate memory and actual PGA memory
consumed will be more than the limit you set. It is therefore meaningless to
set a value of PGA_AGGREGATE_TARGET in that zone.
6- Some customer reported that SQL LOADER in Oracle 9i is slower than SQL Loader
in Oracle 8i, and example of this is [BUG:2230115] which was closed as not a
bug. Using PGA_AGGREGATE_TARGET alleviated the problem.
7- PGA_AGGREGATE_TARGET is not supported on VMS, for more information please refer
to [NOTE:175216.1] "Oracle9i Release Notes Release 1 (9.0.1) for Alpha OpenVMS".
ORA-3113 is returned on instance startup when init.ora PGA_AGGREGATE_TARGET is set.
8- Setting PGA_AGGREGATE_TARGET in 9.0.1 on HP-UX 11.0 may panic the OS. for
more information please refer to [NOTE:43507.1] "ALERT HP-UX Patch Levels
Advised" and Bug:2122307.
伴随自动PGA调整新特性的引入,Oracle随之引入了一系列新的视图,V$PGASTAT就是其中的一个.
在V$PGASTAT中有这样一个条目: global memory bound ,该条目记录数据库允许的最高PGA内存使用量,我们可以从不同的PGA参数设置来观察一下Oracle运行的PGA上限.
SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 10m
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=10m
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:03:04.12
…….
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC
-------------------------------------------------- ------------------ -----
LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
-------------------------- -------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO
.548828125 206 PASSES 62914560
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 30M
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=30M
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:00:53.30
………..
SQL> SET autotrace off
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024,
2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024
-------------------------------------------------- ------------------ ----- --------------------------
LAST_EXECUTION LAST_TEMPSEG_SIZE
-------------------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO 1.48046875
6 PASSES 57671680
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 30
global memory bound 1.5
Elapsed: 00:00:00.00
我
们可以注意到,PGA的global memory bound会一直处在5%的PGA_AGGREGATE_TARGET参数设置,直到
5% PGA_AGGREGATE_TARGET超过100M,然后global memory bound被限制为100M,也就是满足我们提到的:
对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
注意,修改PGA_AGGREGATE_TARGET参数可以使用如下命令:
alter system set pga_aggregate_target=4096M ;
修改参数后,通常需要之行操作才能看到视图信息的变化:
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 10
global memory bound .5
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 20
global memory bound 1
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 40
global memory bound 2
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 1024
global memory bound 51.1992188
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 100
实际上这个100M的上限是受到了另外一个隐含参数的控制,该参数为_pga_max_size,该参数的缺省值为200M,单进程串行操作PGA的上限不能超过该参数的1/2.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
Enter value for par: pga_max
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%pga_max%'
NAME VALUE DESCRIB
--------------------------------------------- ---------------- ---
_pga_max_size 209715200 Maximum size of the PGA memory for one process
如果我们修改该参数, global memory bound将可以突破100M的上限:
SQL> alter system set "_pga_max_size"=400M;
System altered.
……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 200
对于PGA的控制,还有一系列的内部参数,列举如下,仅供参考:
SQL> l
1 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6* AND x.ksppinm LIKE '%&par%'
SQL> /
Enter value for par: smm
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%smm%'
NAME VALUE DESCRIB
------------------------ ----- ----------------------------------------------------------------
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 128 minimum work area size in auto mode
_smm_max_size 2560 maximum work area size in auto mode (serial)
_smm_px_max_size 15360 maximum work area size in auto mode (global)
_smm_bound 0 overwrites memory manager automatically computed bound
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
11 rows selected.
