Column PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR Datatype NUMBER NUMBER Description 估计的pga_aggregate_target大小(bytes) PGA_TARGET_FOR_ESTIMATE 除以当前pga_aggregate_target得出的值 ADVICE_STATUS LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE ESTD_OPTIMAL_EXECUTIONS ESTD_ONEPASS_EXECUTIONS VARCHAR2(3) 指示advice开启或关闭 NUMBER NUMBER NUMBER NUMBER Histogram区间内Optimal下限 (bytes) Histogram区间内Optimal上限 (bytes) Histogram区间内估计optimal次数 Histogram区间内估计onepass次数 Histogram区间内估计multipass次数 Histogram区间内估计执行总次数 Histogram区间内由于内存和cpu限制而被advice忽略的workarea数 ESTD_MULTIPASSES_EXECUTIONS NUMBER ESTD_TOTAL_EXECUTIONS IGNORED_WORKAREAS_COUNT
NUMBER NUMBER 有了这两张视图,我们可以很清楚得知道pga_aggregate_target该怎么设置为好.来看一个例子
SQL> select * from v$pga_target_advice;
134217728 .125 ON 1.3903E+12 8.6153E+10 94 32014 268435456 .25 ON 1.3903E+12 9871660032 99 0 536870912 .5 ON 1.3903E+12 4168520704 100 0 805306368 .75 ON 1.3903E+12 3538477056 100 0 1073741824 1 ON 1.3903E+12 2298456064 100 0 1288489984 1.2 ON 1.3903E+12 2256093184 100 0 1503238144 1.4 ON 1.3903E+12 2188313600 100 0 1717986304 1.6 ON 1.3903E+12 2188313600 100 0 1932734464 1.8 ON 1.3903E+12 2188313600 100 0 2147483648 2 ON 1.3903E+12 2188313600 100 0 3221225472 3 ON 1.3903E+12 2188313600 100 0 4294967296 4 ON 1.3903E+12 2188313600 100 0 6442450944 6 ON 1.3903E+12 2188313600 100 0 8589934592 8 ON 1.3903E+12 2188313600 100 0
我们看到PGA_TARGET_FACTOR从0.125一直到8,pga的大小从134217728bytes到8589934592bytes,当前的pga大小为1073741824bytes,当pga设置为134217728bytes时内存命中率为94%,268435456bytes时为99%,536870912bytes时就到100%了,当然这是一个估计值,实际上当pga小于1503238144时内存命中率都只是接近100%,这个可以从
ESTD_EXTRA_BYTES_RW列看出来.
v$pga_target_advice_histogram视图里有更清楚的分区间的统计数据,由于数据比较多,不在这里列出,有兴趣的话可以直接查询一下v$pga_target_advice_histogram.这两个视图给了我们直观数据来调整pga的设置,从此我们调整pga设置就不必像9i Release 2以前版本那么困难了.
自动PGA内存管理相关初始化参数
select a.KSPPINM,a.KSPPDESC,b.KSPPSTVL from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.ksppinm like '_smm%'; 参数名 pga_aggregate_target workarea_size_policy _smm_advice_enabled _smm_auto_min_io_size _smm_auto_max_io_size _smm_auto_cost_enabled _smm_control _smm_trace _smm_min_size _smm_max_size _smm_px_max_size _smm_bound _smm_advice_log_size
参数描述 默认值 Target size for the aggregate PGA Init.ora或spfile中memory consumed by the instance 设置 policy used to size SQL working AUTO areas (MANUAL/AUTO) if TRUE, enable v$pga_advice TRUE Minimum IO size (in KB) used by 56 sort/hash-join in auto mode Maximum IO size (in KB) used by 248 sort/hash-join in auto mode if TRUE, use the AUTO size policy TRUE cost functions provides controls on the memory 0 manager Turn on/off tracing for SQL 0 memory manager minimum work area size in auto 1024 mode maximum work area size in auto 5% mode (serial) pga_aggregate_target maximum work area size in auto 30% mode (global) pga_aggregate_target overwrites memory manager 0 automatically computed bound overwrites default size of the 0 PGA advice workarea history log
相关推荐：
loading