Shared Pool

Shared Pool
Library Cache (sql, pl/sql)
Dictionary Cache (Dictionary Data)

Use one or two schema – to reduce shared pool metadata

Avoid DDL during highly activity time

-- plsql, sql, latches
select namespace, gets, pins, reloads, invalidations from V$librarycache 
-- database objects (tables, views, synonyms...)
SELECT COUNT entries, gets requests, getmisses misses, modifications dml_activity, parameter area FROM v$rowcache  

SELECT * FROM v$shared_pool_reserved

SELECT  (SELECT ROUND(VALUE/1024/1024,0) FROM v$parameter
          WHERE NAME = 'shared_pool_size') "Current MBB",
         shared_pool_size_for_estimate "Projected Mb",
         ROUND(shared_pool_size_factor*100) "%",
         estd_lc_size "Library Mb",
         estd_lc_time_saved "Parse Savings",
         estd_lc_memory_object_hits "Hits"
    FROM v$shared_pool_advice 
ORDER BY 1     

--LIBRARY CACHE Ratios         
SELECT 'Library Lock Requests' RATIO,
        ROUND(AVG(gethitratio)*100,2) || '%' Percentage
  FROM v$librarycache    
UNION 
SELECT 'library pin requests' RATIO,
        ROUND(AVG(pinhitratio)*100,2) || '%' Percentage
  FROM v$librarycache 
UNION 
SELECT 'library i/o reloads' RATIO,
        ROUND((SUM(reloads)/SUM(pins))*100,2) || '%' Percentage
  FROM v$librarycache   
UNION 
SELECT 'library reparses' RATIO,
        ROUND((SUM(reloads)/SUM(pins))*100,2) || '%' Percentage
  FROM v$librarycache     
UNION 
SELECT 'Dictionary Cache Hit Ratio' RATIO,
        ROUND((1-(SUM(getmisses)/SUM(gets)))*100,2) || '%' Percentage
  FROM v$rowcache    

DBMS_SHARED_POOL – (keep/unkeep) – pin object in Shared Pool

Large Pool – No aging out process like LRU MRU. Not organized like Shared Pool. Used for Parallel Operations, Rman not to mess up Shared Pool

SELECT * FROM v$librarycache
SELECT * FROM v$library_cache_memory
SELECT * FROM v$db_object_cache
SELECT * FROM v$rowcache

Parameters

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE – by default 10 percent. Area for very large transactions. Contigous chunk for large operations

Caching Cursors

CURSOR_SPACE_FOR_TIME

SESSION_CACHED_CURSORS

SELECT * FROM V$SQL_BIND_CAPTURE
SELECT * FROM v$sql
SELECT * FROM v$sql_plan
SELECT * FROM v$sql_shared_cursor
SELECT * FROM v$sql_monitor