Monday, July 27, 2015

Will offlloading work for OLTP-compressed tables ?


If you create OLTP- or BASIC-compressed table and run count(*) on it using
cell_offload_processing = TRUE or FALSE
there is no difference in time between runs.

If you look to this query via OEM 12c it shows no offloading in SQL Monitoring page.
But if you add WHERE clause to your query, then SQL Monitoring will show offloading and query's time become much less.

Latest documentation of June 2015 talk foggy about BASIC and OLTP compression:


" Predicate evaluation is not offloaded to Oracle Exadata Storage Server in the following cases:

 "
 Oracle® Exadata Storage Server Software User's Guide
12c Release 1 (12.1)
E50471-14, page 7-58

I completely don't understand should offloading work for Basic ans OLTP compressed tables or not.

So we did the research.
As you know, the offloading (smart scan) has 3 sides:
- Projection (return only selected columns)
- Filtering (return only rows according WHERE clause)
- Storage Index (exclude some extents from IO)

And our results are:
------------ ---------- --------- ------------
Table Type  Projection Filtering Storage Index
------------ ---------- --------- ------------
No Compress      +        +          +
Basic            -        +          +
OLTP             -        +          +
HCC              +        +          +
------------ ---------- --------- ------------

Wednesday, July 22, 2015

New ASM functionality



Very interesting information about 12c New Fetures in ASM:
 
ASMFD (ASM Filter Driver) Support on OS Platforms (Doc ID 2034681.1)

Oracle Automatic Storage Management Filter Driver (ASMFD) rejects write I/O requests that are not issued by Oracle software thus preventing corruption in Oracle ASM disks and files within the disk group.


 

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...