7 内存设置

7.2

1.高速缓存命中率的计算（Buffer Cache hit ratio）

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('db block gets', 'consistent gets', 'physical reads')

计算公式：1－(physical reads/(consistent gets+db block gets));

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS

通过改变DB_CACHE_SIZE参数，改变buffer cache的大小。

7.3共享池和大型池参数的设置

1.库缓存命中率的查询语句

select SUM(PINHITS)/SUM(PINS) LIBRARY_CACHE_HIT_RATIO
from V$LIBRARYCACHE

2.查询共享池库缓存性能动态视图中每个项目的性能指标，通过以下语句

SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;

3.查询共享池中空闲内存

SELECT * FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool';

4.共享池中数据字典缓存各指标项的性能及数据查询命中率

column parameter format a21

column pct_succ_gets format 999.9

column updates format 999,999,999

 

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;

命中率建议高于85％-90％

5.会话UGA内 存，会话UGA内存峰值

SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

6.单一用户会话内存大小使用的限制（较少使用）

PRIVATE_SGA

7.缓存会话游标

ALTER SESSION SET SESSION_CACHED_CURSORS = value ;（value为正整数）

计算会话游标缓存的命中率

select * from v$sysstat
where name in ('session cursor cache hits','session cursor cache count')

计算公式：session cursor cache hits/session cursor cache count

8.设置用于PL/SQL、触发器编译、装载java对象的 临时空间等情况的保留池（reserved pool）

SHARED_POOL_RESERVED_SIZE

默认情况下，其大小为SHARED_POOL_SIZE 的5％，一般情况下，可以将其设置为 SHARED_POOL_SIZE大小的10％

9.保留池的调优

视图 V$SHARED_POOL_RESERVED，如有充足的内存增大SGA，那么调优的目标是视图的 REQUEST_MISSES 项数据接近于0，如果整个硬件系统的环境比较苛刻，那么调优的目标是 REQUEST_FAILURES项值为0，或者至少保证 其不持续增长。否则，需要增大 SHARED_POOL_SIZE的大小

10.将重要对象保留在共享池中

DBMS_SHARED_POOL .KEEP

execute sys.dbms_shared_pool.keep ('SYS.STANDARD');

或者

begin
dbms_shared_pool.keep('process_date','p');
end;
/

运行上面过程之前需要执行DBMSPOOL.SQL脚本

11.解决系统中有许多相似的SQL语句

CURSOR_SHARING（默认值 EXACT），可选值EXACT,SIMILAR,FORCE

7.4

1.REDO LOG BUFFER首次评估值

MAX(0.5M, (128K * number of cpus))

2.计算日志缓存重试率

Select Retries.Value / Entries.Value "Redo log Buffer Retry Ratio"
From V$sysstat Entries, V$sysstat Retries
Where Entries.Name = 'redo entries'
And Retries.Name = 'redo buffer allocation retries'

比值应该小于百分之一

3.查询哪些会话的LGWR正在写等待

Select s.Username, Sw.Wait_Time, Sw.Seconds_In_Wait, Sw.State
From V$session_Wait Sw, V$session s
Where Sw.Sid = s.Sid And Sw.Event Like '%log buffer space%';

State有四个取 值：WAITING(会话正在等待),WAITED UNKNOWN TIME(等待时间未知),WAITED SHORT TIME(等待时间小于百分之一秒),WAITED KNOWN TIME(等待时间已知，为wait_time栏位所示的时间);

7. 5

1.计算PGA缓存命中

BP x 100 / (BP + EBP)

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

命中率建议高于60％

2.工作区（work area）中optimal size、one－pass、multi－pass三种运行方式的次数占比

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
 (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
         sum(OPTIMAL_EXECUTIONS) optimal_count,
         sum(ONEPASS_EXECUTIONS) onepass_count,
         sum(MULTIPASSES_EXECUTIONS) multipass_count
    FROM v$sql_workarea_histogram
   WHERE low_optimal_size > 64*1024);

自实例启动后的累计值

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%');

3.不同work_area大小下，optimal size、one－pass、multi－pass三者的运行次数

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

one-pass模式下，内存不需要太大，对1GB的数据进行排序只需要22MB

4.当前工作区中活动的操作

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

5.工作区中排名前十的，需要大量内存的查询操作

SELECT *
FROM
( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size )
WHERE ROWNUM <= 10;