Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |


Lesson 03

"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



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









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.
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.


                    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.

Cache a PL/SQL procedures
Use the KEEP procedure to pin the STANDARD package.




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.




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



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?