oracle 之 内存—鞭辟近里（三）

oracle 之 内存—鞭辟近里（三）

今天是2013-07-08，今天晚上突然接到一个电话，我的外甥问我的qq是多少，我感觉很吃惊，他长大了。在他现在这个年龄就开始接触网络，我难免有少许担心，希望他合理利用网络。
  一）pga introduction：
pga是为服务器进程分配的一块私有的内存，其中包含了相关数据信息和控制信息。每个进程只能对自己的pga区域进行读和写相应的执行代码信息。在运行区域中一个简单的内容就是游标，每个时间游标都会被执行，每个新的run-time area也是为游标相应的分配pga中的内存，以此来完成服务进程所要完成的执行操作。也就是说，run-time area 的创建和释放相应的内存是根据cursor的开启和关闭的。当在共享服务器模式下，一部分运行区域在sga中进行分配，如果large pool设置了那么就在large pool中分配，如果没有那么就存在于share pool中。
对于复杂的查询（如决策执行查询），那么大部分的运行区是从事于工作区进行密集型操作的，像如下：
》基于排序分类，像order by ,group by ,rollup以及窗口函数等
》hash-join
》bitmap merge
》bitmap create
》使用批量的写入buffer
sort area使用工作区的部分内存进行数据行的排序操作，同样的，这个hash-join操作使用hash area工作区进行处理一个散列表。
这个工作区域的大小能够被控制和进行相应的改变，通常，对一个消耗很多内存的特别的操作，一个大的工作区域能够显著提高这个处理性能。理想上，这个工作区域有一个合理的值，可以很好的处理sql语句所执行的数据操作。当工作区的值小于这个合理的值的时候，哪进行sql操作的时候势必会增减相应的执行时间，因为需要额外的把数据暂时缓存到磁盘上，然后在进行缓慢的处理。例如我要进行一个1g的数据排序操作，那么就需要有1g的内存需求，才能一次处理迅速处理完成，但是我的sort area为10M，那么它就会经过多次的数据input进行处理。
自动pga内存管理能够简化和改善pga内存的分配难题，默认情况下自动pga内存管理启动了，在这种情况下，oracle会自己动态的根据sga的20%调整一部分pga内存给work area，最小值为10M。
二）配置自动pga内存管理：
workarea_size_policy有两个值一个是auto代表开启pga自动管理，一个是manual代表进行手动管理pga。但是我在联机手册上看到这么一句话：

Note:

For backward compatibility, automatic PGA memory management can be disabled by setting the value of the PGA_AGGREGATE_TARGET initialization parameter to 0. When automatic PGA memory management is disabled, the maximum size of a work area can be sized with the associated _AREA_SIZE parameter, such as the SORT_AREA_SIZE initialization parameter.

当在自动pga管理方式下，设置一个pga_aggregate_target不为0的参数，然后oracle会根据这个参数的限制进行pga 内部的component的size turn，在这种情况下*_area_size参数就会被忽略失效（包括：
bitmap_merge_area_size       
create_bitmap_area_size      
hash_area_size               
sort_area_size               
workarea_size_policy
）。
当配置一个刚刚创建的实例的时候，很难确定pga_aggregate_target该参数在参数文件中应该设置多么大的一个值，可以采用如下方法：

对于pga_aggregate_target参数值大小确认oracle给出了以下步骤：

1）在初始状态下并不知道oracle该值应该设置为多大合理，那么可以首先设置一个sga的20%，对于该值可能太小也可能大。后续在处理。

2）运行一些具有工作负荷的语句然后观察性能变化，然后使用pga统计这些数据，查看最大的使用pga是否在设置的值之下或是之上，然后进行相应的调整。

3）        参考使用oracle pga 诊断统计器的值，进行修改pga_aggregate_target参数大小。

必须根据具体的实际oracle说占有的内存，进行调整pga和sga的大小：
对于OLTP系统的话，划分一个很小的总占用内存的值，如20%，然后剩余80%给sga
对于dss系统，需要划分一个大点的值，以满足复杂请求需要，可以划分0%（仅此参考，具体情况还需诊断在确认）。
oracle这么也给出了一个很好的参考计算方法：
For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

三）监控自动pga管理的相关视图：
这些视图能够动态的查看pga内存管理的一些详细信息，便于研究性能瓶颈和调整pga_aggregate_target参数：
1、v$pgastat：该视图是在数据库的实例层，统计的自动内存分配情况：
eg:

SYS@orcl#select * from v$pgastat
  2  ;

NAME                                                              VALUE UNIT
------------------------------------------------------------ ---------- ------------------------
aggregate PGA target parameter                                268435456 bytes
aggregate PGA auto target                                     167362560 bytes
global memory bound                                            53686272 bytes
total PGA inuse                                                82583552 bytes
total PGA allocated                                           161752064 bytes
maximum PGA allocated                                         238797824 bytes
total freeable PGA memory                                       7536640 bytes
process count                                                        36
max processes count                                                  44
PGA memory freed back to OS                                  2492006400 bytes
total PGA used for auto workareas                                     0 bytes

NAME                                                              VALUE UNIT
------------------------------------------------------------ ---------- ------------------------
maximum PGA used for auto workareas                             5968896 bytes
total PGA used for manual workareas                                   0 bytes
maximum PGA used for manual workareas                            268288 bytes
over allocation count                                                 0
bytes processed                                              2484548608 bytes
extra bytes read/written                                              0 bytes
cache hit percentage                                                100 percent
recompute count (total)                                           11557

已选择19行。

SYS@orcl#

，那么自动管理pga就被关闭了。

SYS@orcl#r

1* select name,value from v$spparameter wherename like 'pga%'

NAMEVALUE

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

pga_aggregate_target268435456

可以看到该值就是spfile中设置的值，为256M

Aggregate pga auto target:在自动模块下能够给工作去分配的总的pga大小，这个值动态的来源于pga_aggregate_target参数的值和工作区域的工作量占用的大小。然而，这个值被oracle不断的进行调整，如果这个值比pga_aggregate_target 值小，那么就有可能存在很多的pga内存被其他组件占用如pl/sql 或是java内存，给工作区域留下了很小的一部分内存空间，在自动模块一应该保证有足够的work area进行运行相应内容。

Global memory  bound：

在自动模块下可用的工作区的最大大小。这个值经常被oracle进行调整，以此来反应当前工作区域执行任务的情况。当活动的工作区在系统中增加的时候，这个全局内存区域通常就会减小。一般来说这个全局值不应该小于1M，如果小于1M，那么就应该增加pga_aggregate_target参数的值了。
Total pga  iuse：
该参数表示当前分配的pga中有多少内存给了work area ，一般可用参考该值进行反映出其他消耗内存的组件消耗了多少内存。

Total pga allocated：

这个值反应了当前被实例所分配的所有pga的大小，oracle会试图保持该值在pga_aggregate_target参数值之下，当工作区域任务增加的非常快的时候或是这个初始化参数pga_aggregate_target设置的比较小，那么该值有可能在短暂超过pga——aggregate_target参数指定的值的大小。
maximum PGA allocated：
从实例开始在一个时间分配的pga的最大大小是多少。

Total freeablepga memory：表示所有process占用的多少pga内存可以释放给os。
process count：
表示在最后三秒内所有激活的进程数目是多少。
max process count：
从实例启动开始算起，最大的进程数目是多少
PGA memory freed back to OS：
表示从实例启动开始一共返回给系统多少的内大小。

Total pga usedfor auto workareas：

表示在自动内存管理模式下，当前有多少pga内存被工作区域所占用，这个值可以决定有多少pga内存被其他的组件进行消耗。

Pga other=total pga allocated-total pgaused for auto workareas

Over allocationcount:

该值就是一个计数，从实例启动开始算起，当PGA_AGGREGATE_TARGET设置非常小或工作区负载增长很快时，会超额分配PGA内存（分配的值大于PGA_AGGREGATE_TARGET）。这种情况发生时，Oracle不能限制PGA内存小于PGA_AGGREGATE_TARGET，只能分配实际需要的PGA内存。此时，建议通过建议器视图V$PGA_TARGET_ADVICE来增加PGA_AGGREGATE_TARGET的大小。

Total bytes processed：从实例启动开始被进程处理的字节数。

Extra bytes read/written:当一个工作区域达不到一个最优值的时候，自从实例启动后，需要额外输入数据所处理的字节数。当工作区无法在最佳状态下运行时，就需要进行这个额外处理。

cache hit percentage：

Oracle计算出来的一个与PGA内存组件性能相关的数据，是自从实例启动后累加的。如果这个值是100%，则表示实例启动后，所有系统使用到的工作区都分配了最佳的PGA内存。

当工作区无法在最佳状态下运行，就需要进行额外的数据输入处理，这将会降低cache hit percentage。
2、v$process:
该视图中可以反映出进程使用的pga的大小信息：
PGA_USED_MEM：该进程所使用的pga内存大小
PGA_ALLOC_MEM：分配给该进程的pga大小：
PGA_FREEABLE_MEM：该进程能够释放的内存大小
PGA_MAX_MEM：该进程所能够分配的最大内存大小：
eg：
SQL> select program,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process
  2  ;

PROGRAM                                          PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
PSEUDO                                                      0             0                0           0
oracle@oracle-R2 (PMON)                                580052        669072                0      669072
oracle@oracle-R2 (PSP0)                                562660        652672                0      652672
oracle@oracle-R2 (VKTM)                                559584        652672                0      652672
oracle@oracle-R2 (GEN0)                                566528        652672                0      652672
oracle@oracle-R2 (DIAG)                                559612        652672                0      652672
oracle@oracle-R2 (DBRM)                                638676       1045888           131072     1045888
oracle@oracle-R2 (DIA0)                               1542316       1749756                0     1749756
oracle@oracle-R2 (MMAN)                                562660        652672                0      652672
oracle@oracle-R2 (DBW0)                               5416072       5804960                0    10916768
oracle@oracle-R2 (LGWR)                              11167108      11990400           131072    11990400

PROGRAM                                          PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
oracle@oracle-R2 (CKPT)                                609912        875188                0      875188
oracle@oracle-R2 (SMON)                                823148       4715904          3801088     4715904
oracle@oracle-R2 (RECO)                                703308       1308032           393216     1308032
oracle@oracle-R2 (MMON)                               1613944       3060476          1179648     3977980
oracle@oracle-R2 (MMNL)                                648448        849280                0      849280
oracle@oracle-R2 (D000)                                807768       1111424                0     1111424
oracle@oracle-R2 (S000)                                357392        587136                0      587136
oracle@oracle-R2 (SMCO)                                559588        652672                0      652672
oracle@oracle-R2 (ARC0)                              24261816      26801536          1114112    26801536
oracle@oracle-R2 (ARC1)                              24261816      26801536          1114112    26801536
oracle@oracle-R2 (ARC2)                              17452884      18675072                0    18675072

PROGRAM                                          PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------------------------------------------ ------------ ------------- ---------------- -----------
oracle@oracle-R2 (ARC3)                              24261816      26801536          1114112    26801536
oracle@oracle-R2 (QMNC)                                563796        718208                0      718208
oracle@oracle-R2 (CJQ0)                               1051512       9237888          7864320     9827712
oracle@oracle-R2 (Q000)                               3678780       4895484           589824     4895484
oracle@oracle-R2 (Q001)                                849964        914816                0     1635712
oracle@oracle-R2 (J000)                                585720       1504640                0     1504640
oracle@oracle-R2 (J001)                                559652        652672                0      652672
oracle@oracle-R2 (W000)                                662472        783744                0      783744
oracle@oracle-R2 (TNS V1-V3)                           852756       2168292           983040     2758116

31 rows selected.

SQL>
3、v$process_memory:
该视图显示了，对于oracle进程中哪些类型的组件占用了多少的pga内存情况信息。
包含六个字段：
pid：oracle内部进程号：
serial#：进行序列号
category：在使用进程占用内存的组件是什么，如sql，pl/sql,other，freeable（代表能够释放给系统多少size）：
allocated：分配的pga内存大小
userd：使用了多大
max_allocated:最大能够分配给该components多么大的pga内存。
eg：

SQL> select * from v$process_memory where rownum<10;

PID    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
         2          1 PL/SQL                2024        136          2024
         2          1 Other               667048                   667048
         3          1 PL/SQL                2024        136          2024
         3          1 Other               650648                   650648
         4          1 PL/SQL                2024        136          2024
         4          1 Other               650648                   650648
         5          1 PL/SQL                2024        136          2024
         5          1 Other               650648                   650648
         6          1 PL/SQL                2024        136          2024

9 rows selected.

SQL>
另外可以使用该视图与v$process查看那个进程中存在哪些category占用内存情况：
eg：
SQL> select m.pid,p.pname,m.serial#,m.category,m.allocated,m.used,m.max_allocated
  2  from v$process p,v$process_memory m where p.pid=m.pid and rownum<16;

PID PNAME    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ----- ---------- --------------- ---------- ---------- -------------
         2 PMON           1 PL/SQL                2024        136          2024
         2 PMON           1 Other               667048                   667048
         3 PSP0           1 PL/SQL                2024        136          2024
         3 PSP0           1 Other               650648                   650648
         4 VKTM           1 PL/SQL                2024        136          2024
         4 VKTM           1 Other               650648                   650648
         5 GEN0           1 PL/SQL                2024        136          2024
         5 GEN0           1 Other               650648                   650648
         6 DIAG           1 PL/SQL                2024        136          2024
         6 DIAG           1 Other               650648                   650648
         7 DBRM           1 SQL                      0          0        250144

PID PNAME    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ----- ---------- --------------- ---------- ---------- -------------
         7 DBRM           1 PL/SQL                2024        136          2024
         7 DBRM           1 Freeable            131072          0
         7 DBRM           1 Other               912792                   912792
         8 DIA0           1 PL/SQL                2024        136          2024

15 rows selected.

SQL>
4、4、v$sql_workarea_histogram;
该视图展示了对不同的工作区域大小然后进行工作区域执行累计的统计信息。这个工作区域更具最优内存需求和增长的需要被换分了33个组，
对于每个工作区域，该视图展示了有多少工作区域在哪个范围能够运行在最优化模式，有多少运行在一个one-pass模式，和有多少能够运行在
multi-pass模式。
eg：
SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
  2         (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
  3         OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
  4    FROM V$SQL_WORKAREA_HISTOGRAM
  5   WHERE TOTAL_EXECUTIONS != 0;
\
    LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
         2          4           30492758                  0
                     0

64        128             128341                  0
                     0

128        256               7253                  0
                     0

LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
       256        512             165998                  0
                     0

512       1024             115171                  0
                     0

1024       2048               4962                  0
                     0

LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
      2048       4096              15169                  0
                     0

4096       8192               3189                  6
                     0

8192      16384                261                  6
                     0

LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
     16384      32768                247                  0
                     0

32768      65536                  6                  0
                     0

65536     131072                  3                  2
                     0

LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS
---------- ---------- ------------------ ------------------
MULTIPASSES_EXECUTIONS
----------------------
    131072     262144                  4                  8
                     0
13 rows selected.
可以从这次查询的内容中看出如下信息：
当在4M的时候，有3189个area size可以运行在最优模式，有6个需要进行一次 one-pass，没有mutil-pass。同样反映出了，如果设置在4M之下，那么就能保证都运行在
最优模式下。（因此可以判断area size的大小可以设置为3M，还算可以的）。

5、v$sql_workarea_active:
v$sql_workarea_active试图包含了被系统分配的当前数案件的工作区域的相关信息，可以通过workarea_address与v$sql_workarea视图进行连接，来查看哪个工作区域的信息。如果在一个work area被分到了磁盘上，然后这个视图包含一些临时段为协助完成work area解决空间不足处理sql的需求。通过这个视图我们可以获得如下问题的详细信息：
1）当前被系统分配的前10最大的工作区域是哪些
2）What percentage of memory is over-allocated (EXPECTED_SIZE < ACTUAL_MEM_USED) and under-allocated (EXPECTED_SIZE > ACTUAL_MEM_USED)?
3）哪些活动的区域使用更多的内存。
4）哪些工作区域已经被分裂到磁盘上了。
eg：
session1：
SYS@orcl#set autotrace trace statistics
SYS@orcl#select * from dba_objects;

已选择68722行。

统计信息
----------------------------------------------------------
0  recursive calls
          0  db block gets
       7862  consistent gets
        884  physical reads
          0  redo size
    3803931  bytes sent via SQL*Net to client
      50915  bytes received via SQL*Net from client
       4583  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68722  rows processed

SYS@orcl#

session2:
select * from v$sql_workarea_active;
SQL> r

SQL_HASH_VALUE SQL_ID        SQL_EXEC_START SQL_EXEC_ID WORKAREA_ADDRESS OPERATION_TYPE                                                                   OPERATION_ID POLICY                          SID  QCINST_ID      QCSID ACTIVE_TIME WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE TABLESPACE                       SEGRFNO#    SEGBLK#
-------------- ------------- -------------- ----------- ---------------- -------------------------------------------------------------------------------- ------------ ------------------------ ---------- ---------- ---------- ----------- -------------- ------------- --------------- ------------ ------------- ------------ ------------------------------ ---------- ----------
1578617346 6avfua5g1gkh2 2013/7/11 22:3    16777219 0000000071DD1F48 HASH-JOIN                                                                                  10 AUTO                            155                           2952316        3085312       3084288         1249280      1249280             0                                                       
    1578617346 6avfua5g1gkh2 2013/7/11 22:3    16777219 0000000071DD1FB0 HASH-JOIN                                                                                   8 AUTO                            155                           2954647        2848768       2847744         1271808      1271808             0

SQL>
哈哈，分析一下：
1）sql_hash_value:当前执行的sql语句的hash值：该值为：1578617346

2）sql_id:当前执行的sql命令的id号：6avfua5g1gkh2

3)SQL_EXEC_START:从会话开始执行该条语句的时间：2013/7/11 22:3

4）sql_exec_id:该语句执行的标示符：16777219

5）该语句所在的workarea的地址，可以通过该地址与v$sql_workarea进行连接查看该区域的更加详细信息，在学习v$sql_workarea会对此进行详细说明，改地址是唯一的且是该视图的主键：0000000071DD1F48

6）operation_type:使用该区域进行的操作类型（包括，sort，hash join，group by，buffering，bitmap merge，bitmap create）：HASH-JOIN

可以看一下该语句的执行计划如下：

Plan hash value: 456374238

----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 65381 |    12M|   247   (3)| 00:00:03 |
|   1 |  VIEW                          | DBA_OBJECTS | 65381 |    12M|   247   (3)| 00:00:03 |
|   2 |   UNION-ALL                    |             |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID | OBJ$        |     1 |    30 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | I_OBJ1      |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    FILTER                      |             |       |       |            |          |
|*  8 |     HASH JOIN                  |             | 69509 |  8281K|   244   (3)| 00:00:03 |
|   9 |      TABLE ACCESS FULL         | USER$       |    87 |  1479 |     3   (0)| 00:00:01 |
|* 10 |      HASH JOIN                 |             | 69509 |  7127K|   240   (2)| 00:00:03 |
|  11 |       INDEX FULL SCAN          | I_USER2     |    87 |  2001 |     1   (0)| 00:00:01 |
|* 12 |       TABLE ACCESS FULL        | OBJ$        | 69509 |  5566K|   239   (2)| 00:00:03 |
|* 13 |     TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  15 |     NESTED LOOPS               |             |     1 |    28 |     2   (0)| 00:00:01 |
|* 16 |      INDEX FULL SCAN           | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN          | I_OBJ4      |     1 |     8 |     1   (0)| 00:00:01 |
|  18 |    NESTED LOOPS                |             |     1 |   105 |     3   (0)| 00:00:01 |
|  19 |     TABLE ACCESS FULL          | LINK$       |     1 |    88 |     2   (0)| 00:00:01 |
|  20 |     TABLE ACCESS CLUSTER       | USER$       |     1 |    17 |     1   (0)| 00:00:01 |
|* 21 |      INDEX UNIQUE SCAN         | I_USER#     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

可以看到其实数据库内部进行了多个表的join，我们在深入研究一下看一下dba_objects这个表的内部创建语句。

SQL> R

DBMS_METADATA.GET_DDL('VIEW','
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_OBJECTS" ("OWNER", "OBJECT_NAME", "SUB
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
  WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       o.namespace,
       o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
7）
OPERATION_ID：该参数是在执行计划中定义操作的一个唯一的数字号，可以通过与v$sql_plan视图进行连接查看使用工作区内该语句的plan的信息;
8）POLICY： 该工作区的是auto还是manual
9）sid：执行该会话的id：该会话为155
10）QCINST_ID 查询协调员实例标识符。随着QCSID,使您能够唯一地标识查询协调员。
11) QCSID:查询协调员的会话标示，如果工作区通过连续的游标进行分配的，那么这个号是一个。
12)ACTIVE_TIME 这个工作区被激活的平均时间
13）WORK_AREA_SIZE：该操作占用的最大工作区的大小（kb)
14)EXPECTED_SIZE；一个估计的工作区大小
15）ACTUAL_MEM_USED:当前pga给area 分配的大小。
16) tempseg_size:写道磁盘上的段的数据
17）tablespace：为work area分配段在哪个表空间
18）segrfno#，文件号
19）segblk#，块号
eg：


SQL>

7）OPERATION_ID：

eg：
SQL> SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
  2         operation_type OPERATION,
  3         trunc(EXPECTED_SIZE/1024) ESIZE,
  4         trunc(ACTUAL_MEM_USED/1024) MEM,
  5         trunc(MAX_MEM_USED/1024) "MAX MEM",
  6         NUMBER_PASSES PASS,
  7         trunc(TEMPSEG_SIZE/1024) TSIZE
  8    FROM V$SQL_WORKAREA_ACTIVE
  9   ORDER BY 1,2;

       SID OPERATION                                     ESIZE        MEM
---------- ---------------------------------------- ---------- ----------
MAX MEM       PASS      TSIZE
---------- ---------- ----------
2125 HASH-JOIN                                      1733       1040
      1040          0
当前执行的sql语句使用过的会话id为2125，操作类型为hash-join，这个工作区域估计占用大小为1733bytes，实际为1040，过去占用的pga内存为1040，无one-pass记录，没有产生额外的磁盘分配。
6、v$sql_workarea
另外我们可以通过workarea_address与v$sql_workarea查看哪些分配了最大内存消耗的操作：
eg：
SQL> SELECT *
  2  FROM   (SELECT workarea_address, operation_type, policy, estimated_optimal_size
  3          FROM V$SQL_WORKAREA
  4          ORDER BY estimated_optimal_size DESC)
  5   WHERE ROWNUM <= 10;

WORKAREA_ADDRESS OPERATION_TYPE                           POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA36F8 GROUP BY (HASH)                          AUTO
              37981184

C0000004C64B5148 HASH-JOIN                                AUTO
              31110144

C0000004C7FCDF20 HASH-JOIN                                AUTO
              31110144

WORKAREA_ADDRESS OPERATION_TYPE                           POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA3690 HASH-JOIN                                AUTO
              28812288

C0000004C74E2C90 HASH-JOIN                                AUTO
              20407296

C0000004E25AE498 HASH-JOIN                                AUTO
              20373504

WORKAREA_ADDRESS OPERATION_TYPE                           POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004DE610500 HASH-JOIN                                AUTO
              20373504

C0000004D13C43F8 HASH-JOIN                                AUTO
              12705792

C0000004C7FCDF88 SORT (v2)                                AUTO
               9923584

WORKAREA_ADDRESS OPERATION_TYPE                           POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2CE30D8 HASH-JOIN                                AUTO
               9205760

10 rows selected.
可以看到，第一个是work area的地址为C0000004E2AA36F8，进行了group by的操作，使用的是自动work area 分配方式，该操作，估计需要消耗工作区域大小为37981184byte。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++↖(^ω^)↗+++++++