KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.

I just noticed this in an 11.2 instance:

SQL> select * from v$sgastat where name like ‘KGH%';
POOL         NAME                            BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS                4186144
So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it’s unable to flush everything out from the granule for complete granule handover.
Let’s see whether that’s the case:

SQL> select last_oper_type, last_oper_mode from v$sga_dynamic_components where component = 'streams pool';
LAST_OPER_TYP LAST_OPER
------------- ---------
SHRINK        DEFERRED
Yep, the last streams pool shrink operation was left in DEFERRED status, which means the granule wasn’t handed over – streams pool kept the granule for itself, marked everything it could flush out as KGH: NO ACCESS in its heap header and handed these chunks over to buffer cache manager.
Lets check whether these chunks are actually used by buffer cache buffers:
(NB! Think twice before running this query in production as it may hold your shared pool latches for very long time):
SQL> select ksmchidx,ksmchdur,ksmchcom,ksmchptr,ksmchsiz,ksmchcls from x$ksmsst where ksmchcom = 'KGH: NO ACCESS';
  KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS
---------- ---------- ---------------- ---------------- ---------- --------
         1          4 KGH: NO ACCESS   00000003A2401FE0    4186144 no acce
So, there’s only one chunk flushed & handed over to buffer cache in the streams pool heap. The KSMCHPTR column shows the starting address of that chunk in SGA and the KSMCHSIZ is the size of that chunk.
So, let’s see if there are any buffers within that address range. First I’ll calculate the end address of that chunk (start address + size -1 = end address)

SQL> @calc 0x00000003A2401FE0 + 4186143
                     DEC                  HEX
------------------------ --------------------
         15611199487.000            3A27FFFFF
And now lets query X$BH using that address range to see if / how many buffer cache buffers have been placed in there:

SQL> select count(*) from x$bh where rawtohex(ba) between '00000003A2401FE0' and '00000003A27FFFFF';

  COUNT(*)
----------
       483
We have just proven, that there are 483 buffer cache buffers (~3.8MB, 8kB buffers) which reside physically in streams pool heap!
This entry was posted in Cool stuff, Oracle and tagged , , . Bookmark the permalink.

2 Responses to KGH: NO ACCESS – Buffer cache inside streams pool too!

  1. John says:

    Hi Tanel,

    It happens in 10.2.0.4 too. Look:

    SQL> r
    1 select banner
    2* from v$version

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
    PL/SQL Release 10.2.0.4.0 – Production
    CORE 10.2.0.4.0 Production
    TNS for HPUX: Version 10.2.0.4.0 – Production
    NLSRTL Version 10.2.0.4.0 – Production

    SQL> compute sum of bytes on pool
    SQL> r
    1 select * from v$sgastat
    2* order by 1,2

    POOL NAME BYTES
    ———— ————————– ———-
    java pool free memory 103868800


    streams pool KGH: NO ACCESS 16760864
    Sender info 28560
    fixed allocation callback 336
    free memory 33562392
    image handles 432
    kgqmdm_fl_1 216
    ….
    ….

    SQL> R
    1 SELECT *
    2 from v$sga_dynamic_components
    3* where component=’streams pool’

    COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP
    —————————————————————- ———— ———- ———- ——————- ———- ————-
    LAST_OPER LAST_OPER_ GRANULE_SIZE
    ——— ———- ————
    streams pool 33554432 16777216 0 0 6 SHRINK
    DEFERRED 2010/09/21 16777216

    SQL> select *
    2 from x$ksmsst
    3 where ksmchcom=’KGH: NO ACCESS';

    ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
    —————- ———- ———- ———- ———- —————- —————- ———- ——– ———- —————-
    9FFFFFFFBF309690 6 1 1 4 KGH: NO ACCESS C000000351001FE0 16760864 no acce 0 00

    SQL> select count(8)
    from x$bh
    where rawtohex(ba) between
    ‘C000000351001FE0′ and ‘C000000351FFE000′;

    COUNT(8)
    ———-
    1969

    Thank you

    John Ospino Rivas

  2. Tanel Poder says:

    @John
    Cool, thanks for letting us know!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>