Oakies Blog Aggregator

davidkurtz's picture

A Brief Look Inside Oracle's Autonomous Data Warehouse Cloud

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:

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

    There are a number of database initialisation parameters set to non-default values that are noteworthy:
    • 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:

    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 end of the day, ADWC is an Oracle database on an engineered system, although it has been set up and configured in a very particular way.  It is possible to alter ADWC settings to get traditional Star Transformation behaviour and do all the things that you usually do to implement a data warehouse.  However, as I have demonstrated earlier in this series, you probably wouldn't want to do that.
    If your legacy data warehouse doesn't follow the principles of good practice, including but not limited to those set out above, then you probably should not be trying to force it into ADWC.  Other platforms, where you have more discretion, are available.

      Jonathan Lewis's picture

      Index rebuild bug

      I tweeted a reference yesterday to a 9 year old article about index rebuilds, and this led me on to look for an item that I thought I’d written on a related topic. I hadn’t written it (so there’s another item on my todo list) but I did discover a draft I’d written a few years ago about an unpleasant side effect relating to rebuilding subpartitions of local indexes on composite partitoned tables. It’s probably the case that no-one will notice they’re suffering from it because it’s a bit of an edge case – but you might want to review the things your system does.

      Here’s the scenario: you have a large table that is composite partitioned with roughly 180 daily partitions and 512 subpartitions (per partition). For some strange reason you have a couple of local indexes on the table that have been declared unusable – hoping, perhaps, that no-one ever does anything that makes Oracle decide to rebuild all the unusable bits.

      One day you decide to rebuild just one subpartition of one of the indexes that isn’t marked as unusable. You might be planning to rebuild every single subpartition of that index overnight, but you’re going to start with just one to see how long it takes. Something very strange happens – and here’s a simple model to demonstrate:

      rem
      rem     Script:         index_rebuild_pt_bug.sql
      rem     Author:         Jonathan Lewis
      rem     Dated:          Feb 2015
      rem
      rem     Last tested
      rem             18.3.0.0
      rem             12.1.0.1
      rem             11.2.0.4
      rem
      
      create table interval_hash (
              n1 number,
              n2 number,
              n3 number
      )
      segment creation immediate
      partition by range(n1) interval (1)
      subpartition by hash (n2) subpartitions 16 (
              partition p1 values less than (2) 
      )
      ;
      
      
      begin
              for i in 1 .. 16 loop
                      for j in 1..64 loop
                              insert into interval_hash i(n1, n2, n3) values (i, j, j + 64*(i-i));
                      end loop;
              end loop;
      
              commit;
      end;
      /
      
      create index ih_i1 on interval_hash(n1) local;
      create index ih_i2 on interval_hash(n2) local;
      create index ih_i3 on interval_hash(n3) local;
      
      alter index ih_i1 unusable;
      alter index ih_i2 unusable;
      
      

      The code creates a table which extends as data arrives to have 16 partitions with 16 subpartitions each – for a total of 256 data segments. After loading the data I’ve created 3 local indexes on the table and made two of them unusable.

      After setting up the table and indexes I’ve identified one subpartition of the table by name, enabled tracing, and rebuilt the corresponding subpartition of the index which is currently usable (the same effect appears if I rebuild a partition of one of the unusable indexes, but the phenomenon is slightly more surprising if you rebuild a usable subpartition). Here’s the code for the rebuild:

      
      column max_subp new_value m_subp
      
      select
              max(partition_name) max_subp
      from
              user_segments
      where
              segment_name = 'INTERVAL_HASH'
      ;
      
      alter session set events '10046 trace name context forever, level 4';
      
      alter index ih_i3 rebuild subpartition &m_subp;
      
      alter session set events '10046 trace name context off';
      
      

      Would you expect to find anything interesting in the trace file after running it through tkprof ? Here’s the most frequently executed SQL statement I found when running this test on an instance of Oracle 18.3:

      
      tkprof or18_ora_24939.trc temp sort=execnt
      
      SQL ID: 0yn07bvqs30qj Plan Hash: 866645418
      
      select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg,
        maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg,
        maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg,
         cmpflag_stg, cmplvl_stg,imcflag_stg, ccflag_stg, flags2_stg
      from
       deferred_stg$  where obj# =:1
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          0          0           0
      Execute    512      0.02       0.03          0          0          0           0
      Fetch      512      0.00       0.00          0       1536          0         512
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total     1025      0.03       0.03          0       1536          0         512
      
      

      This query runs once for every single subpartition of the two unusable indexes. (There’s another statement that runs once for every partition of the two unusable indexes to provide the object numbers of the subpartitions and that shouldn’t be forgotten). In my example the impact and time to run doesn’t look too bad – but when the numbers climb to a couple of hundred thousand executions before you start to rebuild the first subpartition you might start to worry. Depending on the state of your data dictionary, and how you got to the point where you had so many unusable segments, the time to execute could become large, and you might do most of it all over again for the next subpartition!

      You might wonder why anyone would have a couple of unusable indexes. First, many years ago (in Practival Oracle 8i) I pointed out that if you wanted to create a new locally partitioned index you might want to create it unusable and then rebuild each partition in turn – that might not be a good idea any more. (The book also pointed out the requirement to think about sizing the dictionary cache (rowcache)).

      Secondly, before the introduction of partial indexing it was possible to emulate the feature manually for local indexes by setting partitions and subpartition unusable and allowing the optimizer to use table expansion to pick the best plan for partitions that had different index partitions still usable.

      Finally if you are using the new partial indexing feature of 12.2 where you can set the default characteristic of a partitioned table to “indexing off”, and the default characteristic of an index to “indexing partial”, the partitions of any local index that are not created are deemed to be deferred – but you won’t see the effect in my example unless you modify it to include partial indexes and include an “alter system flush shared pool” just before the rebuild.

      Footnote

      I’ve found this bug (or something very similar) on MoS: Bug 17335646 : ALTER INDEX IDX REBUILD SUBPARTITION SP VISITS EVERY INDEX AND SUBPARTION. However the bug was recorded against 11.2.0.3 and its status is: “31 – Could Not Reproduce. To Filer”. It does seem to be terribly easy to reproduce, though, provided you have a large number of unusable subpartitions in your indexes – so it’s possible the original bug appeared even when there were no unusable subpartitions (the customer comments about the bug don’t give any suggestion that there might be unusable indexes in place – and it seems unlikely that the 22 indexes mentioned were all unusable).

       

       

       

       

       

      martin.bach's picture

      Bootstrapping a VM image in Oracle Cloud Infrastructure using cloud-init

      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!

      davidkurtz's picture

      Data Warehouse Design: Engineered Systems Considerations

      This post is part of a series that discusses some common issues in data warehouses.

      On an engineered system, a key feature is that Bloom filters are pushed to storage cells during smart scan,  Additionally, a Bloom filter computed from a join column of a one table can be used against another table.  Storage index can skip I/O against the large fact table based on a Bloom filter calculated from a small dimension table (see Tanel Poder's Blog: Combining Bloom Filter Offloading and Storage Indexes on Exadata)
      This shifts the balance away from Star Transformation, so you are far less likely to want to add bitmap indexes.
      I repeated the same test from my previous blog post (US by State for sales in 1999), on exactly the same data, on Oracle 12.1 on Exadata. The cost of the Star Transformation is lower at 575, because db_file_multi_block_read_count=128, and this makes sense on an Exadata because you can do 1Mb reads from disk.

      #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: 472227253

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | | | 575 (100)| | | | 45 |00:00:00.37 | 98059 | 98 | 98 | | | |
      | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 45 |00:00:00.37 | 98059 | 98 | 98 | | | |
      | 2 | LOAD AS SELECT | | 1 | | | | | | | 0 |00:00:00.03 | 1647 | 0 | 98 | 1040K| 1040K| |
      | 3 | HASH JOIN | | 1 | 2921 | 111K| 23 (5)| 00:00:01 | | | 18520 |00:00:00.02 | 1546 | 0 | 0 | 2408K| 2408K| 281K (0)|
      | 4 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | | | |
      | 5 | TABLE ACCESS STORAGE FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 0 | 0 | 1025K| 1025K| |
      | 6 | JOIN FILTER USE | :BF0000 | 1 | 55500 | 1138K| 21 (5)| 00:00:01 | | | 18520 |00:00:00.01 | 1537 | 0 | 0 | | | |
      | 7 | TABLE ACCESS STORAGE FULL | CUSTOMERS | 1 | 55500 | 1138K| 21 (5)| 00:00:01 | | | 18520 |00:00:00.01 | 1537 | 0 | 0 | 1025K| 1025K| |
      | 8 | SORT GROUP BY | | 1 | 2344 | 100K| 551 (1)| 00:00:01 | | | 45 |00:00:00.35 | 96406 | 98 | 0 | 6144 | 6144 | 6144 (0)|
      | 9 | HASH JOIN | | 1 | 2344 | 100K| 550 (1)| 00:00:01 | | | 64818 |00:00:00.32 | 96406 | 98 | 0 | 5827K| 5180K| 7295K (0)|
      | 10 | VIEW | VW_ST_C525CEF3 | 1 | 2344 | 42192 | 548 (1)| 00:00:01 | | | 64818 |00:00:00.29 | 96300 | 98 | 0 | | | |
      | 11 | NESTED LOOPS | | 1 | 2344 | 116K| 542 (1)| 00:00:01 | | | 64818 |00:00:00.28 | 96300 | 98 | 0 | | | |
      | 12 | PARTITION RANGE SUBQUERY | | 1 | 2344 | 67986 | 313 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| 64818 |00:00:00.21 | 96041 | 98 | 0 | | | |
      | 13 | BITMAP CONVERSION TO ROWIDS | | 5 | 2344 | 67986 | 313 (1)| 00:00:01 | | | 64818 |00:00:00.20 | 95974 | 98 | 0 | | | |
      | 14 | BITMAP AND | | 5 | | | | | | | 5 |00:00:00.19 | 95974 | 98 | 0 | | | |
      | 15 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.01 | 1921 | 0 | 0 | 1024K| 512K|39936 (0)|
      | 16 | BITMAP KEY ITERATION | | 5 | | | | | | | 365 |00:00:00.01 | 1921 | 0 | 0 | | | |
      | 17 | BUFFER SORT | | 5 | | | | | | | 1820 |00:00:00.01 | 65 | 0 | 0 | 73728 | 73728 | |
      | 18 | TABLE ACCESS STORAGE FULL| TIMES | 1 | 364 | 4368 | 2 (0)| 00:00:01 | | | 364 |00:00:00.01 | 65 | 0 | 0 | 1025K| 1025K| |
      | 19 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | 1820 | | | | |KEY(SQ)|KEY(SQ)| 365 |00:00:00.01 | 1856 | 0 | 0 | 1025K| 1025K| |
      | 20 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.01 | 161 | 0 | 0 | 1024K| 512K|10240 (0)|
      | 21 | BITMAP KEY ITERATION | | 5 | | | | | | | 132 |00:00:00.01 | 161 | 0 | 0 | | | |
      | 22 | BUFFER SORT | | 5 | | | | | | | 130 |00:00:00.01 | 10 | 0 | 0 | 73728 | 73728 | |
      | 23 | TABLE ACCESS STORAGE FULL| PRODUCTS | 1 | 14 | 112 | 2 (0)| 00:00:01 | | | 26 |00:00:00.01 | 10 | 0 | 0 | 1025K| 1025K| |
      | 24 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | 130 | | | | |KEY(SQ)|KEY(SQ)| 132 |00:00:00.01 | 151 | 0 | 0 | 1025K| 1025K| |
      | 25 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.19 | 93892 | 98 | 0 | 1024K| 512K| 310K (0)|
      | 26 | BITMAP KEY ITERATION | | 5 | | | | | | | 6504 |00:00:00.18 | 93892 | 98 | 0 | | | |
      | 27 | BUFFER SORT | | 5 | | | | | | | 92600 |00:00:00.03 | 106 | 98 | 0 | 28M| 2978K| 928K (0)|
      | 28 | TABLE ACCESS STORAGE FULL| SYS_TEMP_0FD9D66B4_75DFDD27 | 1 | 2921 | 14605 | 2 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 106 | 98 | 0 | 1025K| 1025K| |
      | 29 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | 92600 | | | | |KEY(SQ)|KEY(SQ)| 6504 |00:00:00.13 | 93786 | 0 | 0 | 1025K| 1025K| |
      | 30 | TABLE ACCESS BY USER ROWID | SALES | 64818 | 1 | 22 | 235 (0)| 00:00:01 | ROWID | ROWID | 64818 |00:00:00.05 | 259 | 0 | 0 | | | |
      | 31 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D66B4_75DFDD27 | 1 | 2921 | 75946 | 2 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 106 | 0 | 0 | 1025K| 1025K| |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      The cost of the full scan plan is just 66.  In fact, I could only get the star transformation with an explicit hint to force it (so, the cost-based star transformation is working as expected).

      #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: 874291156

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | 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 | | | 66 (100)| | | | 45 |00:00:00.14 | 2173 | | | |
      | 1 | SORT GROUP BY | | 1 | 103 | 8343 | 66 (25)| 00:00:01 | | | 45 |00:00:00.14 | 2173 | 6144 | 6144 | 6144 (0)|
      | 2 | HASH JOIN | | 1 | 2411 | 190K| 65 (24)| 00:00:01 | | | 64818 |00:00:00.11 | 2173 | 4038K| 4038K| 1436K (0)|
      | 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 364 | 4368 | 2 (0)| 00:00:01 | | | 364 |00:00:00.01 | 65 | | | |
      | 4 | TABLE ACCESS STORAGE FULL | TIMES | 1 | 364 | 4368 | 2 (0)| 00:00:01 | | | 364 |00:00:00.01 | 65 | 1025K| 1025K| |
      | 5 | HASH JOIN | | 1 | 9672 | 651K| 63 (24)| 00:00:01 | | | 79240 |00:00:00.10 | 2108 | 5219K| 5219K| 1098K (0)|
      | 6 | JOIN FILTER CREATE | :BF0001 | 1 | 14 | 112 | 2 (0)| 00:00:01 | | | 26 |00:00:00.01 | 10 | | | |
      | 7 | TABLE ACCESS STORAGE FULL | PRODUCTS | 1 | 14 | 112 | 2 (0)| 00:00:01 | | | 26 |00:00:00.01 | 10 | 1025K| 1025K| |
      | 8 | HASH JOIN | | 1 | 48360 | 2880K| 60 (24)| 00:00:01 | | | 79240 |00:00:00.09 | 2098 | 3241K| 3241K| 1869K (0)|
      | 9 | JOIN FILTER CREATE | :BF0002 | 1 | 2921 | 111K| 23 (5)| 00:00:01 | | | 18520 |00:00:00.02 | 1546 | | | |
      | 10 | HASH JOIN | | 1 | 2921 | 111K| 23 (5)| 00:00:01 | | | 18520 |00:00:00.01 | 1546 | 2507K| 2507K| 234K (0)|
      | 11 | JOIN FILTER CREATE | :BF0003 | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | | | |
      | 12 | TABLE ACCESS STORAGE FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 9 | 1025K| 1025K| |
      | 13 | JOIN FILTER USE | :BF0003 | 1 | 55500 | 1138K| 21 (5)| 00:00:01 | | | 18520 |00:00:00.01 | 1537 | | | |
      | 14 | TABLE ACCESS STORAGE FULL | CUSTOMERS | 1 | 55500 | 1138K| 21 (5)| 00:00:01 | | | 18520 |00:00:00.01 | 1537 | 1025K| 1025K| |
      | 15 | JOIN FILTER USE | :BF0001 | 1 | 918K| 19M| 34 (30)| 00:00:01 | | | 87209 |00:00:00.06 | 552 | | | |
      | 16 | JOIN FILTER USE | :BF0002 | 1 | 918K| 19M| 34 (30)| 00:00:01 | | | 87209 |00:00:00.06 | 552 | | | |
      | 17 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 19M| 34 (30)| 00:00:01 |:BF0000|:BF0000| 87209 |00:00:00.06 | 552 | | | |
      | 18 | TABLE ACCESS STORAGE FULL | SALES | 5 | 918K| 19M| 34 (30)| 00:00:01 |:BF0000|:BF0000| 87209 |00:00:00.06 | 552 | 1025K| 1025K| |
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      The balance point between star transformation and full scan Bloom filter is exactly the same, but they all perform about 4x faster.

      #4472C4; border-right: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      ISO Country Code
      #4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Country Name
      #4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      Number of Sales in 1999
      #4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      Number of Sales
      #4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.04%;" valign="top" width="20%">
      Star Transformation
      #4472C4; border-left: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-right-alt: solid white .5pt; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 21.46%;" valign="top" width="21%">
      Full Scan-Bloom Filter
      #B4C6E7; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      A-Time
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      Buffers
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      A-Time
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      Buffers
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      US
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      United States of America
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      2662
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      526212
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .45
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      98056
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .13
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      DE
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Germany
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      561
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      81978
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .14
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      45538
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .07
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      JP
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Japan
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      281
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      60183
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .19
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      7118
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .29
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2068
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      GB
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      United Kingdom
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      391
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      58638
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .41
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      4238
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .25
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2068
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      IT
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Italy
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      257
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      42570
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .12
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      43473
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .07
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      AU
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Australia
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      228
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      33685
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .04
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      8186
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      FR
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      France
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      161
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      33078
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .07
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      23434
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .07
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      SG
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Singapore
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      80
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      25253
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .04
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      6984
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      CA
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Canada
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      90
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      22858
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .05
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      14156
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      ES
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Spain
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      85
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      17136
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .05
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      14319
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      DK
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Denmark
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      89
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      16651
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .03
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      5195
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      AR
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Argentina
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      3
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      202
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .02
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      5975
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      BR
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Brazil
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      9
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      180
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .03
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      7957
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .05
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      TR
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Turkey
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      1
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      168
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .02
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      4200
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .05
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      CN
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      China
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      4
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      19
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .03
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      7342
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      PL
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Poland
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      2
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      18
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .03
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      7342
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .06
      #B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173
      #4472C4; border-top: none; border: solid white 1.0pt; height: 8.5pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.4%;" valign="top" width="11%">
      SA
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.6%;" valign="top" width="22%">
      Saudi Arabia
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.76%;" valign="top" width="11%">
      0
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.74%;" valign="top" width="12%">
      7
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.52%;" valign="top" width="9%">
      .02
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.52%;" valign="top" width="10%">
      4093
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.2%;" valign="top" width="10%">
      .05
      #D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.26%;" valign="top" width="11%">
      2173

       A full-scan is far more likely to deliver acceptable performance on an Engineered system, so why would you go to the trouble of bitmap indexing to achieve star transformation?  You probably wouldn't.

      connor_mc_d's picture

      The phantom tablespace

      (Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

      OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick Smile

      I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming Smile) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.

      
      SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') x from dual
      
      X
      -------------------------------------------------------------------------------------------------
      CREATE TABLE "SCOTT"."T"
       (    "X" NUMBER(*,0)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
      STORAGE (  
        BUFFER_POOL DEFAULT 
        FLASH_CACHE DEFAULT 
        CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "DEMO"
      PARTITION BY LIST ("X")
      (PARTITION "P1"  VALUES (1) 
         SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
         STORAGE (
           INITIAL 8388608 
           NEXT 1048576 
           MINEXTENTS 1 
           MAXEXTENTS 2147483645  
           PCTINCREASE 0 
           FREELISTS 1 
           FREELIST GROUPS 1
           BUFFER_POOL DEFAULT 
           FLASH_CACHE DEFAULT 
           CELL_FLASH_CACHE DEFAULT)
         TABLESPACE "USERS" ,
       PARTITION "P2"  VALUES (2) 
         SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
         STORAGE (
           INITIAL 8388608 
           NEXT 1048576 
           MINEXTENTS 1 
           MAXEXTENTS 2147483645  
           PCTINCREASE 0 
           FREELISTS 1 
           FREELIST GROUPS 1
           BUFFER_POOL DEFAULT 
           FLASH_CACHE DEFAULT 
           CELL_FLASH_CACHE DEFAULT)
         TABLESPACE "LARGETS" )
      

      With a little colour coding, you can see that there are three tablespaces that pertain to this table:

      • DEMO
      • USERS
      • LARGETS

      But look what happens when I query the data dictionary for those tablespaces:

      
      SQL> select tablespace_name
        2  from   dba_tablespaces
        3  where  tablespace_name in ('DEMO','USERS','LARGETS');
      
      TABLESPACE_NAME
      ------------------------
      LARGETS
      USERS
      

      Where is DEMO? Where has it gone? More startlingly, how can I have an existing table that needs that tablespace, and yet the tablespace is not present in the database? Have I lost data? Is there corruption?

      Fortunately, the answer to all of these questions do not involve data loss and/or corruption. It is a quirk of the syntax that can be used for partitioned tables. Here is the DDL as I wrote it for the table T.

      
      SQL> create table t ( x int ) tablespace demo
        2  partition by list ( x )
        3  ( partition p1 values (1) tablespace users,
        4    partition p2 values (2) tablespace largets
        5  );
      
      Table created.
      

      And immediately after I created the table, I did the following

      
      SQL> drop tablespace demo including contents and datafiles;
      
      Tablespace dropped.
      

      You might be thinking that such an operation would surely drop the table I just created as well, but it is still here just fine.

      
      SQL> desc t
       Name                                                              Null?    Type
       ----------------------------------------------------------------- -------- ---------------
       X                                                                          NUMBER(38)
      

      The specification of the tablespace at table level for a partitioned table is nominating the default tablespace for each partition in case it is not specified at partition level. Because I specified a tablespace explicitly for the two partitions on T, the tablespace DEMO does not contain any data, or any partitions for that matter. Which is why I was able to drop it without any problems. Compounding the confusion that often arises is that you won’t see the tablespace name DEMO listed in the USER_TABLES dictionary view even if I had not dropped the tablespace.

      
      SQL> select tablespace_name from user_tables
        2  where table_name = 'T';
      
      TABLESPACE_NAME
      ------------------------
      (null)
      

      The tablespace name in USER_TABLES nominates the tablespace for the segment that will be associated with this table. You will see a similar null value in this column when the table is an Index Organized Table, because it is the underlying index that maps to a tablespace, not the table definition. For a partitioned table, to see which tablespace is the default tablespace, you need to query the USER_PART_TABLES dictionary view.

      
      SQL> select def_tablespace_name
        2  from user_part_tables
        3  where table_name = 'T';
      
      DEF_TABLESPACE_NAME
      ------------------------------
      DEMO
      

      Besides this being some trickery with tablespace definitions, there is a good reason why you should know about the segments tablespace and the default tablespace for partitioned objects. As it stands, I could run a datapump export command on the table T and it will successfully be unloaded to a data pump file. However, if I attempt to run a data pump import, the creation of the table will fail, because of the (now illegal) reference to the DEMO tablespace.

      So if you are planning to run a data pump export, here’s a query I whipped up to run a check against your database to ensure that you don’t have any references to tablespaces that no longer exist in your database.

      
      SQL> with all_possible_ts as
        2  (
        3  select tablespace_name from dba_lobs                       union all
        4  select tablespace_name from dba_clusters                   union all
        5  select tablespace_name from dba_indexes                    union all
        6  select tablespace_name from dba_rollback_segs              union all
        7  select tablespace_name from dba_tables                     union all
        8  select tablespace_name from dba_object_tables              union all
        9  select def_tablespace_name from dba_part_tables            union all
       10  select def_tablespace_name from dba_part_indexes           union all
       11  select tablespace_name from dba_tab_partitions             union all
       12  select tablespace_name from dba_ind_partitions             union all
       13  select tablespace_name from dba_tab_subpartitions          union all
       14  select tablespace_name from dba_ind_subpartitions          union all
       15  select def_tablespace_name from dba_part_lobs              union all
       16  select tablespace_name from dba_lob_partitions             union all
       17  select tablespace_name from dba_lob_subpartitions          union all
       18  select tablespace_name from dba_subpartition_templates     union all
       19  select tablespace_name from dba_lob_templates              union all
       20  select tablespace_name from dba_segments                   union all
       21  select tablespace_name from dba_extents                    union all
       22  select tablespace_name from dba_undo_extents
       23  )
       24  select tablespace_name from all_possible_ts
       25  minus
       26  select tablespace_name from dba_tablespaces;
      
      TABLESPACE_NAME
      --------------------
      DEMO
      
      Jonathan Lewis's picture

      Counting Rows

      Here’s another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn’t do anything clever, just call routines in the dbms_rowid package for each rowid in the table:

      
      rem
      rem     Rowid_count.sql
      rem     Generic code to count rows per block in a table
      rem     Ordered by file and block
      rem
      
      define m_table = '&1'
      
      spool rowid_count
      
      select 
              dbms_rowid.rowid_relative_fno(rowid)    rel_file_no, 
              dbms_rowid.rowid_block_number(rowid)    block_no,
              count(*)                                rows_starting_in_block
      from 
              &m_table        t1
      group by 
              dbms_rowid.rowid_relative_fno(rowid), 
              dbms_rowid.rowid_block_number(rowid) 
      order by 
              dbms_rowid.rowid_relative_fno(rowid), 
              dbms_rowid.rowid_block_number(rowid)
      ;
      
      
      select
              rows_starting_in_block,
              count(*)        blocks
      from
              (
              select 
                      dbms_rowid.rowid_relative_fno(rowid), 
                      dbms_rowid.rowid_block_number(rowid),
                      count(*)                                rows_starting_in_block
              from 
                      &m_table        t1
              group by 
                      dbms_rowid.rowid_relative_fno(rowid), 
                      dbms_rowid.rowid_block_number(rowid) 
              )
      group by
              rows_starting_in_block
      order by
              rows_starting_in_block
      ;
      
      spool off
      
      
      

      And here’s a sample of the output:

      
      REL_FILE_NO   BLOCK_NO ROWS_STARTING_IN_BLOCK
      ----------- ---------- ----------------------
      	 22	   131			  199
      	 22	   132			  199
      	 22	   133			  199
      	 22	   134			  199
      	 22	   135			   88
      	 22	   138			  111
      
      6 rows selected.
      
      
      ROWS_STARTING_IN_BLOCK	   BLOCKS
      ---------------------- ----------
      		    88		1
      		   111		1
      		   199		4
      
      3 rows selected.
      
      
      

      Obviously it could take quite a lot of I/O and CPU to run the two queries against a large table – generally I use it when I want to pick a block to dump afterwards.

      martin.bach's picture

      Enhanced “validate” commands in Oracle’s Data Guard Broker 18c

      If you are using an Oracle Database Enterprise Edition chances are that there is at least one environment in your estate making use of Data Guard. And if you are using Data Guard, why not use the broker? I have been using Data Guard broker for a long time now, and it has definitely improved a lot over the first releases, back in the day. I like it so much these days that I feel hard done by if I can’t make use of it. This is of course a matter of personal preference, and I might be exaggerating a little :)

      One of the nice additions to the broker in Oracle 12.1 was the ability to validate a database before a role change. This is documented in the Data Guard broker documentation. I certainly don’t solely rely on the output of the command, I have my own checks I’m running that go over and above what a validate can do.

      During research I ran the command on my 12.2 system and noticed much enhanced output. This is super exciting and worth blogging about. Well, that is, to me at least-I hope you find this post useful. I love discovering little changes like this, they aren’t always advertised on slide 1 in “What’s new in release X” presentations, but nevertheless great boosts to productivity.

      This post was nearly complete, but then rested in my drafts folder for just a little too long and 18c has been released. Time for an update!

      The environment

      Before moving on, here’s the stack in case you find this via a search engine:

      • Oracle Linux 7.4 powering 2 VMs: server1 and server2
      • Oracle 18.3.0, single instance, no Oracle Restart
      • Data Guard Broker configuration managing 2 databases: NCDBA and NCDBB

      The broker is quite happy with my setup, at least for now.

      DGMGRL> show configuration
      
      Configuration - test
      
        Protection Mode: MaxAvailability
        Members:
        NCDBA - Primary database
          NCDBB - Physical standby database 
      
      Fast-Start Failover: DISABLED
      
      Configuration Status:
      SUCCESS   (status updated 51 seconds ago)
      
      DGMGRL> 
      

      This is my setup, YMMV as always. Refer to your standard documents or other relevant documentation for more details about your configuration

      New things to validate in 18c

      With 12.2 it was possible to validate a datafile in addition to validating the database. Oracle 18c enhances the validate command quite a bit more:

      DGMGRL> help validate
      
      Performs an exhaustive set of validations for a member
      
      Syntax:
      
        VALIDATE DATABASE [VERBOSE] ;
      
        VALIDATE DATABASE [VERBOSE]  DATAFILE  
          OUTPUT=;
      
        VALIDATE DATABASE [VERBOSE]  SPFILE;
      
        VALIDATE FAR_SYNC [VERBOSE]  
          [WHEN PRIMARY IS ];
      
        VALIDATE NETWORK CONFIGURATION FOR { ALL |  };
      
        VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL |  };
      
      DGMGRL> 
      

      In this post I am going to focus on the verbose output generated by validate database, if I can find the time I’ll write about the other new options as well.

      Validate database in Oracle 18c

      Let’s have a look at the output of validate database verbose … Looking at the configuation status, NCDBB is currently running in recovery mode with NCDBA acting as the primary database. It makes sense to start with the validation of NCDBB first.

      The output of the command is rather comprehensive as you will see, I have decided to annotate the output so you don’t have to scroll up and down that much.

      DGMGRL> validate database verbose 'NCDBB'
      
        Database Role:     Physical standby database
        Primary Database:  NCDBA
      
        Ready for Switchover:  Yes
        Ready for Failover:    Yes (Primary Running)
      
        Flashback Database Status:
          NCDBA :  Off
          NCDBB :  Off
      
        Capacity Information:
          Database  Instances        Threads
          NCDBA      1               1
          NCDBB      1               1
      
        Managed by Clusterware:
          NCDBA :  NO
          NCDBB:  NO
          Validating static connect identifier for database NCDBA...
          The static connect identifier allows for a connection to database "NCDBA".
      

      The first part of the output is related to the database’s role and status. Oracle reckons my standby database is ready for a role change (which I’ll double-check using my own tools and scripts). You can see that flashback database is not enabled (for reasons that don’t matter for this post).

      Since both members are single instance databases it makes sense for them to have a single redo thread.

      Another important piece of information can be found in the “managed by Clusterware” section. In releases prior to 12.1.0.2 you always had to statically register your databases with the listener for use with the broker. This has changed in 12.1.0.2: another one of these productivity boosters :) In modern releases you don’t need to statically register your databases with the listener provided Clusterware manages them. See MOS DocID 1387859.1 for all the details.

      You read in the introduction that I’m using single instance Oracle databases without any Grid Infrastructure at all so this shiny new feature does not apply. There’s something else that might help though: Oracle validates the static connection identifier for you. It doesn’t simply ping the tns alias, the broker actually establishes a connection to the database. I noticed this in the listener.log: a program named dgmgrl connects to the database using the static conncetion identifier (as per “show database memberName staticconnectidentifier”). Output is formatted for readability:

      2018-08-14 10:54:16.377000 +01:00
      14-AUG-2018 10:54:16 * (CONNECT_DATA=(SERVICE_NAME=NCDBB_DGMGRL)(INSTANCE_NAME=NCDBB)(SERVER=DEDICATED)
      (STATIC_SERVICE=TRUE)(CID=(PROGRAM=dgmgrl)(HOST=server1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)
      (HOST=192.168.100.21)(PORT=27049)) * establish * NCDBB_DGMGRL * 0
      

      Let’s continue with the output of the validate database command:

        Temporary Tablespace File Information:
          NCDBA TEMP Files:   1
          NCDBB TEMP Files:   1
      
        Data file Online Move in Progress:
          NCDBA:  No
          NCDBB:  No
      

      This little section compares the number of temp files and warns you of any online data file move operations.

        Standby Apply-Related Information:
          Apply State:      Running
          Apply Lag:        0 seconds (computed 0 seconds ago)
          Apply Delay:      0 minutes
      
        Transport-Related Information:
          Transport On:      Yes
          Gap Status:        No Gap
          Transport Lag:     0 seconds (computed 0 seconds ago)
          Transport Status:  Success
      
      
        Log Files Cleared:
          NCDBA Standby Redo Log Files:  Cleared
          NCDBB Online Redo Log Files:   Not Cleared
          NCDBB Standby Redo Log Files:  Available
      
        Current Log File Groups Configuration:
          Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
                    (NCDBA)                 (NCDBB)
          1         2                       3                       Sufficient SRLs
      
        Future Log File Groups Configuration:
          Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
                    (NCDBB)                 (NCDBA)
          1         2                       3                       Sufficient SRLs
      
        Current Configuration Log File Sizes:
          Thread #   Smallest Online Redo      Smallest Standby Redo
                     Log File Size             Log File Size
                     (NCDBA)                    (NCDBB)
          1          200 MBytes                200 MBytes
      
        Future Configuration Log File Sizes:
          Thread #   Smallest Online Redo      Smallest Standby Redo
                     Log File Size             Log File Size
                     (NCDBB)                   (NCDBA)
          1          200 MBytes                200 MBytes
      

      This section is quite interesting as well as it allows you to have a gander at the transport lag and apply lag respectively. I can also see that I have standby redo logs both for my primary as well as the standby database. Sometimes the broker doesn’t get the number of standby redo logs right in “future log file groups configuration”. I found this to rectify itself after a switchover and switch-back.
      Another reassuring fact is presented in the current and future configuration log file sizes: I follwed th documentation carefully and created all my (online and standby) redo logs the exact same size.

        Apply-Related Property Settings:
          Property                        NCDBA Value              NCDBB Value
          DelayMins                       0                        0
          ApplyParallel                   AUTO                     AUTO
          ApplyInstances                  0                        0
      
        Transport-Related Property Settings:
          Property                        NCDBA Value              NCDBB Value
          LogXptMode                      sync                     sync
          Dependency                                        
          DelayMins                       0                        0
          Binding                         optional                 optional
          MaxFailure                      0                        0
          MaxConnections                  1                        1
          ReopenSecs                      300                      300
          NetTimeout                      30                       30
          RedoCompression                 DISABLE                  DISABLE
          LogShipping                     ON                       ON
      

      The section just above compares apply and transport related settings between the primary and standby databases. These are stored in Data Guard broker properties eventually mapping back to database initialisation parameters.

        Automatic Diagnostic Repository Errors:
          Error                       NCDBA    NCDBB
          No logging operation        NO       NO
          Control file corruptions    NO       NO
          SRL Group Unavailable       NO       NO
          System data file missing    NO       NO
          System data file corrupted  NO       NO
          System data file offline    NO       NO
          User data file missing      NO       NO
          User data file corrupted    NO       NO
          User data file offline      NO       NO
          Block Corruptions found     NO       NO
      
      DGMGRL> 
      

      And finally, it appears as if the Automatic Diagnostic Repository (ADR) didn’t show any issues. This doesn’t mean there aren’t, maybe there is still something looming in your configuration that hasn’t been detected yet.

      Summary

      The validate database command is really quite useful in my opinion performing basic checks before role changes. It doesn’t take the responsibility off you for ensuring the role change will be a success but I found it to be a useful first indicator.

      Franck Pachot's picture

      Oracle Adaptive Plan info in OTHER_XML

      DBMS_XPLAN displays the operation ID with no gap, even for Adaptive Plans where the inactive operations are skipped. Did you ever wonder where the information of skipped rows is stored?

      Here is a simple query (but please, remember that natural join is bad ;)

      SQL> set feedback on sql_id
      SQL> select * from dept natural join emp natural join bonus;
      no rows selected
      SQL_ID: 3q7fbwk91v4ra

      The execution plan shows that the plan is adaptive:

      SQL> select * from dbms_xplan.display_cursor(format=>'BASIC +note');
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------
      EXPLAINED SQL STATEMENT:
      ------------------------
      select * from dept natural join emp natural join bonus
      Plan hash value: 1315453310
      ------------------------------------------------
      | Id | Operation | Name |
      ------------------------------------------------
      | 0 | SELECT STATEMENT | |
      | 1 | NESTED LOOPS | |
      | 2 | NESTED LOOPS | |
      | 3 | HASH JOIN | |
      | 4 | TABLE ACCESS FULL | BONUS |
      | 5 | TABLE ACCESS FULL | EMP |
      | 6 | INDEX UNIQUE SCAN | PK_DEPT |
      | 7 | TABLE ACCESS BY INDEX ROWID| DEPT |
      ------------------------------------------------
      Note
      -----
      - this is an adaptive plan

      With the ‘+adaptive format’ we see the active and inactive branches:

      SQL> select * from dbms_xplan.display_cursor( sql_id=>'3q7fbwk91v4ra', format=>'BASIC +adaptive +note');
      PLAN_TABLE_OUTPUT
      ------------------------------------------------------
      EXPLAINED SQL STATEMENT:
      ------------------------
      select * from dept natural join emp natural join bonus
      Plan hash value: 1315453310
      -------------------------------------------------
      | Id | Operation | Name |
      -------------------------------------------------
      | 0 | SELECT STATEMENT | |
      |- 1 | HASH JOIN | |
      | 2 | NESTED LOOPS | |
      | 3 | NESTED LOOPS | |
      |- 4 | STATISTICS COLLECTOR | |
      | 5 | HASH JOIN | |
      | 6 | TABLE ACCESS FULL | BONUS |
      | 7 | TABLE ACCESS FULL | EMP |
      | 8 | INDEX UNIQUE SCAN | PK_DEPT |
      | 9 | TABLE ACCESS BY INDEX ROWID| DEPT |
      |- 10 | TABLE ACCESS FULL | DEPT |
      -------------------------------------------------
      Note
      -----
      - this is an adaptive plan (rows marked '-' are inactive)

      You can see that the numbering has changed: now 1–10 and this is what we actually have in PLAN_TABLE:

      SQL> column "LPAD('',DEPTH,'')||OPERATION" format a30
      SQL> select id,depth,parent_id,lpad(' ',depth,' ')||operation from v$sql_plan where sql_id like '3q7fbwk91v4ra';
      ID      DEPTH  PARENT_ID LPAD('',DEPTH,'')||OPERATION
      ---------- ---------- ---------- ------------------------------
      0 0 SELECT STATEMENT
      1 1 0 HASH JOIN
      2 2 1 NESTED LOOPS
      3 3 2 NESTED LOOPS
      4 4 3 STATISTICS COLLECTOR
      5 5 4 HASH JOIN
      6 6 5 TABLE ACCESS
      7 6 5 TABLE ACCESS
      8 4 3 INDEX
      9 3 2 TABLE ACCESS
      10 2 1 TABLE ACCESS

      The ID here is unique. Adaptive Plan does not break this. So this means that it was mapped to different numbers when the inactive lines were skipped. DBMS_XPLAN does this for us but if we have our own tool we need to know where this mapping comes from.

      /other_xml/display_map

      As most of the additional information that we can see in the notes, they come from OTHER_XML

      SQL> set long 100000 linesize 68
      SQL> select other_xml from v$sql_plan where sql_id like '3q7fbwk91v4ra' and id=1;
      OTHER_XML
      --------------------------------------------------------------------
      18.0.0.0176307
      9200
      13154533104080194477yesfo>><
      ![CDATA[DB_VERSION('18.1.0')]]>
      nt>ATA[MERGE(@"SEL$58A6D7F6" >"SEL$3")]]>@"SEL$3")]]>
      "SEL$2")]]>INE(@"SEL$2")]]>nt>
      CDATA[FULL(@"SEL$9E43CB6E" "EMP"@"SEL$1")]]>DEX(@"SEL$9E43CB6E" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]>>T"@"SEL$1")]]>
      SEL$1")]]>")]]>1")]]>
      prt="0" dep="0" skp="1"/> op="4" dis="2" par="2" prt="0" dep="2" skp="1"/> par="2" prt="0" dep="3" skp="0"/> dis="7" par="1" prt="0" dep="2" skp="0"/>

      Here is what we have in /other_xml/display_map:

      SQL> select column_value from v$sql_plan , table(xmlsequence(extract(xmltype(other_xml),'/*/display_map/row'))) where sql_id like '3q7fbwk91v4ra' and id=1;
      COLUMN_VALUE
      --------------------------------------------------------------------
      skp="1"/>


      skp="1"/>





      skp="1"/>

      I can see one row per execution plan operation, referenced by ‘op’, the operation ID, the ‘par’, the PARENT_ID, and ‘dep’ the DEPTH. The ‘skp’ flags with 1 the inactive branches that must be skipped in the default format, and ‘dis’ is the display ID to map to in order to have no gap. I don’t think that the ‘par’ — PART_ID — is currently used by dbms_xplan.

      When is this information translated from internal representation to XML? We can see some internal structure copy during Execution such as qesdpCopySharedToExeDisplayMap() and qesdpCopyExeToSharedDisplayMap() but the conversion to XML is done when querying V$SQL_PLAN.OTHER_XML:

      #0  0x000000000916a390 in qesdpWriteDisplayMap ()
      #1 0x00000000098c447d in qksxaCompactToCustomXml ()
      #2 0x00000000098c489f in qksxaCompactToXml ()
      #3 0x000000000befe794 in xplCompactToOtherXml ()
      #4 0x000000000beff2ee in xplNodeToRow ()
      #5 0x000000000bf0045d in xplMakeRow ()
      #6 0x00000000121606aa in xplFetchRow ()
      #7 0x000000000b0143e8 in kqlfgx ()
      #8 0x0000000010d87767 in kglic_cbk ()
      #9 0x0000000010d87035 in kglic0 ()
      #10 0x0000000010d86d1c in kglic ()
      #11 0x000000000b01095b in kqlfxp ()
      #12 0x00000000040b1cf7 in qerfxFetch ()
      #13 0x00000000120661af in opifch2 ()

      It is interesting to see that even when this information could have been displayed as columns in V$SQL_PLAN, the CBO developers chose the ‘NoSQL’ way of putting everything new in XML. We see the limit when we try to build our own tools. I must admit that I have some awk scripts which parse the dbms_xplan.display_cursor() text output rather than trying to get information from V$SQL_PLAN…

      About tools other than DBMS_XPLAN, SQL Monitor has the information in the XML. The Flash version differentiate active and inactive operations, but it looks like the SQLDev HTML5 does not (yet)grey the inactive one.

      Jonathan Lewis's picture

      Dump logfile

      Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.

      The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available.

      
      rem
      rem     Script:         c_dump_log.sql
      rem     Author:         Jonathan Lewis
      rem     Dated:          December 2002
      rem     Purpose:        Create procedured to dump the current online redo log file.
      rem
      rem     Last tested
      rem             18.3.0.0
      rem             12.2.0.1
      rem             11.1.0.7
      rem             11.2.0.6
      rem             10.2.0.5
      rem             10.1.0.4
      rem              9.2.0.8
      rem              8.1.7.4
      rem
      rem     Notes:
      rem     Must be run as a DBA
      rem     Very simple minded - no error trapping
      rem     
      
      create or replace procedure dump_log
      as
              m_log_name      varchar2(255);
              m_process       varchar2(255);
              m_trace_name    varchar2(255);
      
      begin
              select 
                      lf.member
              into
                      m_log_name
              from
                      V$log           lo,
                      v$logfile       lf
              where 
                      lo.status = 'CURRENT'
              and     lf.group# = lo.group#
              and     rownum = 1
              ;
      
              execute immediate
              'alter system dump logfile ''' || m_log_name || '''';
      
              select
                      spid
              into
                      m_process
              from
                      v$session       se,
                      v$process       pr
              where
                      se.sid = --dbms_support.mysid
                              (select sid from v$mystat where rownum = 1)
              and     pr.addr = se.paddr
              ;
      
              select
                      tracefile
              into
                      m_trace_name
              from
                      v$session       se,
                      v$process       pr
              where
                      se.sid = --dbms_support.mysid
                              (select sid from v$mystat where rownum = 1)
              and     pr.addr = se.paddr
              ;
      
              dbms_output.put_line('Trace file is: ' || m_trace_name);
              dbms_output.put_line('Log file name is: ' || m_log_name);
              dbms_output.put_line('Trace file name includes: ' || m_process);
      
      
      end;
      /
      
      show errors
      
      drop public synonym dump_log;
      create public synonym dump_log for dump_log;
      grant execute on dump_log to public;
      
      

      I don’t use the package often but if I want to find out what redo is generated during a test I usually follow the sequence:

      • alter system switch logfile;
      • do the experiment
      • execute dump_log

      If you’re running in a PDB there’s an extra step needed as you can’t “switch logfile” inside a PDB so I’ll either do a log file switch before I start the test or (if there are steps in the test script that could generate a lot of log file I don’t want to see) I include a “pause” in the test script and use another session to do the logfile switch – in both cases the second session has to be connected to the CDB.

      You will have noticed the creation of the public synonym and granting of the execute privilege to public. In my own sandbox database that’s a convenience – you may want to be a little more protective in your development and test systems.

      The “dump logfile” command has a number of options for selective dumping – I have a note in my file commenting on these options, but I haven’t checked if there are any new ones (or changes to existing ones) for a long time:

      
      alter system dump logfile '{filename}'
              scn min {first SCN to dump}
              scn max {last SCN to dump}
              time min {seconds since midnight at the end of 1st Sept 1987}
              time max {see redo_time_calc.sql}
              layer {integer} opcode {integer} e.g.:
                      layer 23        Block Written Records
                      layer 5         Undo handling in general
                      layer 5 opcode 4        Undo Seg header on commit; or rollback;
                      layer 9999 opcode 9999  Trick to validate the whole log file structure
              xid {usn} {slot} {sequence}     -- 10g only, may break on IMU redo (see below)
              objno {object_id}               -- 10g only, may break on IMU redo (see below)
              dba min {datafile no} . {blockno} -- with spaces either side of the dot.
              dba max {datafile no} . {blockno} -- with spaces either side of the dot.
              rba min {log file seq no} . {blockno} -- with spaces either side of the dot.
              rba max {log file seq no} . {blockno} -- with spaces either side of the dot..
      (The dots in the last four options becomes invalid syntax in 10g).
      
      

      The introduction to this note references back to a presentation I did in the year 2000, but the closing comment suggests that I probably haven’t checked the list since some time in the 10g timeline.

      The reference to redo_time_calc.sql points to the following script, that expresses the time as the number of seconds since Jan 1988, with the unfortunate simplification that Oracle thinks there are 31 days in every month of the year:

      
      rem
      rem     Script:         redo_time_calc3.sql
      rem     Author:         Jonathan Lewis
      rem     Dated:          Dec 2012
      rem     Purpose:
      rem
      
      select 
              86400 * (
                      31 *
                              months_between(
                                      trunc(sysdate,'MM'),
                                      to_date('01-Jan-1988','dd-mon-yyyy')
                              ) +
                      sysdate - trunc(sysdate,'MM')
              )       redo_now
      from 
              dual
      ;
      
      
      
      select 
              86400 * (
                      (sysdate - 10/1440) - trunc((sysdate-10/1440),'MM') + 
                      31 * 
                              months_between(
                                      trunc((sysdate - 10/1440),'MM'),
                                      to_date('01-Jan-1988','dd-mon-yyyy')
                              )
                      )               ten_minutes_ago,
              86400 * (
                      sysdate - trunc(sysdate,'MM') + 
                      31 * 
                              months_between(
                                      trunc(sysdate,'MM'),
                                      to_date('01-Jan-1988','dd-mon-yyyy')
                              )
                      )               redo_now,
              to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')        now
      from 
              dual
      ;
      
      

      This isn’t a piece of code I use much – the original version (which I published in Oracle Core, p.241) was something I wrote in 2003 and had to adjust by hand each time I used it without realising that I’d got it wrong. Luckily someone pointed out my error and gave me the corrected code a little while after I’d published the book. (It was one of those “why didn’t I think of that” moments – it seemed so obvious after he’d told me the right answer.)

      martin.bach's picture

      Log in to Ubuntu VMs in Oracle Cloud Infrastructure

      When I learned that Oracle was providing Ubuntu images in Oracle Cloud Infrastructure (OCI) I was a bit surprised at first. After all, Oracle provides a great Enterprise Linux distribution in the form of Oracle Linux. As a Ubuntu fan I do of course appreciate the addition of Ubuntu to the list of supported distributions. In fact it doesn’t end there, have a look at the complete list of Oracle provided images to see what’s available.

      Trying Ubuntu LTS

      I wanted to give Ubuntu a spin on OCI and decided to start a small VM using the 16.04 LTS image. I have been using this release quite heavily in the past and have yet to make the transition to 18.04. Starting the 16.04 VM up was easily done using my terraform script. Immediately after the terraform prompt returned I faced a slight issue: I couldn’t log in:

      $ ssh opc@w.x.y.z
      The authenticity of host ... can't be established.
      ...
      opc@w.x.y.z: Permission denied (publickey)
      

      This is entirely my fault, for some reason I didn’t scroll down within the page to read more about users. Assuming the account created during the VM provisioning would be the same as for the Oracle Linux image, I tried logging in as user “opc”. The result is what I showed you earlier in the listing.

      The clue about users is found in Linux Image Details, section “users” and aforementioned documentation page. I am quoting verbally because I couldn’t possibly say it any better:

      For instances created using the Ubuntu image, the user name ubuntu is created automatically. The ubuntu user has sudo privileges and is configured for remote access over the SSH v2 protocol using RSA keys. The SSH public keys that you specify while creating instances are added to the /home/ubuntu/.ssh/authorized_keys file.

      There it is.

      It seems I wasn’t the only one, and beginning with the Canonical-Ubuntu-16.04-2018.11.15-0 image, a message is displayed when you try to log in as opc:

      $ ssh opc@w.x.y.z
      ...
      Warning: Permanently added ... to the list of known hosts
      Please login as the user "ubuntu" rather than the user "opc".
      
      Connection to w.x.y.z closed
      $ 
      

      So no more missing this important piece of information :)

      To prevent automated spam submissions leave this field empty.