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;