Chat now with support
Chat with Support

Spotlight on Oracle 10.7 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Spotlight on MySQL Troubleshooting: Connection Problems

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

  1. Select the Spotlight on Oracle connection in the Spotlight Browser.
  2. 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.


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.


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.


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.


  • 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




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.


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


Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating