Category Archives: 12.2

DMLs and the Columnar Cache on ADW

Posted on by Posted in 12.2, inmemory, oracle 1,956 Page views Leave a comment

One of the main performance technologies underlying ADW is the In-Memory format column cache and a question that has come up several times is: how does the columnar cache handle DMLs and cache invalidations. This blog post attempts to answer that question.

The Two Columnar Cache Formats

The original columnar cache was an idempotent rearrangement of Hybrid Columnar Compressed blocks into pure columnar form still in the original HCC encoding. This is known internally as “CC1” and was not applicable to row format blocks. Because this is an idempotent transformation we are able to reconstitute the original raw blocks from a CC1 cache if needed.

We then introduced the In-Memory format columnar cache where, if you had an In-Memory licence, we would run the 1 MB chunks processed by Smart Scan through the In-Memory loader and write new clean columns encoded in In-Memory formats which meant that we could then use the new SIMD based predicate evaluation and other performance improvements developed for Database In-Memory. If you do not have an In-Memory licence, the original CC1 column cache is used. Another advantage of the CC2 format is that we can load row format blocks as well as HCC format blocks into pure columnar In-Memory format.  

Continue reading→

Reading and analyzing trace file contents using just SQL

Posted on by Posted in 12.2, oracle, trace, troubleshooting 1,667 Page views Leave a comment

Simple example: tracefiles for the last 5 days:

select fc.* 
from v$diag_trace_file f
     join v$diag_trace_file_contents fc
          on f.adr_home=fc.adr_home
          and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
  and fc.timestamp  >= systimestamp - interval'5' minute
  and fc.component_name = 'SQL_Trace'
  --and fc.section_name like 'kests%'
  ;
--or:
select tr.*
  from v$diag_app_trace_file tf,
       v$diag_sql_trace_records tr
 where tf.sql_trace = 'Y'
   and tf.modify_time > systimestamp - interval'5'minute
   and tr.adr_home = tf.adr_home
   and tr.trace_filename = tf.trace_filename
   and tr.timestamp > systimestamp - interval'5'minute;

Oracle issues after upgrade to 12.2

Posted on by Posted in 12.2, bug, oracle 2,495 Page views 2 Comments

Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from Eric van Roon). I’m going to remove the bugs from this list when they become “public” or “fixed”.
If you want to add own findings into this list, you can add them into comments. To make this process easier, you can provide just symptomps, short description and the link to own post with details – I’ll add it just as a link.
Continue reading→

What’s new in 12.2 CELLMEMORY Part 3

Posted on by Posted in 12.2, inmemory, oracle, SmartScan 1,596 Page views 1 Comment

The Cellmemory Stats in RDBMS 

The RDBMS stats for Cellmemory are designed to closely follow the pattern used by the Inmemory stats 

Query Stats 

 Each column in each one MB of disk blocks will be rewritten into one IMC format Column CU in flash and a set of Column CUs comprise an overall Compression Unit so these stats reflect the number of 1 MB rewrites that were processed (not the number of column CUs).

  1. “cellmemory IM scan CUs processed for query”
    – #1 MB chuncks scanned in MEMCOMPRESS FOR QUERY format
  2. “cellmemory IM scan CUs processed for capacity”
    – #1 MB chuncks scanned in MEMCOMPRESS FOR CAPACITY format
  3. “cellmemory IM scan CUs processed no memcompress”
    – #1 MB chuncks scanned in NO CELLMEMORY format (12.1.0.2 format)

Load Stats

  1. “cellmemory IM load CUs for query”
    – #1 MB chunks successfully rewritten from 12.1.0.2 to MEMCOMPRESS FOR QUERY format  
  2. “cellmemory IM load CUs for capacity”
    – #1 MB chunks successfully rewritten from 12.1.0.2 to MEMCOMPRESS FOR CAPACITY format
  3. “cellmemory IM load CUs no memcompress”
    – #1 MB chunks successfully rewritten into 12.1.0.2 format

Before a rewrite happens a routine is called that looks through the blocks in the 1 MB chunk and determines if it is eligible for write. Reasons it may not be include transactional metadata from the commit cache, the presence of blocks formats that can’t be rewitten (although this list is getting smaller with each rpm), and the amount of space the rewrite will take up.

A rewrite into 12.1.0.2 format must fit in the original 1 MB of flash cache. An IMC format rewrite is not permitted to exceed 8 MB. This limit is highly unlikely to be reached by MEMCOMPRESS FOR CAPACITY but could be reached when trying to rewrite HCC blocks with much greater than 8X original compression capacity into MEMCOMPRESS FOR QUERY format. This is one reason that the default is FOR CAPACITY.

  1. “cellmemory IM scan CUs rejected for query”
    – #1 MB chunks that could not be rewritten into MEMCOMPRESS FOR QUERY for whatever reason
  2. “cellmemory IM scan CUs rejected for capacity
    – #1 MB chunks that could not be rewritten into MEMCOMPRESS FOR CAPACITY for whatever reason
  3. “cellmemory IM scan CUs rejected no memcompress”
    – #1 MB chunks that could not even be rewritten into 12.1.0.2 format for whatever reason

What’s new in 12.2 CELLMEMORY Part 1

Posted on by Posted in 12.2, inmemory, oracle, SmartScan 1,646 Page views 1 Comment

Many people know that in 12.1.0.2 we introduced a ground-breaking columnar cache that rewrote 1 MB chunks of HCC format blocks in the flash cache into pure columnar form in a way that allowed us to only do the I/O for the columns needed but also to recreate the original block when that
was required.

This showed up in stats as “cell physical IO bytes saved by columnar cache”.

But in 12.1.0.2 we had also introduced Database In-Memory (or DBIM) that rewrote heap blocks into pure columnar form in memory. That project introduced: 

  • new columnar formats optimized for query performance
  • a new way of compiling predicates that supported better columnar execution
  • the ability to run predicates against columns using SIMD instructions which could execute the predicate against multiple rows simultaneously

 so it made perfect sense to rework the columnar cache in 12.2 to take advantage of the new In-Memory optimizations.

Quick reminder of DBIM essentials

In 12.2, tables have to be marked manually for DBIM using the INMEMORY keyword:

SQL> Alter Table INMEMORY 

When a scan on a table tagged as INMEMORY is queried the background process is notified to start loading it into the INMEMORY area. This design was adopted so that the user’s scans are not impeded by loading. Subsequent scans that come along will check what percentage is currently loaded in memory and make a rule based decision:

For Exadata 

  • Greater than 80% populated, use In-Memory and the Buffer Cache for the rest
  • Less than 80% populated, use Smart Scan
  • The 80% cutoff between BC and DR is configurable with an undocumented underscore parameter
  • Note: if an In-Memory scan is selected even for partially populated, Smart Scan is not used

For non-Exadata

  • Greater than 80% populated, use In-Memory and the Buffer Cache for the rest
  • Less than 80% populated, use In-Memory and Direct Read for the rest
    Note: this requires that segment to be check pointed first
  • The 80% cutoff between BC and DR is configurable with an undocumented underscore parameter

 While DBIM can provides dramatic performance improvements it is limited by the amount of usable SGA on the system that can be set aside for the In-Memory area. After that performance becomes that of disk access to heap blocks from the flash cache or disk groups. What was needed was a way to increase the In-Memory area so that cooler segments could still benefit from the In-Memory formats without using valuable RAM which is often a highly constrained resource.

Cellmemory

Cellmemory works in a similar way to the 12.1.0.2 columnar cache in that 1 MB of HCC formatted blocks are cached in columnar form automatically without the DBA needing to be involved or do anything. This means columnar cache scans are cached after Smart Scan has processed the blocks rather than before as happens with ineligible scans.  The 12.1.0.2 columnar cache format simply takes all the column-1 compression units (CUs) and stores them contiguously, then all the column-2 CUs stored contiguously etc. so that each column can be read directly from the cache without reading unneeded columns. This happens during Smart Scan so that the reformated blocks are returned to the cell server along with the query results for that 1 MB chunk.

In 12.2, eligible scans continue to be cached in the 12.1.0.2 format columnar cache format after Smart Scan has processed the blocks so that columnar disk I/O is available immediately. The difference is that if and only if the In-Memory area is in use on the RDBMS node (i.e. the In-Memory feature is already in use in that database), the address of the beginning of the columnar cache entry is added to a queue so that a background process running at a lower priority can read those cache entries and invoke the DBIM loader to rewrite the cache entry into In-Memory formatted data.

Unlike the columnar cache which has multiple column-1 CUs, the IMC format creates a single column using the new formats that use semantic compression and support SIMD etc on the compressed intermediate compressed data. By default a second round of compression using LZO is then applied. When 1 MB of HCC ZLIB compressed blocks are rewritten in this way they typically take around 1.2 MB (YMMV obviously).

Coming up

Up-coming blog entries will cover: