Performance

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:

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

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

/
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

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

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.
 

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.
 

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?