Shared pool free lists length
                                                 Last update (2008-04-15 14:23:12)
                                                                                                                   Date added (2008-04-14 11:06:09)

SELECT DECODE(SIGN(KSMCHSIZ - 812), -1, (KSMCHSIZ - 16) / 4, 
DECODE(SIGN(KSMCHSIZ - 4012), -1, TRUNC((KSMCHSIZ + 11924) / 64), 
DECODE(SIGN(KSMCHSIZ - 65548), -1, TRUNC(1/LOG(KSMCHSIZ - 11, 2)) + 238, 254))) BUCKET,
  SUM(KSMCHSIZ)  FREE_SPACE,
  COUNT(*)  FREE_CHUNKS,
  TRUNC(AVG(KSMCHSIZ))  AVERAGE_SIZE,
  MAX(KSMCHSIZ)  BIGGEST
FROM SYS.X$KSMSP
WHERE INST_ID = USERENV('Instance') AND
  KSMCHCLS = 'free'
GROUP BY DECODE( SIGN(KSMCHSIZ - 812),-1, (KSMCHSIZ - 16) / 4, 
DECODE(SIGN(KSMCHSIZ - 4012), -1, TRUNC((KSMCHSIZ + 11924) / 64), 
DECODE(SIGN(KSMCHSIZ - 65548), -1, TRUNC(1/LOG(KSMCHSIZ - 11, 2)) + 238, 254)));

    BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE    BIGGEST
---------- ---------- ----------- ------------ ----------
         2       1752          73           24         24
         4       2432          76           32         32
         6       3240          81           40         40
        10       2520          45           56         56
        12       2304          36           64         64
        14       2520          35           72         72
        16       4400          55           80         80
        18       4136          47           88         88
        20       3456          36           96         96
        28        128           1          128        128
        82        344           1          344        344

    BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE    BIGGEST
---------- ---------- ----------- ------------ ----------
       207       1376           1         1376       1376
       232       2928           1         2928       2928
       254  129101232           2     64550616   67108848

14 rows selected.

Elapsed: 00:00:00.03