Oracle内存结构（之三） - 数据库编程

5 .25 85 4 70 .5 85 4 105 .75 85 4 140 1 100 0 168 1.2 100 0 196 1.4 100 0 224 1.6 100 0 252 1.8 100 0 280 2 100 0 420 3 100 0 560 4 100 0 840 6 100 0 1120 8 100 0

v$pga_target_advice_histogram 视图可以通过对不同工作区大小的采样评估供统计信息

供分析使用。其中几个重要字段有：

（1）LOW_OPTIMAL_SIZE-Histogram评估区间内Optimal下限 (bytes)

（2）HIGH_OPTIMAL_SIZE-Histogram评估区间内Optimal上限 (bytes)

（3）ESTD_OPTIMAL_EXECUTIONS-Histogram评估区间内估计optimal执行次数

（4）ESTD_ONEPASS_EXECUTIONS-Histogram评估区间内估计onepass执行次数

（5）ESTD_MULTIPASSES_EXECUTIONS-Histogram评估区间内估计multipass执行次数

（6）ESTD_TOTAL_EXECUTIONS-Histogram评估区间内估计执行总次数

SELECT pga_target_factor factor,
       low_optimal_size / 1024 low,
       ROUND(high_optimal_size / 1024) high,
       estd_optimal_executions estd_opt,
       estd_onepass_executions estd_op,
       estd_multipasses_executions estd_mp,
       estd_total_executions estd_exec
  FROM v$pga_target_advice_histogram
 WHERE pga_target_factor = 0.25
   AND estd_total_executions > 0;

 heap name="pga heap"  desc=0xbaf3ca0   --注意这里

 extentsz=0x20c0 alt=216 het=32767 rec=0 flg=3 opc=2
 parent=(nil)owner=(nil) nex=(nil) xsz=0xfff0 heap=(nil)
 fl2=0x60,nex=(nil), dsxvers=1, dsxflg=0x0
 dsx firstext=0xf7fa7720
EXTENT 0 addr=0x7fadf7c72010
  Chunk     7fadf7c72020 sz=    65504   free      "               "
EXTENT 1 addr=0x7fadf7d00010
  Chunk     7fadf7d00020 sz=    28920   perm      "perm           "  alo=9384
  Chunk     7fadf7d07118 sz=     7656   free      "               "
  Chunk     7fadf7d08f00 sz=     4224   freeable  "diag pga       " ds=0x7fadf82157e0
  Chunk     7fadf7d09f80 sz=     4224   freeable  "diag pga       " ds=0x7fadf82157e0
/heap                                                                                                                            
  Chunk     7fadf7fd28d0 sz=     7608   perm      "perm           "  alo=7608
  Chunk     7fadf7fd4688 sz=       40   free      "               "
  Chunk     7fadf7fd46b0 sz=       80   freeable  "dbgdInitEventGr"
  Chunk     7fadf7fd4700 sz=      184   freeable  "sdbgrf: iosb   "
  Chunk     7fadf7fd47b8 sz=       80   freeable  "skgfzctx       "
  Chunk     7fadf7fd4808 sz=      376   freeable  "PLS cca hpdesc"
EXTENT 18 addr=0x7fadf7fcd860
  Chunk     7fadf7fcd870 sz=    20512   perm      "perm           "  alo=20512
EXTENT 19 addr=0x7fadf7fc9958
  Chunk     7fadf7fc9968 sz=    16072   perm      "perm           "  alo=16072
EXTENT 20 addr=0x7fadf7fc7868
  Chunk     7fadf7fc7878 sz=     3240   perm      "perm           "  alo=3240
  Chunk     7fadf7fc8520 sz=     3184   perm      "perm           "  alo=3184
  Chunk     7fadf7fc9190 sz=     1088   perm      "perm           "  alo=1088
  Chunk     7fadf7fc95d0 sz=      168   perm      "perm           "  alo=168
  Chunk     7fadf7fc9678 sz=       48   free      "               "
  Chunk     7fadf7fc96a8 sz=       72   freeable  "koh-kghu callh"
  Chunk     7fadf7fc96f0 sz=      568   freeable  "joxp heap      "
EXTENT 21 addr=0x7fadf7fc5778
  Chunk     7fadf7fc5788 sz=     4848   perm      "perm           "  alo=4848
  Chunk     7fadf7fc6a78 sz=      440   freeable  "krbabrPgaRespMs"
  Chunk     7fadf7fc6c30 sz=       56   freeable  "krbabrPgaReqCtx"
  Chunk     7fadf7fc6c68 sz=      424   freeable  "krbabrPgaReqMsg"
  Chunk     7fadf7fc6e10 sz=     2600   freeable  "kjztprq struct"
EXTENT 22 addr=0x7fadf7fc3688
  Chunk     7fadf7fc3698 sz=     7720   perm      "perm           "  alo=7720
  Chunk     7fadf7fc54c0 sz=      160   freeable  "regheapd_kdlwpg"
  Chunk     7fadf7fc5560 sz=       88   freeable  "KCFIS GCTX     "
  Chunk     7fadf7fc55b8 sz=      400   freeable  "krbabrPgaStbyRe"
EXTENT 23 addr=0x7fadf7fb5e28
  Chunk     7fadf7fb5e38 sz=    55328   perm      "perm           "  alo=55328
EXTENT 24 addr=0x7fadf7fb3d38
  Chunk     7fadf7fb3d48 sz=     4888   perm      "perm           "  alo=4888
  Chunk     7fadf7fb5060 sz=     2992   recreate  "KSFQ heap      " latch=(nil)

进一步的，可以将某个具体的数据存储结构转储出来（DS），如以上的ds dcd00c0其空间使用的大小为 sz= 14036，首先对空间地址进行一下转换：

select to_number('baf3ca0','xxxxxxxxxx') from dual
15:50:33 sys@felix SQL>/

TO_NUMBER('BAF3CA0','XXXXXXXXXX')
-----------------------

Oracle内存结构（之三）(二)