DBA常用SQL语句（2）— SGA和PGA

检查 sga

show sga
select * from v$sga;

查看 buffer cache 设置建议

select 
size_for_estimate, 
estd_physical_read_factor,
to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" 
from v$db_cache_advice where name = 'DEFAULT';

COL pool FORMAT a10;
SELECT 
(
SELECT ROUND(value/1024/1024,0) 
FROM v$parameter
WHERE name = 'db_cache_size'
) "Current Cache(Mb)",
name "Pool", 
size_for_estimate "Projected Cache(Mb)",
ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;

查看 cache 池

show parameter cache

查看 buffer cache 中 defalut pool 命中率

select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;

检查 shared pool

show parameter shared

检查 shared pool 中 library cache

select namespace,pinhitratio from v$librarycache;

检查整体命中率 (library cache)

select sum(pinhits)/sum(pins) from v$librarycache;

select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;

检查 shared pool free space

SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

每个子 shared pool 由单独的 shared pool latch 保护，查看他们的命中率 shared pool latch，用于 shared pool 空间回收分配使用的 latch

col name format a15
select 
addr,name,gets,misses,1-misses/gets 
from v$latch_children 
where name='shared pool';

使用 v$shared_pool_advice 计算不同 shared pool 大小情况下，响应时间， S 单位

SELECT 
'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN 
current_parse_time_elapsed_s + adjustment_s < 0 THEN 0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (
SELECT 
shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100 current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice 
a,
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice
WHERE shared_pool_size_factor = 1) c)
/

查看 shared pool 中 各种类型的 chunk 的大小数量

SELECT 
KSMCHCLS CLASS, 
COUNT(KSMCHCLS) NUM, 
SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;

查看是否有库缓冲有关的等待事件

select 
sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state 
from v$session_wait 
where event like 'library%';

查询 sga 中各个 pool 情况

COL name FORMAT a32;
SELECT 
pool, name, bytes 
FROM v$sgastat
WHERE pool IS NULL 
OR pool != 'shared pool' 
OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
SELECT * FROM V$SGAINFO;

查看使用 shard_pool 保留池情况

SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;

Oracle 专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的 5%(_shared_pool_reserved_pct 5 控制 ) oracle 建设置为 10% 。大小通过参数 SHARED_POOL_RESERVED_SIZE 改。它是从共享池中分配，不是直接从 SGA 中分配的，它是共享池的保留部分，专门用于存储大块段#shared pool 中内存大于_SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池维护一个单独的 freelist,lru ，并且不会在 lru 列表存recreatable 类型 chunks ，普通 shared pool 的释放与 shared pool 保留池无关。

关于设置 SHARED_POOL_RESERVED_SIZE

1. 如果系统出现 ora-04031, 发现请求内存都是大于_SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的内存的请求失败 , 那么需要加大 SHARED_POOL_RESERVED_SIZE
2. 如果 ora-04031 请求内存出现在 4100-4400 并造成 shared pool lru 合并 , 老化换出内存 , 可以调小_SHARED_POOL_RESERVED_MIN_ALLOC 让此部分内存进入 shared reserved pool, 相应的加大 SHARED_POOL_RESERVED_SIZE
3. 从v$shared_pool_reserved 来判断 , 如果 REQUEST_FAILURES>0(出现过ora-04031) 且LAST_FAILURE_SIZE( 最后请求内存大小)>_SHARED_POOL_RESERVED_MIN_ALLOC 表示 shared reserved pool 缺少连续内存 , 可以加大 SHARED_POOL_RESERVED_SIZE, 减少_SHARED_POOL_RESERVED_MIN_ALLOC 少放对象 , 并相对加大shared_pool_size。要是反过来 REQUEST_FAILURES>0(出现过 ora-04031) 且LAST_FAILURE_SIZE(最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC,表示 在 shared pool 中缺少连续内存 , 可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些对象 , 减少 sharedpool 压力 , 适当加大shared_pool_size,SHARED_POOL_RESERVED_SIZE

查询还保留在 library cache 中，解析次数和执行次数最多的 sql( 解析 * 执行 )

COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;

查看 pga

show parameters area_size
SELECT * FROM v$pgastat;

查看 pga 建议

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;