Oakies Blog Aggregator

A recent post on the ODC database forum prompted me to write a short note about a trap that catches everyone from time to time. The trap is following the obvious; and it’s a trap because it’s only previous experience that lets you decide what’s obvious and the similarity between what you’re looking and your previous experience may be purely coincidental.

The question on OTN (paraphrased) was as follows:

When I run the first query below Oracle doesn’t use the index on column AF and is slow, but when I run the second query the Oracle uses the index and it’s fast. So when the input starts with ‘\\’ the indexes are not used. What’s going on ?

SELECT * FROM T WHERE AF = '\\domain\test\1123.pdf';
SELECT * FROM T WHERE AF = 'a\\domain\test\1123.pdf';

Looking at the two queries my first thought was that it’s obvious what’s (probably) happening, and my second thought was the more interesting question: “why does this person think that the ‘\\’ is significant ?”

The cause of the difference in behaviour is probably related to the way that Oracle stores statistics (specifically histograms) about character columns, and the way in which the cardinality calculations can go wrong.  If two character match over the first few characters the numeric representation of those strings that Oracle uses in a histogram is identical, and if they are long enough even the “actual value” stored would be identical. It looks as if this person is storing URLs, and it’s quite likely that there are a lot of long URLs that start with the same (long) string of characters – it’s a very old problem – and it’s an example of a column where you probably want to be absolutely sure that you don’t gather a histogram.

But why did the OP decide that the ‘\\’ was the significant bit ? I don’t know, of course, but  how about this:

• No contrary tests: Perhaps every single time the query misbehaved the value started with ‘\\’ and it never went wrong for any other starting values. And maybe the OP tested several different domain names – it would be much easier to see the ‘\\’ as the common denominator rather than “repetitive leading character string” if you tested with values that spanned different domains.

combined with

• An easily available “justification”: In many programming languages (including SQL) ‘\’ is an escape character – if you don’t really know much about how the optimizer works you might believe that that could be enough to confuse the optimizer.

It can be very difficult when you spot an obvious pattern to pause long enough to consider whether you’ve identified the whole pattern, or whether you’re looking at a special case that’s going to take you in the wrong direction.

One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support for the declarative syntax makes migration from other database platforms a lot easier.

Under the covers, identity columns are implemented as sequences. This makes a lot of sense – why invent a new piece of functionality when you can exploit something that already has been tried and tested exhaustively for 20 years? So when you create a table with an identity column, you’ll see the appearance of a system named sequence to support it.

Another nice thing about identity columns is that if you drop the table, the underlying sequence that supports the column is also automatically dropped….. (cue ominous music…) or is it? I had this demo come my way via email asking that exact question:

SQL> create table t1 ( x int generated always as identity );

Table created.

SQL> create table t2 ( x int generated by default as identity );

Table created.

SQL> select table_name, sequence_name
  2  from   user_tab_identity_cols;

TABLE_NAME           SEQUENCE_NAME
-------------------- --------------------
T1                   ISEQ$$_139912
T2                   ISEQ$$_139914

6 rows selected.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> select table_name, sequence_name
  2  from   user_tab_identity_cols;

no rows selected

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------
ISEQ$$_139912
ISEQ$$_139914

Is that a bug? Why are the sequences still there?

There is no need to panic – there is a very simple explanation to this. From 10g onwards, by default, when you drop a table we do not actually drop it (ie, release the data and the space it consumed). We simply rename it, and it becomes a member of the recycle bin. This is a nifty insurance policy against losing data inadvertently. The table “disappears” from standard dictionary views but the sequences remain which is what causes the confusion.  We don’t destroy the sequences because recreating them if the table is brought back from the recycle bin would require knowing what the current high watermark for the sequence value would need to be.

Purging the recycle bin brings everything back into alignment.

SQL> purge recyclebin;

Recyclebin purged.

SQL> select sequence_name from user_sequences;

no rows selected

This post is part of a series that discusses some common issues in data warehouses.
There is lots of documentation for Autonomous Data Warehouse Cloud (ADWC), in which I found this bold claim:

•  'Additionally, Autonomous Data Warehouse does not require any tuning. Autonomous Data Warehouse is designed as a ''load and go” service: you start the service, define tables, load data, and then run queries. When you use Autonomous Data Warehouse, no tuning is necessary. You do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries.' - Getting Started with Autonomous Data Warehouse 

I also found these references were helpful to get me going:

• Oracle Cloud Infrastructure Documentation
• Connecting to an Autonomous Data Warehouse 
• Autonomous Data Warehouse
• Loading Data from Files in the Cloud
• Importing Data Using Oracle Data Pump 

The documentation describes how to import data with the DBMS_CLOUD package or with data pump. You have to upload files onto cloud storage and import from there.  For data pump import, the various object types in the exclude parameter recommended in the documentation are a consequence of the 'load and go' approach.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">impdp admin/password@ADWC1_high \       
     directory=data_pump_dir \       
     credential=def_cred_name \       
     dumpfile= https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/adwc/adwc_use... \
     parallel=16 \
     partition_options=merge \ 
     transform=segment_attributes:n \
     transform=dwcs_cvt_iots:y \
     transform=constraint_use_default_index:y \
     exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Clearly, Oracle expects you to periodically incrementally bulk load data and then work with essentially static data.

What is Autonomous Data Warehouse Cloud? 

It is built on Exadata, so it is an engineered system.  This is what I found I was running on:

• 12.2.0.1.0 Single Instance RAC
• SGA: 3.3Gb, PGA: 5Gb, RAM: 708Gb
• Intel(R) Xeon(R) CPU E7-8867 v4 @ 2.40GHz 84 CPUs/Core/Threads
• 12 storage servers

There are two pre-installed locked sample schemas

• The sales history schema SH, that I have been using so far, is a bit different to the one you would build with the scripts on Github. 
• SSB – Star Schema Benchmark.

How is the Oracle delivered Sales History demo schema in ADWC built? 

There are some significant differences with the delivered Sales History schema:

• Primary key constraints are DISABLED, NOVALIDATE, RELY.  There is corresponding no unique index because the constraint was created DISABLED. 
• Thus, there is nothing to prevent you loading duplicate data!
• Foreign key constraints are present, but also DISABLED, NOVALIDATE, RELY.
• Foreign keys are also not indexed, but as they are not enforced there is no risk of TM locking.
• Foreign key join elimination can still happen because QUERY_REWRITE_INTEGRITY=TRUSTED. 

It is clearly not intended that referential integrity is going to be enforced by the database in ADWC. That is not an unreasonable decision in a data warehouse because you expect the data to be validated in the source system.  However, while it is one thing not to enforce referential integrity, I think that not enforcing uniqueness is courageous!  Although, part of the cost of doing so is maintaining a unique index.
I am surprised that the SALES table is not partitioned.  Partition pruning is a very effective way of eliminating data from a query, but perhaps Oracle is relying on the Bloom filtering and the storage indexes to instead.
PCTFREE is set to 0, so that the data is packed into data blocks without leaving room for updates.  This makes good sense.  In a data warehouse, we don't expect to update the data after it has been loaded, or cater also for OLTP behaviour.  This fits with the 'load and go' statement.
The tables have been Hybrid Columnar Compressed (HCC). This also makes sense, because the data is bulk loaded in direct-path mode, and again we don't expect to be updating the data after it has been loaded.  However, Oracle has used QUERY HIGH HCC.  As early as 2016, Roger MacNichol pointed out in Compression in a well-balanced system that “since HCC was released …, DBAs have almost always used Query High” but as CPU speeds have increased “the time has come for a reassessment of role HCC Query Low”.  I have also written about just such a case.

Building My Own Sales History Demo Schema 

I also built my own sales history demo per the standard public scripts. The difference between the two is quite telling. I found that:

• I can build indexes, not just primary keys and foreign keys (though I understand that has only recently become possible).
• I can specify tablespaces and storage 
• I can do some alter session commands but not others due to PDB security profiles.
• I can't ALTER SESSION SET star_transformation_enabled = TRUE;
• However, I can set ALTER SESSION SET optimizer_ignore_hints =FALSE, and then I get star transformation by using the STAR_TRANSFORMATION hint. 
• I can also build B-tree or bitmap secondary indexes. 

 For example, you might choose to index attribute columns on your dimensions.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE UNIQUE INDEX "COUNTRIES_ISO" ON "COUNTRIES" ("COUNTRY_ISO_CODE");

And ADWC will use it.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2437708077

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |      1 |        |       |   427 (100)|       |          |       |     45 |00:00:00.11 |    1651 |       |       |          |
|   1 |  SORT GROUP BY                    |               |      1 |    102 |  8874 |   427   (6)| 00:00:01 |       |       |     45 |00:00:00.11 |    1651 |  6144 |  6144 | 6144  (0)|
|*  2 |   HASH JOIN                       |               |      1 |   4210 |   357K|   426   (6)| 00:00:01 |       |       |  64818 |00:00:00.16 |    1651 |  5443K|  5443K| 5260K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL      | PRODUCTS      |      1 |     26 |   208 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |       7 |  1025K|  1025K|          |
|*  4 |    HASH JOIN                      |               |      1 |  11886 |   916K|   424   (6)| 00:00:01 |       |       |    141K|00:00:00.08 |    1644 |  4266K|  4266K| 4262K (0)|
|   5 |     JOIN FILTER CREATE            | :BF0001       |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |       |       |          |
|   6 |      PART JOIN FILTER CREATE      | :BF0000       |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |       |       |          |
|*  7 |       TABLE ACCESS STORAGE FULL   | TIMES         |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |  1025K|  1025K|          |
|*  8 |     HASH JOIN                     |               |      1 |  48360 |  3164K|   422   (6)| 00:00:01 |       |       |    143K|00:00:00.07 |    1628 |  3376K|  3178K| 4759K (0)|
|   9 |      JOIN FILTER CREATE           | :BF0002       |      1 |   2921 |   128K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.02 |     755 |       |       |          |
|  10 |       NESTED LOOPS                |               |      1 |   2921 |   128K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |     755 |       |       |          |
|  11 |        TABLE ACCESS BY INDEX ROWID| COUNTRIES     |      1 |      1 |    18 |     1   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       2 |       |       |          |
|* 12 |         INDEX UNIQUE SCAN         | COUNTRIES_ISO |      1 |      1 |       |     0   (0)|       |          |       |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|* 13 |        VIEW                       | VW_GBF_25     |      1 |   2921 | 78867 |    20   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |     753 |       |       |          |
|  14 |         TABLE ACCESS STORAGE FULL | CUSTOMERS     |      1 |  55500 |  1138K|    20   (5)| 00:00:01 |       |       |  55500 |00:00:00.01 |     753 |  1025K|  1025K|          |
|  15 |      JOIN FILTER USE              | :BF0001       |      1 |    918K|    19M|   396   (5)| 00:00:01 |       |       |    158K|00:00:00.04 |     873 |       |       |          |
|  16 |       JOIN FILTER USE             | :BF0002       |      1 |    918K|    19M|   396   (5)| 00:00:01 |       |       |    158K|00:00:00.04 |     873 |       |       |          |
|  17 |        PARTITION RANGE JOIN-FILTER|               |      1 |    918K|    19M|   396   (5)| 00:00:01 |:BF0000|:BF0000|    158K|00:00:00.04 |     873 |       |       |          |
|* 18 |         TABLE ACCESS STORAGE FULL | SALES         |      5 |    918K|    19M|   396   (5)| 00:00:01 |:BF0000|:BF0000|    158K|00:00:00.04 |     873 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Whether I should do any of this is another matter!

Delivered non-default parameters

• QUERY_REWRITE_INTEGRITY=TRUSTED: This ties in with making the foreign key constraints RELY, so that foreign key join elimination will still occur.
• RESULT_CACHE_MODE=FORCE enables the result cache by default for all queries.  Whether this is going to be a significant benefit depends upon your application.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">select  /*+ gather_plan_statistics opt_param('result_cache_mode','MANUAL')*/ 
 c.country_name
, u.cust_state_province
,  COUNT(*) num_sales
,  SUM(s.amount_sold) total_amount_sold
from  sales s
,   customers u
,   products p
,  times t
, countries c
WHERE  s.time_id = t.time_id
AND   s.prod_id = p.prod_id
AND   u.cust_id = s.cust_id
AND u.country_id = c.country_id
AND c.country_iso_code = '&&iso_country_code'
AND p.prod_category_id = 205
and  t.fiscal_year = 1999
GROUP BY c.country_name
, u.cust_state_province
ORDER BY 1,2
/

Without the result cache, I get the standard full scan/Bloom filter execution plan.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2719715383

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |      1 |        |       |    38 (100)|          |     45 |00:00:00.16 |     657 |       |       |          |
|   1 |  SORT GROUP BY                   |           |      1 |    102 |  8262 |    38  (48)| 00:00:01 |     45 |00:00:00.16 |     657 |  6144 |  6144 | 6144  (0)|
|*  2 |   HASH JOIN                      |           |      1 |   3478 |   275K|    37  (46)| 00:00:01 |  64818 |00:00:00.03 |     657 |  5443K|  5443K| 5259K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL     | PRODUCTS  |      1 |     26 |   208 |     2   (0)| 00:00:01 |     26 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  4 |    HASH JOIN                     |           |      1 |   9819 |   699K|    35  (49)| 00:00:01 |    141K|00:00:00.18 |     654 |  4266K|  4266K| 4289K (0)|
|   5 |     JOIN FILTER CREATE           | :BF0000   |      1 |    364 |  4368 |     2   (0)| 00:00:01 |    364 |00:00:00.01 |       3 |       |       |          |
|*  6 |      TABLE ACCESS STORAGE FULL   | TIMES     |      1 |    364 |  4368 |     2   (0)| 00:00:01 |    364 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  7 |     HASH JOIN                    |           |      1 |  39950 |  2379K|    33  (52)| 00:00:01 |    141K|00:00:00.18 |     651 |  3411K|  3411K| 4776K (0)|
|   8 |      JOIN FILTER CREATE          | :BF0001   |      1 |   2413 | 94107 |     9  (12)| 00:00:01 |  18520 |00:00:00.01 |     215 |       |       |          |
|*  9 |       HASH JOIN                  |           |      1 |   2413 | 94107 |     9  (12)| 00:00:01 |  18520 |00:00:00.01 |     215 |  2642K|  2642K|  681K (0)|
|  10 |        JOIN FILTER CREATE        | :BF0002   |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |         TABLE ACCESS STORAGE FULL| COUNTRIES |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          |
|  12 |        JOIN FILTER USE           | :BF0002   |      1 |  55500 |  1138K|     7  (15)| 00:00:01 |  18520 |00:00:00.01 |     212 |       |       |          |
|* 13 |         TABLE ACCESS STORAGE FULL| CUSTOMERS |      1 |  55500 |  1138K|     7  (15)| 00:00:01 |  18520 |00:00:00.01 |     212 |  1025K|  1025K|          |
|  14 |      JOIN FILTER USE             | :BF0000   |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |       |       |          |
|  15 |       JOIN FILTER USE            | :BF0001   |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |       |       |          |
|* 16 |        TABLE ACCESS STORAGE FULL | SALES     |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Normally result cache is enabled.  Here the query was not executed because the result was in the cache.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2719715383

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |      1 |        |       |    38 (100)|          |     45 |00:00:00.01 |
|   1 |  RESULT CACHE                     | 8992dgrw00p4p9zu2vmq8p3nwg |      1 |        |       |            |          |     45 |00:00:00.01 |
|   2 |   SORT GROUP BY                   |                            |      0 |    102 |  8262 |    38  (48)| 00:00:01 |      0 |00:00:00.01 |
|   3 |    HASH JOIN                      |                            |      0 |   3478 |   275K|    37  (46)| 00:00:01 |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS STORAGE FULL     | PRODUCTS                   |      0 |     26 |   208 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   5 |     HASH JOIN                     |                            |      0 |   9819 |   699K|    35  (49)| 00:00:01 |      0 |00:00:00.01 |
|   6 |      JOIN FILTER CREATE           | :BF0000                    |      0 |    364 |  4368 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   7 |       TABLE ACCESS STORAGE FULL   | TIMES                      |      0 |    364 |  4368 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   8 |      HASH JOIN                    |                            |      0 |  39950 |  2379K|    33  (52)| 00:00:01 |      0 |00:00:00.01 |
|   9 |       JOIN FILTER CREATE          | :BF0001                    |      0 |   2413 | 94107 |     9  (12)| 00:00:01 |      0 |00:00:00.01 |
|  10 |        HASH JOIN                  |                            |      0 |   2413 | 94107 |     9  (12)| 00:00:01 |      0 |00:00:00.01 |
|  11 |         JOIN FILTER CREATE        | :BF0002                    |      0 |      1 |    18 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|  12 |          TABLE ACCESS STORAGE FULL| COUNTRIES                  |      0 |      1 |    18 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|  13 |         JOIN FILTER USE           | :BF0002                    |      0 |  55500 |  1138K|     7  (15)| 00:00:01 |      0 |00:00:00.01 |
|  14 |          TABLE ACCESS STORAGE FULL| CUSTOMERS                  |      0 |  55500 |  1138K|     7  (15)| 00:00:01 |      0 |00:00:00.01 |
|  15 |       JOIN FILTER USE             | :BF0000                    |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
|  16 |        JOIN FILTER USE            | :BF0001                    |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
|  17 |         TABLE ACCESS STORAGE FULL | SALES                      |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------------------------

#8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-layout-alt: fixed; mso-yfti-tbllook: 1056;">

#4472C4; border-right: none; border: solid #4472C4 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-left-alt: solid #4472C4 .5pt; mso-border-left-themecolor: accent1; mso-border-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt; width: 140pt;" valign="top"> Parameter	#4472C4; border-bottom: solid #4472C4 1.0pt; border-left: none; border-right: none; border-top: solid #4472C4 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt; width: 40pt;" valign="top"> Value	#4472C4; border-left: none; border: solid #4472C4 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-right-alt: solid #4472C4 .5pt; mso-border-right-themecolor: accent1; mso-border-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Comment
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> _default_pct_free	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> 1	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Sets the default value for PCT_FREE to 1% (defaults to 10%) in order to pack data in blocks.
#8EAADB 1.0pt; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> _optimizer_gather_stats_on_load_all	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> TRUE	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Statistics gathered on-line during direct-path insert into a table that is not empty.  Otherwise, this only happens on initial load or during a create-table-as-select command.
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> _optimizer_gather_stats_on_load_hist	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> TRUE	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Histograms also gathered on-line during direct-path load.
#8EAADB 1.0pt; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> optimizer_ignore_hints	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> TRUE	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Ignore hints embedded in SQL.
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> optimizer_ignore_parallel_hints	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> TRUE	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Ignore embedded parallel hints.
#8EAADB 1.0pt; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> result_cache_max_size	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> 100M	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Maximum size of result cache.
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> result_cache_max_result	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> 1	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Maximum percentage of result cache that one result can use (defaults to 5%).
#8EAADB 1.0pt; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Inmemory_size	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> 1G	#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> Size of In-memory Column Store, so this feature is enabled.
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> _cell_offload_vector_groupby	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> FALSE	#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; height: 2.85pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top"> In-Memory Aggregation optimisation is disabled (see related white paper).

Other useful references:

• Frank Pachot ADWC – System and session settings (DWCS lockdown profile)
• Dani Schnider: Gathering Statistics in the Autonomous Data Warehouse Cloud
• Frank Pachot Result Cache: when *not* to use it

Summary

Everything that I have discussed in this series applies to ADWC just as it does to other data warehouses.

• Your data warehouse should certainly use a strict star schema data model such that you can define foreign and either primary or unique keys in the database.  
• You should only have equality joins between dimension and fact tables.
• Wherever possible these should be single column joins and therefore single column foreign and primary keys.  You can consider multi-column keys from Oracle 12.2, however, there are bugs.
• Whether the database enforces the foreign key constraints is a matter of choice, but you need to define them in order to achieve join elimination.

ADWC is built on Engineered systems

• So full scan/Bloom filter is going to be able to take advantage of Engineer system optimisations.
• Bloom Filters are pushed to storage server during smart scan
• A Bloom Filter on one table can be used against the storage index on another table to skip I/O.
• Hybrid Columnar Compression further assists smart scans.
• I would not like to work without enforced primary keys, I think that is courageous!
• If the database is not going to enforce referential integrity, then foreign key should be made RELY.  As QUERY_REWRITE_INTEGRITY=TRUSTED you will still get foreign key join elimination.
• You are expected to load data by periodically incrementally bulk loading it.
• Optimiser statistics are maintained automatically in direct path insert even after the initial insert.  The regular statistics maintenance window is disabled.  If you are going to further transform of data after the initial load, then you will need to think about whether and when to collect statistics.
• RESULT_CACHE_MODE=FORCE, so the result cache is used by default across the board, with a maximum result size to keep things reasonable.
• This raises the question whether this should be considered for non-autonomous engineered system data warehouses?
• However, used incorrectly, the result cache can cause contention problems.

Will Anything run on ADWC?

At the time of writing Oracle’s Cloud Infrastructure as a Service (IaaS) offers 2 ways to connect block storage to virtual machines: paravirtualised and via iSCSI. There are important differences between the two so please read the documentation to understand all the implications. I need all the performance I can get with my systems so I’m going with iSCSI.

It’s the little differences

Using the paravirtualised driver couldn’t be easier: you boot the VM, and all block devices are automatically attached and available. When using iSCSI you need to run a few iscsiadm commands (once) to discover and mount the remote storage. These commands are available on the click of a button in the GUI. It’s been ages that I used the GUI and I prefer a scripted approach to cloud infrastructure. My tool of choice when it comes to “infrastructure as code” is terraform. 

Until fairly recently I have made use of the null provider combined with a remote-exec provisioner in my terraform scripts. The combination allows me to execute the iscsiadm commands necessary to attach the iSCSI devices to the VM. A number of enhancements in this space allowed me to ditch the rather cumbersome remote-exec step and use cloud-init combined with OCI utilities instead. As I hope to show you, using these two combined make the management of iSCSI device just as simple as the paravirtualised ones.

Cloud Init

When creating VMs I often need to perform a few extra steps that don’t quite justify the creation of a custom image. The cloud-init toolkit in OCI allows me to pass a shell script as “user_data” to the instance’s metadata, provided it’s encoded in base64. Have a look at the documentation I just referenced for more details about restrictions etc. In my terraform script, I use something like this:

resource "oci_core_instance" "docker_tf_instance" {
[...]
   
    metadata {
        ssh_authorized_keys = "${var.ssh_public_key}"
        user_data = "${base64encode(file("bootstrap.sh"))}"
    }

[...]
}

Most examples I found specify the input to the file() function as a variable, I didn’t do this in this post for the sake of simplicity. The script I’m passing as user_data makes use of the OCI utilities.

OCI Utilities

I wasn’t aware of these until my colleague Syed asked me why I didn’t use them. It couldn’t be easier: just install a RPM package and start a service. This will take care of the iSCSI part for you. The only caveat is that currently they can only be used for Oracle provided images based on Oracle Linux. Here is a really basic example of a shell script calling the OCI utilities:

$ cat bootstrap.sh 
#!/bin/bash

cp /etc/motd /etc/motd.bkp
cat << EOF > /etc/motd

I have been modified by cloud-init at $(date)

EOF

yum install -y python-oci-cli
systemctl enable ocid.service
systemctl start ocid.service
systemctl status ocid.service

The first line has to start with #!/bin/bash to indicate to cloud-init that you want to run a shell script. Following the instructions for using OCI utilities, I am installing the python-oci-cli and start the ocid.service. This in turn will perform the iSCSI volume attachment for me – super nice! After my terraform script completed, I can log in to see if this worked:

[root@docker-tf-instance ~]# lsblk
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sdb      8:16   0   50G  0 disk 
sda      8:0    0 46.6G  0 disk 
├─sda2   8:2    0    8G  0 part [SWAP]
├─sda3   8:3    0 38.4G  0 part /
└─sda1   8:1    0  200M  0 part /boot/efi
[root@docker-tf-instance ~]# 

[root@docker-tf-instance ~]# systemctl status ocid.service
● ocid.service - Oracle Cloud Infrastructure utilities daemon
   Loaded: loaded (/etc/systemd/system/ocid.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2018-11-27 19:52:45 GMT; 19min ago
 Main PID: 15138 (python2.7)
   CGroup: /system.slice/ocid.service
           └─15138 python2.7 /usr/libexec/ocid

Nov 27 19:52:04 docker-tf-instance python2.7[15138]: ocid - INFO - Starting ocid thread 'iscsi'
Nov 27 19:52:04 docker-tf-instance python2.7[15138]: ocid - INFO - Starting ocid thread 'vnic'
...
Nov 27 19:52:09 docker-tf-instance python2.7[15138]: ocid - INFO - secondary VNIC script reports: Info: no changes, IP configuration is up-to-date
Nov 27 19:52:44 docker-tf-instance python2.7[15138]: ocid - INFO - Attaching iscsi device: 169.254.a.b:3260 (iqn.2015-12.com.oracleiaas:e1af1...)
Nov 27 19:52:45 docker-tf-instance systemd[1]: Started Oracle Cloud Infrastructure utilities daemon.

You can see cloud-init in action by checking /var/log/messages for occurrences of “cloud-init”. The file /var/log/cloud-init.log doesn’t contain information relevant to the “user-data” processed by the way. If you want to see how your script arrived on the VM, check /var/lib/cloud/instance/user-data.txt.

Summary

It would seem you can have the cake and eat it. Using cloud-init for bootstrapping my VM and OCI utilities to attach my block devices I don’t need to write any remote-exec hacks using the null provider and use the iSCSI volumes with the same ease of use as the paravirtualised ones. Without having to make compromises. I like it!