使用spfile

当sga_target设置不为零时，可以查看sga target设置建议。
SELECT SGA_SIZE, SGA_SIZE_FACTOR FROM V$SGA_TARGET_ADVICE;

查看db_cache_size建议
select name, SIZE_FOR_ESTIMATE, SIZE_FACTOR FROM V$DB_CACHE_ADVICE;

查看db_cache_size的命中率
SELECT NAME, VALUE
  FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');

1-phy reads/(db block gets from cache + consistent gets)

查看share_pool_size建议

select SHARED_POOL_SIZE_FOR_ESTIMATE, SHARED_POOL_SIZE_FACTOR FROM V$SHARED_POOL_ADVICE;

SELECT parameter
     , sum(gets)
     , sum(getmisses)
     , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
  FROM V$ROWCACHE
 WHERE gets > 0
 GROUP BY parameter;

查看parse的命中率
select statistic#, name from v$statname where name like '%parse%';

select name, value from v$sysstat where statistic# in (338,339,340);

large_pool_size

查看java_pool_size的建议
select JAVA_POOL_SIZE_FOR_ESTIMATE, JAVA_POOL_SIZE_FACTOR FROM V$JAVA_POOL_ADVICE;

-- statistics_level = typical / all

===============================Tune pga memory
自动pga管理
PGA_AGGREGATE_TARGET
WORKAREA_SIZE_POLICY

pga组成部分
PGA: SQL workareas and untune memory

查看统计信息
select name, value from v$sysstat where name like 'workarea%';

查看process的内存使用情况
select * from v$process;

SELECT sum(pga_used_mem), sum(pga_alloc_mem), sum(pga_max_mem) FROM v$process;

select * from V$SQL_WORKAREA;

SELECT *
FROM (SELECT workarea_address,
operation_type,
policy,
estimated_optimal_size
FROM v$sql_workarea
ORDER BY estimated_optimal_size DESC)
WHERE ROWNUM <= 10;

查看使用disk的queries
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;

查看pga的建议
SELECT PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR,ESTD_PGA_CACHE_HIT_PERCENTAGE FROM V$PGA_TARGET_ADVICE;

SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
    FROM (SELECT name, value cnt, (sum(value) over ()) total
    FROM V$SYSSTAT
    WHERE name like 'workarea exec%');