DBA common SQL statements (2) - SGA and PGA

Check sga

show sga
select * from v$sga;

see buffer cache Set up suggestions

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;

see cache pool

show parameter cache

see buffer cache in defalut pool shooting

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

Check shared pool

show parameter shared

Check shared pool in library cache

select namespace,pinhitratio from v$librarycache;

Check the overall hit rate (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;

Check shared pool free space

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

Each child shared pool Separate shared pool latch Protect , Check their hit rate shared pool latch, be used for shared pool Space recycling is allocated to latch

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

Use v$shared_pool_advice The calculation is different shared pool Size case , response time , S Company

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)
/

see shared pool in All kinds of chunk The size and quantity of

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;

Check if there are waiting events related to the library buffer

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

Inquire about sga In each pool situation

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;

View usage shard_pool Retention pool

SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;

Oracle A special area is built from the shared pool to allocate memory and keep these large blocks . The default size of this reserved shared pool is... Of the shared pool 5%(_shared_pool_reserved_pct 5 control ) oracle The construction is set to 10% . Size by parameter SHARED_POOL_RESERVED_SIZE Change . It is allocated from the shared pool , Not directly from SGA Allocated in , It is the reserved part of the shared pool , Dedicated to storing large segments #shared pool Memory in is greater than _SHARED_POOL_RESERVED_MIN_ALLOC Put in shared pool Keep the pool , The retention pool maintains a separate freelist,lru , And not in lru List save recreatable type chunks , Ordinary shared pool Release and shared pool Retention pool independent .

About settings SHARED_POOL_RESERVED_SIZE

1. If the system appears ora-04031, It is found that the requested memory is greater than _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , And v$shared_pool_reserved There's a lot of REQUEST_MISSES( And you can see LAST_MISS_SIZE ) Express SHARED_POOL_RESERVED_SIZE Too small, request for large memory failed , Then we need to increase SHARED_POOL_RESERVED_SIZE
2. If ora-04031 The requested memory appears in 4100-4400 And create shared pool lru Merge , Aging swap out memory , It can be turned down _SHARED_POOL_RESERVED_MIN_ALLOC Let this part of memory enter shared reserved pool, Increase accordingly SHARED_POOL_RESERVED_SIZE
3. from v$shared_pool_reserved To judge , If REQUEST_FAILURES>0( There have been ora-04031) And LAST_FAILURE_SIZE( Last request memory size )>_SHARED_POOL_RESERVED_MIN_ALLOC Express shared reserved pool Missing contiguous memory , Can increase SHARED_POOL_RESERVED_SIZE, Reduce _SHARED_POOL_RESERVED_MIN_ALLOC Put fewer objects , And relatively increase shared_pool_size. If the other way around REQUEST_FAILURES>0( There have been ora-04031) And LAST_FAILURE_SIZE( Last request memory size )<_SHARED_POOL_RESERVED_MIN_ALLOC, Express stay shared pool Missing contiguous memory in , Can increase or decrease _SHARED_POOL_RESERVED_MIN_ALLOC Put more objects , Reduce sharedpool pressure , Appropriately increase shared_pool_size,SHARED_POOL_RESERVED_SIZE

The query remains in library cache in , The one with the most parsing and execution times sql( analysis * perform )

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;

see pga

show parameters area_size
SELECT * FROM v$pgastat;

see pga Suggest

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;