Oracle Advanced Performance Tuning Scripts

Shared Pool Scripts

shared_pool_lru_stats.sql

The shared pool has two LRU lists for unpinned recreatable chunks - one for transient objects, and one for recurrent use objects. This script shows the current length of the transient and recurrent LRU lists, together with the number of chunks that have been flushed (aged out) and the number of other LRU list operations (adds and removes). These statistics provide feedback on the size of the shared pool.

View sample listing

Download script for: 7.3, 8.0 to 9.2

shared_pool_free_lists.sql

Free chunks in the shared pool are maintained on a number of free lists, according to the chunk size. This scripts reports the length and total available free memory on each free list. Long free lists are a major cause of shared pool latch contention. However, because of a change in the structure of the free lists at 8.0.6/8.1.6, this is scarcely an issue any more.

View sample listing

Download script for: 7.3, 8.0.5 or 8.1.5, 8.0.6 or 8.1.6 and higher

shared_pool_summary.sql

This script gives an indication of the balance between free, recreatable, freeable and permanent memory in the shared pool, as well as a breakdown of the chunks and their usage.

View sample listing

Download script for: 7.3, 8.0 to 9.2

reserved_pool_summary.sql

As above, but for the reserved part of the shared pool.

Download script for: 7.3, 8.0 to 9.2

nice_shared_pool_flush.sql

Flushing the shared pool is the only way of fully coalescing free space in the shared pool, and has little performance impact if everything is kept that should be, particularly if done out of peak hours.

This script first calls keeper.sql prior to flushing, to minimize the unwanted side effects of flushing the shared pool.

Download script for: all versions


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home