Hands-On 03 (Sizing the Shared Pool)

As a DBA, you are responsible for monitoring and changing the memory space allocation for the Shared Pool in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Resizing the Shared Pool Memory allocation

Keeping an object in the Shared Pool memory

Displaying the Library Cache Hit Ratio

Displaying the objects from the Shared Pool memory

Removing an object from the Shared Pool memory

Calculating the Dictionary Cache Hit Ratio

The Major components of the Shared Pool memory

The Library Cache

The Dictionary Cache

The User Global Area

Using the SHARED_POOL_RESERVED_SIZE parameter

Commands:

SHOW PARAMETER

SET SERVEROUTPUT ON

EXECUTE SYS.DBMS_SHARED_POOL.SIZES(150);

EXECUTE SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD');

EXECUTE SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD');

-- Hands-On 03 (Sizing the Shared Pool)
-- Sizing the shared pool

SET ECHO ON
--preparation...
CONNECT system/manager AS SYSDBA
set pagesize 100 linesize 1000
col VALUE_COL_PLUS_SHOW_PARAM format a30
col NAME_COL_PLUS_SHOW_PARAM format a30
col parameter format a20
pause

--Start

CLEAR SCR
-- In this exercise we will learn how to: re-size the 
-- Shared Pool, get the Library Cache Hit Ratio, get the objects
-- from the Shared Pool, KEEP or UNKEEP an object in the Shared Pool, 
-- and calculate the Dictionary Cache Hit Ratio.

-- Also, we learn about the major components of the Shared Pool
-- such as the Library Cache, the Dictionary Cache, and the User
-- Global Area.

-- Now, connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause


CLEAR SCR 
-- The major components of the shared pool are: the library cache,
-- the dictionary cache (row cache), and the User Global Area (UGA).

-- Let's first show the size of the shared pool memory.

pause

SHOW PARAMETER shared_pool



-- Notice that the SHARED_POOL_RESERVED_SIZE parameter 
-- can be reserved for large objects.

pause

CLEAR SCR
-- Let's measure and tune the Library Cache Hit Ratio.

-- Get the overall library cache hit ratio.

pause

SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 as "Library Cache Hit Ratio"
FROM v$librarycache
/

-- Assuming that the instance has been up and running for a while,
-- the hit ratio must be above 99 percent.

-- If the hit ratio is very high it means that the objects have
-- not been aged out of the cache.

pause

CLEAR SCR
-- Query more details about the hit ratio from each library cache item.

pause
SELECT namespace, gethitratio 
FROM v$librarycache
WHERE gethitratio > 0
/
-- Any ratio below 99, indicates the objects have been aged out of
-- the cache at an unacceptable rate.

-- If the ratio is very low it also is possible that there were
-- either a minimal or no requests. In this case it is normal to
-- have a low ratio.
pause

CLEAR SCR

-- Show the count for each object type that was cached more than
-- 100 times.

pause
SELECT type, count(*) as "Number of Objects"
FROM v$db_object_cache
GROUP BY type
HAVING count(*) > 100
/
pause

CLEAR SCR
-- Now, show any objects with sizes larger than 150K.

pause


SET SERVEROUTPUT ON 
EXECUTE SYS.DBMS_SHARED_POOL.SIZES(150);

pause

CLEAR SCR
-- Use the KEEP procedure to pin the STANDARD package.

pause


EXECUTE SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD');

-- Now your object stays in the memory.

pause

CLEAR SCR
-- Use the UNKEEP procedure to change the status of the pinned object.

pause

EXECUTE SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD');

-- Now your object will not stay in the memory.

pause

CLEAR SCR
-- Measure and tune the Dictionary Cache Hit Ratio.
-- First, query the V$ROWCACHE view to determine the hit ratio for each 
-- item in the dictionary cache.
pause

SELECT parameter, gets, getmisses, 100*(gets-getmisses)/(gets) hit_ratio, modifications 
FROM v$rowcache WHERE gets> 0
/
-- The hit ratio must be above 98 percent.
-- The low hit ratios indicate that the objects have not been 
-- loaded into the cache yet.
pause

CLEAR SCR
-- Calculate the overall dictionary cache hit ratio.

pause

SELECT (SUM(gets - getmisses)) / SUM(gets) "Dictionary Hit Ratio"
FROM v$rowcache
/

-- The hit ratio should be above 85 percent.
-- If the ratio is below 85 percent, you may need to increase the shared pool size.

pause

CLEAR SCR
-- Query the User Global Area (UGA) statistics.

pause

SELECT name, SUM(value)
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND UPPER(name) like '%UGA%'
GROUP BY name
/

-- In this query, you can not differentiate between the shared server 
-- and dedicated processes.

-- It shows the current memory consumptions and the maximum memory
-- that each session has used.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause