Database Administration (MOSC)

MOSC Banner

how to interpret x$ksmsp having fragmented memory?

edited Aug 13, 2024 8:55PM in Database Administration (MOSC) 7 commentsAnswered

HI Friends,

refering to Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools (Doc ID 146599.1) to find shared pool fragmention. (session under X$KSMSP View (Similar to Heapdump Information)

How to tell how many chunks of free space are fragments or what is the percentage of chunks do you consider fragmented memory.

Statistics below: Please help to interpret if the following stats indicate fragmentation

SQL> SELECT '0 (<140)' bucket, ksmchcls, ksmchidx, 10TRUNC(ksmchsiz/10) "From",
COUNT(
) "Count", MAX(ksmchsiz) "Biggest",
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
2 3 4 5 WHERE ksmchsiz<140
AND ksmchcls='free'
6 7 GROUP BY ksmchcls, ksmchidx, 10

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center