Shared Pool Utilization Page
Shared Pool Utilization shows how the Shared Pool area of the SGA (System Global Area) is used to store cached data dictionary information, shared SQL statements, PL/SQL and Java objects, cursors, procedures, functions, packages and triggers.
The shared pool allows items that are used by many sessions to remain in memory as long as they are useful. In doing so, it reduces parse overhead — and improves database performance — for a multi-user Oracle system.
To open the Shared Pool Utilization Page
- Select the Spotlight on Oracle connection in the Spotlight Browser.
-
Click Configuration & Memory | Shared Pool Utilization.
Library Cache Statistics Grid
The library cache is the part of the Shared Pool that stores the executable form of recently referenced SQL and PL/SQL code. Library Cache Statistics displays the following information about the library cache:
| Column | Description |
|---|---|
|
Library name |
The name of a library unit in the library cache. |
|
Gets |
The number of lock requests for objects of the specified library. A successful get is called a get hit. |
|
Gets hit ratio |
The ratio of get hits to the total number of gets. |
|
Pins |
The number of lock requests for objects previously found in the library cache. A successful pin is a called a pin hit. |
|
Pins hit ratio |
The ratio of pin hits to the total number of pins. |
|
Reloads |
A reload occurs when a pin does not result in a pin hit. The requested library cache object must be rebuilt because it has been aged out or invalidated. |
Grid: Objects cached in the Shared Pool
Select the object type to display.
Notes:
- Spotlight returns a maximum of 100 rows for performance reasons.
- If you re-sort the contents of the grid, you will sort only the 100 rows originally returned, NOT the top 100 rows according to the new sort column.
|
Object Type |
Description |
Criterion |
|---|---|---|
|
Cursors |
Cursors shows information about cached cursors and whether or not they are kept. |
Sharable memory |
|
PL/SQL objects |
PL/SQL objects shows information about cached PL/SQL procedures, functions, packages, triggers and sequences, and whether or not they are kept. |
Sharable memory |
|
Java objects |
Java objects shows information about cached Java objects and whether or not they are kept. |
Sharable memory |
|
Large anonymous blocks |
Large Anonymous Blocks shows the SQL text of the large anonymous blocks. |
Row number |
Button: Flush Shared Pool
Click Flush Shared Pool to flush data manually from the shared pool.
Clicking this button is equivalent to executing the Oracle command ALTER SYSTEM FLUSH SHARED_POOL. It can be used to assess what the performance of the database would be immediately after instance startup.
Notes:
- You need to be a Spotlight user with ALTER SYSTEM privileges.
- The button is enabled for Oracle databases that support manual flushing of the shared pool area.
- Flushing the shared pool may adversely affect the performance of the database in the short term.
- An alternative way to execute this command is via the SGA Panel.
- Any data in the shared pool remains there until it is flushed according to a modified LRU (least recently used) algorithm. Oracle also flushes data from the pool automatically whenever that data is invalidated.