oracle性能调优之--Share Pool调整与优化(四)
shared_pool_size
SELECT SUM(pins) "Executions",SUM(reloads) "CacheMisses while Executing",
ROUND(SUM(reloads)/SUM(pins)*100,2) AS "Reload Ratio, %" FROM V$LIBRARYCACHE;
ExecutionsCache Misses while Executing Reload Ratio, %
---------- -------------------------------------------
2777717625 1288253 .05
3.库缓存的命中率应保持在95%，否则应考虑增大shared_pool_size
SELECT SUM(pins) "Executions",SUM(reloads) "CacheMisses while Executing",
ROUND((SUM(pins)/(SUM(reloads)+SUM(pins)))*100,2) www.2cto.com
"HitRatio, %" FROM V$LIBRARYCACHE;
ExecutionsCache Misses while Executing Hit Ratio, %
---------- ----------------------------------------
2777727542 1288257 99.95
4.估算Library cache占用大小，shared pool的可用空间，总大小
--查看共享池可用空间，当sharedpool有过多的可用空间，再调大shared pool则意义不大
SELECT pool,name,bytes/1024/1024 FROM v$sgastat WHERE name LIKE '%free memory%' AND pool = 'sharedpool';
POOL NAME BYTES/1024/1024
----------- -----------------------------------------
sharedpool freememory 97.6241302
--查询已使用的Library cache大小总和
WITH cte AS(
SELECT SUM(sharable_mem) sharable_mem_count --查询非SQL语句(包，视图)占用的Library cache大小
FROM v$db_object_cache
UNION ALL
SELECT SUM(sharable_mem) --查询SQL语句占用的Librarycache大小
FROM v$sqlarea
)
SELECT SUM(sharable_mem_count)/1024/1024 --查询已使用的Library cache大小总和
FROM cte; --实际上还有一部分为用户游标使用占用的空间，此处略去
SUM(SHARABLE_MEM_COUNT)/1024/1024
---------------------------------
820.59599971771
--查询分配的shared_pool_size的大小
SELECT SUM(bytes)/1024/1024 FROM v$sgastat WHERE pool LIKE '%shar%';
SUM(BYTES)/1024/1024
--------------------
1216 www.2cto.com
SELECT * FROM v$sgainfo WHERE name LIKE 'Shared%';
5.查看shared pool的分配大小，已使用空间，可用空间，已用空间的百分比
column shared_pool_used format 9,999.99
column shared_pool_size format 9,999.99
column shared_pool_avail format 9,999.99
column shared_pool_pct format 999.99
SELECT SUM(a.bytes) / (1024 * 1024) shared_pool_used,
MAX(b.value) / (1024 * 1024) shared_pool_size,
(MAX(b.value) - SUM(a.bytes)) / (1024 * 1024) shared_pool_avail,
(SUM(a.bytes) / MAX(b.value)) * 100 Shared_pool_per
FROM v$sgastat a, v$parameterb
WHERE a.name IN ('table definiti',
'dictionary cache',
'library cache',
'sql area',
'PL/SQL DIANA')
AND b.name = 'shared_pool_size';
SHARED_POOL_USEDSHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PER
---------------- --------------------------------- ---------------
965.49 1,152.00 186.51 83.809699
6.根据上述的各个情况的判断，检查v$shared_pool_advice来判断增加shared_pool_size
SELECT shared_pool_size_for_estimate est_size,