oracle中pga内存分配原则

gblf

pga_aggregate_target 通常缩写为P_A_T，该参数同时限制全局pga分配和私有工作区内存分配

在oracle9i以及10gr1中，单个sql操作内存使用存在如下限制：
对于串行操作，单个sql操作能够使用的pga内存按照一下原则分配：
MIN(5%*PGA_AGGREGATE_TARGET,100MB)

此处的5%*PGA_AGGREGATE_TARGET实际上是由_smm_nax_size决定的，该参数限制自动工作区模式下最大的工作区使用

对于并行操作：
30%PGA_AGGREGATE_TARGET/DOP(DOP=DEGREE IF PARALLELISM,并行度)

在oracle10gr2以及oracle11g中，内存使用存在如下限制：
对于串行操作，能够使用的pga内存按照以下原则分配：
如果P_A_T<500MB,则_smm_max_size=20%P_A_T

如果P_A_T在500MB和1000MB之间，_smm_max_size=100MB

如果P_A_T介于1001MB和2560MB之间，_smm_max_size=10%*P_A_T

如果P_A_T>2560MB则_smm_max_size=262060MB

对于并行操作，能够使用的pga内存按照以下原则分配：
50%PGA_AGGREGATE_TARGET/DOP (DOP=Degree of parallelism,并行度)

但是注意，当dop<5时，_smm_max_size限制生效，并行度超过5时另外一个限制并行的参数_smm_px_max_size才会生效

从oralce10g开始 pga算法受一个新增的隐含参数_newsort_abled影响，如果将该参数设置为false，则数据库会使用之前oracle9i中的算法规则：

sys@ORCL>[url=]@gethidpar.sql[/url]

输入 name 的值:  newsort_enabled

原值    4: and x.ksppinm like'%&name%'

新值    4: and x.ksppinm like'%newsort_enabled%'

KSPPINM           KSPPSTVL

_newsort_enabled          TRUE      

上述@gethidpar.sql内容：
select x.ksppinm,y.ksppstvl,x.ksppdesc from x$ksppi x,x$ksppcv y wherex.indx=y.indx

and y.inst_id=userenv('Instance')

and x.inst_id=userenv('Instance')

and x.ksppinm like '%&name%';

要理解pga的自动调整，还需要区分可调整内存(TUNABLE MEMORY SIZE) 与不可调整内存（UNTUNABLE MEMORY SIZE），可调整内存是由sql工作区使用的，其余部分是不可调整内存        

Oracle在评估执行计划时会更具PGA_AGGREGATE_TARGET参数评估在sort，HASH_JOIN或Bitmap操作时能够使用的最大或最小内存，从而选择最优的执行计划

对于一个单纯的数据库服务器，通常我们需要保存20%的物理内存给操作系统使用，剩余80%可以分配给oracle使用，而oracle的内存由pga和sga构成，pga可以占用oracle消耗内存的20%（OLTP系统）至50%（DSS系统）

通过v$process 可以查询pga的相关使用情况：
V$PROCESS displays information about the currently active processes.

Column

Datatype

Description

ADDR

RAW(4 | 8)

Address of the process state object

PID

NUMBER

Oracle process identifier

SPID

VARCHAR2(24)

Operating system process identifier

PNAME

VARCHAR2(5)

Name of this process

USERNAME

VARCHAR2(15)

Operating system process username

Note: Any two-task user coming across the network has "-T" appended to the username.

SERIAL#

NUMBER

Process serial number

TERMINAL

VARCHAR2(30)

Operating system terminal identifier

PROGRAM

VARCHAR2(48)

Program in progress

TRACEID

VARCHAR2(255)

Trace file identifier

TRACEFILE

VARCHAR2(513)

Trace file name of the process

BACKGROUND

VARCHAR2(1)

1 for a background process; NULL for a normal process

LATCHWAIT

VARCHAR2(8)

Address of the latch the process is waiting for; NULL if none

LATCHSPIN

VARCHAR2(8)

This column is obsolete

PGA_USED_MEM

NUMBER

PGA memory currently used by the process

PGA_ALLOC_MEM

NUMBER

PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)

PGA_FREEABLE_MEM

NUMBER

Allocated PGA memory which can be freed

PGA_MAX_MEM

NUMBER

Maximum PGA memory ever allocated by the process

sys@ORCL>select pid,spid,pname,usernamefrom v$process;

      PID SPID                     PNAMEUSERNAME

---------- ------------------------ --------------------

        1

        2 7780                    PMON  SYSTEM

        3 6156                    VKTM  SYSTEM

        4 7864                    GEN0  SYSTEM

        5 3604                    DIAG  SYSTEM

        6 7916                    DBRM  SYSTEM

        7 7940                     PSP0 SYSTEM

        8 7216                    DIA0  SYSTEM

        9 7264                    MMAN  SYSTEM

       10 7324                    DBW0  SYSTEM

       11 8020                    LGWR  SYSTEM

       12 7972                    CKPT  SYSTEM

       13 7908                    SMON  SYSTEM

       14 6864                    RECO  SYSTEM

       15 1604                    MMON  SYSTEM

       16 1440                    MMNL  SYSTEM

       17 8176                    D000  SYSTEM

       18 6324                    S000  SYSTEM

       19 3976                    SMCO  SYSTEM

       20 4560                    W000  SYSTEM

       23 6332                    ARC0  SYSTEM

       24 4732                     ARC1  SYSTEM

       25 1436                    ARC2  SYSTEM

       26 6732                    ARC3  SYSTEM

       27 7796                    QMNC  SYSTEM

       28 8660                          SYSTEM

       31 6424                     Q000 SYSTEM

       32 892                     Q001  SYSTEM

       35 7976                    CJQ0  SYSTEM

选择spid为8660为例：
sys@ORCL>select pid,spid,username,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem fromv$process where spid=8660;

      PID SPID                    USERNAME        PGA_USED_MEMPGA_ALLOC_MEM  PGA_FREEABLE_MEM  PGA_MAX_MEM

---------- --------------------------------------- ------------ ------------- ---------------- -----------

       28 8660                    SYSTEM               1161714       1786898           524288     1786898

通过v$process_memory视图可以进一步知道pga内存消耗在什么地方：

V$PROCESS_MEMORY displaysdynamic PGA memory usage by named component categories for each process.

Column

Datatype

Description

PID

NUMBER

Oracle process identifier

SERIAL#

NUMBER

Oracle process serial number

CATEGORY

VARCHAR2(15)

Category name. Categories include "SQL", "PL/SQL", "OLAP" and "JAVA". Special categories are "Freeable" and "Other". Freeable memory has been allocated to the process by the operating system, but has not been allocated to a category. "Other" memory has been allocated to a category, but not to one of the named categories.

ALLOCATED

NUMBER

Bytes of PGA memory allocated by the process for the category. For the "Freeable" category, it is the amount of free PGA memory eligible to be released to the operating system.

USED

NUMBER

Bytes of PGA memory used by the process for the category. For "Freeable", the value is zero. For "Other", the value is NULL for performance reasons.

MAX_ALLOCATED

NUMBER

Maximum bytes of PGA memory ever allocated by the process for the category.

sys@ORCL>selectp.program,p.spid,pm.category,pm.allocated,pm.allocated,pm.used,pm.max_allocatedfrom v$process p,v$process_memory pm where p.pid=pm.pid and p.spid=8660;

PROGRAM                                                         SPID                     CATEGORY      ALLOCATED ALLOCATED       USED MAX_ALLOCATED

---------------------------------------------------------------------------------------- --------------- ---------- ---------- -----------------------

ORACLE.EXE (SHAD)                                                8660                     SQL               15416         15416      11888        418096

ORACLE.EXE (SHAD)                                               8660                    PL/SQL            28296         28296      20136        29440

ORACLE.EXE (SHAD)                                               8660                    Freeable         327680        327680          0

ORACLE.EXE (SHAD)                                               8660                    Other           1415506       1415506                  1415506

还可以通过pid得到当前执行的sql语句：
SELECT   /*+ ORDERED */

        sql_text

   FROM v$sqltext a

  WHERE (a.hash_value, a.address) IN (

           SELECT DECODE (sql_hash_value,

                           0, prev_hash_value,

                           sql_hash_value

                          ),

                   DECODE (sql_hash_value, 0,prev_sql_addr, sql_address)

              FROM v$session b

            WHERE b.paddr = (SELECT addr

                                FROM v$processc

                               WHERE c.spid =TO_NUMBER ('&pid', 'xxxx')))

ORDER BY piece ASC

yuanhaoan6

- 当我的眼泪不在对你有意义时, 说明㈠切都结束了,