|
"Well done is
better than well said." - Benjamin Franklin (1706-1790) |
Read
first then play the video:
PRF-VIDEO -Sizing
the Shared Pool
Sizing the Shared Pool
Introduction
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
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.
SQL> CONNECT system/manager AS SYSDBA
The major components of the shared pool are: the library cache, the
dictionary cache (row cache), and the User Global Area (UGA).
View shared pool
information
Let's first show the size of the shared pool memory.
SQL> SHOW PARAMETER shared_pool
Notice that the SHARED_POOL_RESERVED_SIZE parameter can be
reserved for large objects.
Library Cache Hit Ratio
Let's measure and tune the
Library Cache Hit Ratio. Get the overall library cache hit ratio.
SQL> 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.
Details Library Cache Hit
Ratio
Query more details about the hit ratio from each library cache
item.
SQL> 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.
View Cached objects
Show the count for each object type that was cached more than 100
times.
SQL> SELECT type, count(*) as "Number
of Objects"
FROM v$db_object_cache
GROUP BY type
HAVING count(*) > 100
/
View Large objects
Now, show any objects with sizes larger than 150K.
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE SYS.DBMS_SHARED_POOL.SIZES(150);
Cache a PL/SQL procedures
Use the KEEP procedure to pin the STANDARD package.
SQL>
BEGIN
SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD');
END;
/
Now your object stays in the memory.
Note that the
DBMS_SHARD_POOL package contains the stored procedures (functions
and/or procedures) that provide ease of use for the developers to
manipulate size of the shared pool, allocate objects in the shared
pool, etc.
Change status of an object
Use the UNKEEP procedure to change the status of the pinned
object.
SQL>
BEGIN
SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD');
END;
/
Now your object will not stay in the memory.
Dictionary Cache Hit Ratio
Measure and tune the Dictionary Cache Hit Ratio. Query the
V$ROWCACHE view to determine the hit ratio for each item in the
dictionary cache.
SQL> 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.
Overall Dictionary Cache
Hit Ratio
Calculate the overall dictionary cache hit ratio.
SQL> SELECT (SUM(gets - getmisses)) /
SUM(gets)
AS "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.
View UGA statistics
When you are running
dedicated servers then the session information can be stored inside
the process global area (PGA). The UGA is the user global area,
which holds session-based information. When you are
running shared servers then the session information can be stored
inside the user global area (UGA) and when your session does some
sorting, some of the memory allocated for sorting - specifically the
amount defined by parameter sort_area_retained_size -
comes from the SGA and the rest (up to sort_area_size)
comes from the PGA (Snnn). This is because the
sort_area_retained_size may have to be held open as the
pipeline to return results to the front-end, so it has to be located
where the session can find it again as the session migrate from server
to server. On the other hand, the sort_area_size is a
complete throwaway, and by locating it in the PGA, Oracle can make
best use of available memory without soaking the SGA. To avoid
sessions grabbing too much memory in the SGA when running MTS/shared
server, you can set the private_sga value in the resource_limit
for the user. This ensures that any particularly greedy SQL that (for
example) demands multiple allocations of sort_area_retained_size
will crash rather than flushing and exhausting the SGA.
Query the User Global Area (UGA) statistics.
SQL> 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.
|
"Learning is
what most adults will do for a living in the 21st century."
- Perelman |
Questions:
Q: How do you change a size
of the shared pool?
Q: How do you keep an
object in the Shared Pool memory?
Q: How do you remove an
object from the Shared Pool memory?
Q: How do you calculate the
Dictionary Cache Hit ratio value?
Q: What are the Major
components of the Shared Pool Memory?
Q: What is the User Global
Area (UGA)?
Q: When does the Oracle
database use UGA?
Q: What does the
SHARED_POOL_RESERVED_SIZE parameter?
Q: What does the
DBMS_SHARED_POOL package?
|