Table of contents
Preamble
Following the Oracle Certified Master preparation workshop training I had to look deeper in few Business Intelligence (BI) stuffs to try to be well prepared. Nothing new in this blog post except few testing on star transformation, bitmap join index and automatic degree of parallelism. Star transformation and automatic degree of parallelism were already familiar to me but bitmap join index was completely new even if it is available since Oracle 9i (!!).
In a Data Warehouse data model two main typical design models are called:
- Star Schema
- Snowflake Schema
They can be represented as, star schema. The fact table (business figures) has a direct n-1 relationship with multiples dimensions tables (customer, geography, …):
Snowflake schema:
Even if both schemas are acceptable in a BI environment Oracle recommendation is quite clear:
Oracle recommends you choose a star schema over a snowflake schema unless you have a clear reason not to.
Snowflake schema is an extension of the star schema where dimension tables are spread in multiple sub-tables. From Oracle standpoint to benefit from special Cost Based Optimizer (CBO) related improvements the parameter to modify is STAR_TRANSFORMATION_ENABLED = { FALSE | TRUE | TEMP_DISABLE }.
The example below is on a star schema and is using a 11.2.0.4 Enterprise edition database running on HPUX 11iv3 (11.31) with partitioning, diagnostic and tuning pack.
On our reporting BI environment the Business Object universe with the most heavy queries is using a unique Oracle account and it has been easy to find the most consuming ones with something like:
SELECT ROUND(a.elapsed_time/1000000) AS "Elapsed seconds",NUMTODSINTERVAL(ROUND(a.elapsed_time/1000000),'SECOND') AS "Elapsed timestamp",a.sql_id,a.child_number,a.sql_fulltext,a.* FROM v$sql a WHERE a.parsing_schema_name='MARS_DBA' ORDER BY a.elapsed_time DESC; |
Most of the queries does not fit on a single page and when chosen one of the most “simple” I have encountered huge difficulties on the BI test environment to make star transformation happen (while it was working well on live one). This story is probably not new to the ones reading this document and it clearly shows the gap that can occur between a prepared test case for a presentation and real life…
Data Warehouse test case
I have inherited and extended the test tables I created for the In-Memory feature of Oracle 12cR1. For this test case I will have two dimension tables city and customer and a sales fact table linked to my two dimension tables. The dimension tables have both a primary key and I create two bitmap indexes on the foreign columns of the fact table. The creation script can be downloaded for home testing.
I executed all queries setting in my environment:
SET autotrace trace TIMING ON TIME ON lines 150 pages 1000 |
To benefit from extended running statistics you may also want to alter your session (or use /*+ gather_plan_statistics */ hint while running SQL statements):
ALTER SESSION SET statistics_level=ALL; |
Then to display those extended statistics use something like:
SELECT * FROM TABLE(dbms_xplan.display_cursor('sql_id','child_number','ALL ALLSTATS')); |
For each run to be fresh I have also issued:
ALTER SYSTEM flush shared_pool; ALTER SYSTEM flush buffer_cache; |
All queries have been executed multiple times and execution quite is unfortunately quite erratic as my test system is sharing its resources with many colleagues and even the underlining storage is shared. So do not take too much account of the figures you see in A-Time column, I have more focused on number of consistent gets and physical reads …
Star Transformation Enabled
One important thing I noticed in Oracle official documentation about Star Transformation (Oracle Database Data Warehousing Guide):
A prerequisite of the star transformation is that there be a single-column bitmap index on every join column of the fact table. These join columns include all foreign key columns.
The test query I have chosen is the following:
SELECT /*+ gather_plan_statistics */ c.descr, b.descr, SUM(qty), SUM(val) FROM sales a, city b, customer c WHERE a.city__code=b.code AND a.customer__code=c.code AND b.descr='Arbois' AND c.descr='Total' GROUP BY c.descr,b.descr ORDER BY c.descr,b.descr; |
Without Star Transformation Enabled the explain plan is the following:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | Reads | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 10360 (100)| | 1 |00:00:21.97 | 52219 | 52218 | | | | | 1 | SORT GROUP BY NOSORT | | 1 | 1 | 79 | 10360 (1)| 00:01:54 | 1 |00:00:21.97 | 52219 | 52218 | | | | | 2 | NESTED LOOPS | | 1 | 55556 | 4286K| 10360 (1)| 00:01:54 | 59421 |00:00:20.02 | 52219 | 52218 | | | | | 3 | NESTED LOOPS | | 1 | 55556 | 4286K| 10360 (1)| 00:01:54 | 59421 |00:00:00.13 | 400 | 399 | | | | | 4 | MERGE JOIN CARTESIAN | | 1 | 1 | 28 | 10 (0)| 00:00:01 | 1 |00:00:00.02 | 12 | 12 | | | | |* 5 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 6 | BUFFER SORT | | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | 2048 | 2048 | 1/0/0| |* 7 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 8 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | 59421 |00:00:00.08 | 388 | 387 | | | | | 9 | BITMAP AND | | 1 | | | | | 7 |00:00:00.36 | 388 | 387 | | | | |* 10 | BITMAP INDEX SINGLE VALUE| SALES_IDX_CUSTCODE | 1 | | | | | 381 |00:00:00.07 | 193 | 193 | | | | |* 11 | BITMAP INDEX SINGLE VALUE| SALES_IDX_CC | 1 | | | | | 381 |00:00:00.06 | 195 | 194 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | SALES | 59421 | 55556 | 2766K| 10360 (1)| 00:01:54 | 59421 |00:00:21.43 | 51819 | 51819 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
Executed in around 20-25 seconds over 4 runs with average below statistics:
STATISTICS ---------------------------------------------------------- 180 recursive calls 0 db block gets 52912 consistent gets 52268 physical reads 0 redo SIZE 768 bytes sent via SQL*Net TO client 519 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 77 sorts (memory) 0 sorts (disk) 1 ROWS processed |
When you set star_transformation_enabled to true explain plan is:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | Reads | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 9998 (100)| | 1 |00:01:05.70 | 52232 | 52219 | | | | | 1 | SORT GROUP BY NOSORT | | 1 | 1 | 99 | 9998 (1)| 00:01:50 | 1 |00:01:05.70 | 52232 | 52219 | | | | |* 2 | HASH JOIN | | 1 | 55556 | 5371K| 9998 (1)| 00:01:50 | 59421 |00:01:08.33 | 52232 | 52219 | 1416K| 1416K| 1/0/0| | 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 28 | 10 (0)| 00:00:01 | 1 |00:00:00.03 | 12 | 12 | | | | |* 4 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.02 | 6 | 6 | | | | | 5 | BUFFER SORT | | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | 2048 | 2048 | 1/0/0| |* 6 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 7 | VIEW | VW_ST_5E1B3BB9 | 1 | 55556 | 3852K| 9987 (1)| 00:01:50 | 59421 |00:01:08.20 | 52220 | 52207 | | | | | 8 | NESTED LOOPS | | 1 | 55556 | 4069K| 9977 (1)| 00:01:50 | 59421 |00:01:08.16 | 52220 | 52207 | | | | | 9 | BITMAP CONVERSION TO ROWIDS| | 1 | 55555 | 976K| 17 (0)| 00:00:01 | 59421 |00:00:00.51 | 401 | 388 | | | | | 10 | BITMAP AND | | 1 | | | | | 7 |00:00:00.96 | 401 | 388 | | | | | 11 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.40 | 201 | 194 | 2804K| 512K| 1/0/0| | 12 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.36 | 201 | 194 | | | | |* 13 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 14 | BITMAP INDEX RANGE SCAN| SALES_IDX_CC | 1 | | | | | 381 |00:00:00.36 | 195 | 194 | | | | | 15 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.37 | 200 | 194 | 1024K| 512K| 1/0/0| | 16 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.12 | 200 | 194 | | | | |* 17 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 18 | BITMAP INDEX RANGE SCAN| SALES_IDX_CUSTCODE | 1 | | | | | 381 |00:00:00.12 | 194 | 194 | | | | | 19 | TABLE ACCESS BY USER ROWID | SALES | 59421 | 1 | 57 | 9970 (1)| 00:01:50 | 59421 |00:01:04.49 | 51819 | 51819 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
Remark:
to confirm star transformation has been used you must see in explain plan of your query:
Note ----- - star transformation used FOR this statement |
Over 4 runs execution time is almost the same with below equivalent statistics:
Statistics
----------------------------------------------------------
183 recursive calls
0 db block gets
52929 consistent gets
52270 physical reads
0 redo size
768 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
77 sorts (memory)
0 sorts (disk)
1 rows processed |
I would even say that execution plan with star transformation looks even worst than original one. Oracle CBO starts by filtering non-needed rows in fact table (SALES) using the two dimensions table and create a temporary view of this result (VW_ST_5E1B3BB9 created with the two BITMAP MERGE and BITMAP KEY ITERATION operations). Then this temporary view is again joined with dimension tables to get the description and provide the final grouped result.
This is maybe why Oracle has decided not to activate the feature by default as result may not be the one you expect. In other words I have seen statement benefiting from star transformation while for others it was the opposite. CBO lower cost is not always a proof that statement will run faster. Please note than even if feature is off you may activate at statement level with /*+ STAR_TRANSFORMATION */ hint.
Bitmap Join Index
Bitmap join index are created on fact table storing information from dimension tables and avoiding joins.
The first index I have tried, not yet optimal versus my query, is the following:
CREATE bitmap INDEX sales_bjidx_1 ON sales(city.descr) FROM sales, city WHERE sales.city__code=city.code nologging; |
With or without star transformation it gives around same execution time and same statistics and below explain plan is with star transformation:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | Reads | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 416 (100)| | 1 |00:00:39.97 | 52425 | 52412 | | | | | 1 | SORT GROUP BY NOSORT | | 1 | 1 | 79 | 416 (1)| 00:00:05 | 1 |00:00:39.97 | 52425 | 52412 | | | | |* 2 | HASH JOIN | | 1 | 55556 | 4286K| 406 (1)| 00:00:05 | 59421 |00:00:37.80 | 52425 | 52412 | 1416K| 1416K| 1/0/0| | 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 28 | 10 (0)| 00:00:01 | 1 |00:00:00.02 | 12 | 12 | | | | |* 4 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 5 | BUFFER SORT | | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | 2048 | 2048 | 1/0/0| |* 6 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 55556 | 2766K| 396 (1)| 00:00:05 | 59421 |00:00:37.68 | 52413 | 52400 | | | | | 8 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | 59421 |00:00:00.55 | 594 | 581 | | | | | 9 | BITMAP AND | | 1 | | | | | 7 |00:00:01.12 | 594 | 581 | | | | | 10 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.35 | 201 | 194 | 1024K| 512K| 1/0/0| | 11 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.11 | 201 | 194 | | | | |* 12 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 13 | BITMAP INDEX RANGE SCAN| SALES_IDX_CC | 1 | | | | | 381 |00:00:00.11 | 195 | 194 | | | | | 14 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.40 | 200 | 194 | 1024K| 512K| 1/0/0| | 15 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.10 | 200 | 194 | | | | |* 16 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 17 | BITMAP INDEX RANGE SCAN| SALES_IDX_CUSTCODE | 1 | | | | | 381 |00:00:00.10 | 194 | 194 | | | | |* 18 | BITMAP INDEX SINGLE VALUE| SALES_BJIDX_1 | 1 | | | | | 381 |00:00:00.11 | 193 | 193 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
Without star transformation the bitmap index is simply not used… But even if used it does not provide much benefit. Let’s try with a more optimal bitmap join index:
CREATE bitmap INDEX sales_bjidx_2 ON sales(city.descr, customer.descr) FROM sales, city, customer WHERE sales.city__code=city.code AND sales.customer__code=customer.code nologging; |
This “more optimal” bitmap join index is used whatever star transformation is activated or not. With star transformation it does not even suppress an access to city or customer dimension tables so really disappointed on this:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | Reads | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 54 (100)| | 1 |00:00:56.67 | 52247 | 52234 | | | | | 1 | SORT GROUP BY NOSORT | | 1 | 1 | 79 | 54 (0)| 00:00:01 | 1 |00:00:56.67 | 52247 | 52234 | | | | |* 2 | HASH JOIN | | 1 | 55556 | 4286K| 44 (0)| 00:00:01 | 59421 |00:00:53.85 | 52247 | 52234 | 1416K| 1416K| 1/0/0| | 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 28 | 10 (0)| 00:00:01 | 1 |00:00:00.03 | 12 | 12 | | | | |* 4 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.02 | 6 | 6 | | | | | 5 | BUFFER SORT | | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | 2048 | 2048 | 1/0/0| |* 6 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 55556 | 2766K| 34 (0)| 00:00:01 | 59421 |00:00:53.73 | 52235 | 52222 | | | | | 8 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | 59421 |00:00:00.46 | 416 | 403 | | | | | 9 | BITMAP AND | | 1 | | | | | 6 |00:00:00.91 | 416 | 403 | | | | | 10 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.33 | 201 | 194 | 1024K| 512K| 1/0/0| | 11 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.20 | 201 | 194 | | | | |* 12 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 13 | BITMAP INDEX RANGE SCAN| SALES_IDX_CC | 1 | | | | | 381 |00:00:00.20 | 195 | 194 | | | | | 14 | BITMAP MERGE | | 1 | | | | | 85 |00:00:00.33 | 200 | 194 | 1024K| 512K| 1/0/0| | 15 | BITMAP KEY ITERATION | | 1 | | | | | 381 |00:00:00.18 | 200 | 194 | | | | |* 16 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 | | | | |* 17 | BITMAP INDEX RANGE SCAN| SALES_IDX_CUSTCODE | 1 | | | | | 381 |00:00:00.18 | 194 | 194 | | | | |* 18 | BITMAP INDEX SINGLE VALUE| SALES_BJIDX_2 | 1 | | | | | 23 |00:00:00.20 | 15 | 15 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
Without star transformation:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME | A-ROWS | A-TIME | Buffers | Reads | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 9983 (100)| | 1 |00:00:52.50 | 51846 | 51846 | | | | | 1 | SORT GROUP BY NOSORT | | 1 | 1 | 79 | 9983 (1)| 00:01:50 | 1 |00:00:52.50 | 51846 | 51846 | | | | |* 2 | HASH JOIN | | 1 | 62 | 4898 | 9983 (1)| 00:01:50 | 59421 |00:00:47.68 | 51846 | 51846 | 1416K| 1416K| 1/0/0| | 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 28 | 10 (0)| 00:00:01 | 1 |00:00:00.02 | 12 | 12 | | | | |* 4 | TABLE ACCESS FULL | CITY | 1 | 1 | 11 | 5 (0)| 00:00:01 | 1 |00:00:00.02 | 6 | 6 | | | | | 5 | BUFFER SORT | | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | 2048 | 2048 | 1/0/0| |* 6 | TABLE ACCESS FULL | CUSTOMER | 1 | 1 | 17 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 6 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 55556 | 2766K| 9973 (1)| 00:01:50 | 59421 |00:00:47.57 | 51834 | 51834 | | | | | 8 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | 59421 |00:00:00.04 | 15 | 15 | | | | |* 9 | BITMAP INDEX SINGLE VALUE | SALES_BJIDX_2 | 1 | | | | | 23 |00:00:00.24 | 15 | 15 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
But execution time and statistics are almost the same… So again not much benefit from the bitmap join index feature in my test environment, which obviously does not mean it could not be the case in a more real life example…
Automatic parallelism
This is a new initialization parameter that came with Oracle 11gR2. By default automatic degree of parallelism is not activated. To use it you have to set parallel_degree_policy = auto initialization parameter at session or system level:
ALTER SESSION SET parallel_degree_policy=auto; |
My first try gave me below “error” message:
Note ----- - automatic DOP: skipped because OF IO calibrate STATISTICS are missing - star transformation used FOR this statement |
I have simply followed My Oracle Support note 1269321.1 and executed below PL/SQL tuned for my environment (12 disks and expecting 10 milliseconds maximum of latency: we have a HP XP SAN):
SET SERVEROUTPUT ON DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN DBMS_RESOURCE_MANAGER.CALIBRATE_IO (12, 10, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE('latency = ' || lat); DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps); END; / |
Which gave below output:
max_iops = 788 latency = 9 max_mbps = 248
Second issue was:
automatic DOP: Computed Degree OF Parallelism IS 1 because OF parallel threshold |
Because initialization parameter parallel_min_time_threshold was to auto (10 seconds) so Oracle estimated my query to run faster than 10 seconds (which is not true) and did not fire automatic DOP. I set the parameter to 1 at session level… The third run shown a increased degree of parallelism:
Note ----- - automatic DOP: Computed Degree OF Parallelism IS 3 |
Response time was not exactly faster but more consistent I would say. In serial mode it went from 22 seconds to 1 minutes and 40 seconds for the multiple times I executed the query. In parallel over the 10 executions it was always around 55 seconds…
References
- All about Bitmap Indexes (Doc ID 70067.1)
- Using Bitmap Join Indexes to Avoid Join Operations (Doc ID 149521.1)
- Schema Modeling Techniques
- Using Bitmap Join Indexes in Data Warehouses
- Automatic Degree of Parallelism in 11.2.0.2 (Doc ID 1269321.1)
- How to Analyze Why PX Queries Are Slow with AutoDOP Feature (Doc ID 1380736.1)
