X

Everything about Table Scans including offload to Exadata, Big Data SQL, and External Tables

  • January 23, 2018

How to tell if the Exadata column cache is fully loaded

Roger Macnicol
Software Architect

When a background activity is happening on the cell you typically can't use RDBMS v$ views to monitor it in the same way. One such question is how to tell if a segment is fully loaded in the Exadata column cache since this does not appear in the equivalent In-Memory v$ views.

When a segment is scanned by Smart Scan sufficiently often to be eligible the AUTOKEEP pool (typically that means at least twice an hour), the eligible 1MB chunks are written to flash in 12.1.0.2 style format, and put on a background queue. Lower priority tasks pick up the queued 1MB 12.1.0.2 format chunks from the flash cache, run them though the In-Memory loader, and rewrite the pure columnar representation in place of the old 12.1.0.2 style column cache chunks.

The easiest way that I know of to tell when this completes is to monitor that background activity is to use the following query until it shows zero:

select name, sum(value) value from (
      select extractvalue(value(t),'/stat/@name') name,
            extractvalue(value(t),'/stat') value
      from v$cell_state cs,
           table(xmlsequence(extract(xmltype(cs.statistics_value),
                                     '//stats[@type="columnarcache"]/stat'))) t
     where statistics_type='CELL')
     where name in ('outstanding_imcpop_requests')
     group by name;

Join the discussion

Comments ( 1 )
  • Chris Saturday, January 27, 2018
    Hello Roger,
    maybe not really a question to the heart of your article and maybe Im just stupid, but as there is hardly any reference anywhere else .... :
    This AUTOKEEP pool ... can you point me to any documentation about it ? In particular - in which storage server version was it introduced ?
    thanks,
    Chris
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.