Patch Level: "OCW Patch Set Update : 12.1.0.2.4 (20831113)"
Like a lot of people I guess, I just upgraded an existing DWH, have set some 12c in memory parameters and hoped for significant performance improvements.
That did not happen.
So I decided to build a sample DWH with a time and product dimension and a partitioned fact table which is so big that it would not fit completly into the inmemory part, to do testing in a much simpler environment.
In my example, the dimensions are loaded first into memory with priority. The fact will be loading partitions depending on the usage so that the "actual" data will not fit.
Questions I wanted to answer were:
1. Will the inmemory query be much faster than the no inmemory version. (Answer: It depends. Often it's no!)
2. Will ORACLE exchange the fact table partitions INMEMORY when there is no more space and
we query other partitions (e.g. as FIFO?) data. (Answer: No that's documented)
3. Will I see in v$im_segments that I do not have all defined partitions in memory
(No you need to join with DBA_SEGMENTS to see the partitions not in memory.)
Now I tried to find out why I did not have better success.
And while I played and read I found VECTOR_TRANSFORM
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=287258785184897&id=1935305.1&_afrWindowMode=0&_adf.ctrl-state=15o1gcrocb_55
THAT gave a real boost to my query! (when the optimizer takes it)
I've set (to always have it for all my normal DWH queries)
alter system set "_always_vector_transformation"=true;
alter system set "_optimizer_vector_cost_adj"=20;
But that did not work for all queries.
So I decided to share my results with you, and ask if anyone knows why ORACLE here sometimes decide not to use VECTOR_TRANSFORM.
I also did not find much working examples on the WEB which really show the difference in performance of INMEMORY. So I did it here an hope for more working examples / tricks etc.
Here in short my tests, and you see that I'm MUCH faster when both features are used.
Without VECTOR_TRANSFORM and INMEMORY
Elapsed: 00:00:38.56
A query which takes VECTOR_TRANSFORM and INMEMORY takes:
Elapsed: 00:00:00.75 <==== That's factor 50 and VERY impressive!!
Without VECTOR_TRANSFORM but with INMEMORY
Elapsed: 00:00:09.32
Even when the fact is not in memory the query takes:
Elapsed: 00:00:02.79
So, as my system memory is limited, I'd put all dimensions to INMEMORY and
hope that the VECTOR_TRANSFORM is ALWAYS used
But, even I set "_always_vector_transformation" and give a VECTOR_TRANSFORM hint.
VECTOR_TRANSFORM is sometimes not used.
I've set the events below to get an answer, and checked the trace:
alter session set events 'trace[SQL_Transform.*] disk high';
alter session set events 'trace[SQL_Costing.*] disk high';
but that did not help me either. (too much information ;-)
My settings (SOLARIS 10, Intel x64, 64 CPU, 1TB server memory)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 12
inmemory_query string ENABLE
inmemory_size big integer 1G
inmemory_trickle_repopulate_servers_ integer 50
percent
optimizer_inmemory_aware boolean TRUE
sga_max_size big integer 20G
sga_target big integer 20G
_always_vector_transformation boolean TRUE
The DWH, which you can build yourself with the statements below, size
(The fact table has 4000 "days"/partitions of data using 32GB)
SEGMENT_NAME MB COUNT(*)
-------------------------- ----------
SALES_FACT 32000 4000
DAY_DIMENSION 0 1
PRODUCT_DIMENSION 2 1
(If you do not have 32GB to use, it's easy to make the example smaller.)
PKs and Constraints have been set.
Statistics with histograms on indexed rows, or, on day_dimension, every field are taken to give the optimizer everthing he might need.
Thanks for thinking about this.
Andy
Queries:
--------
-- Q1:
------
select
min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2012
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:00.75
-- Q2: when "min" is missing in the SQL VECTOR_TRANSFORM is NOT done.
------
select /*+ VECTOR_TRANSFORM */
--min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2012
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:09.32
-- Q3: when "min" is missing in the SQL VECTOR_TRANSFORM is NOT done and without INMOMRY it's slow.
------
select /*+ VECTOR_TRANSFORM NO_INMEMORY */
--min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2012
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:38.78
-- Q4:
------
select /*+ VECTOR_TRANSFORM NO_INMEMORY */
min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2012
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:01.03
--Q5:
-----
select /*+ NO_VECTOR_TRANSFORM NO_INMEMORY */
min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2012
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:38.56
---Q6: (fact partitoned for 2014 not in memory)
------
select
min(d.day_id),max(d.day_id),
d.year, p.product_type,sum(f.sale_price) sale_price,count(*)
from day_dimension d
inner join sales_fact f
on d.day_id= f.day_id
inner join product_dimension p
on p.product_id =f.product_id
where d.year =2014
and p.product_type='TABLE'
group by d.year, p.product_type
order by d.year, p.product_type;
-- Elapsed: 00:00:02.70
Q1:
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 259 | 20720 | 176K (57)| 00:00:28 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6928_73F9117C | | | | | | |
| 3 | VECTOR GROUP BY | | 366 | 2928 | 1 (0)| 00:00:01 | | |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | | |
|* 5 | TABLE ACCESS INMEMORY FULL | DAY_DIMENSION | 366 | 2928 | 1 (0)| 00:00:01 | | |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6929_73F9117C | | | | | | |
| 7 | VECTOR GROUP BY | | 1 | 12 | 1 (0)| 00:00:01 | | |
| 8 | HASH GROUP BY | | 1 | 12 | 1 (0)| 00:00:01 | | |
| 9 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | |
|* 10 | TABLE ACCESS INMEMORY FULL | PRODUCT_DIMENSION | 888 | 10656 | 1 (0)| 00:00:01 | | |
| 11 | SORT GROUP BY NOSORT | | 259 | 20720 | 176K (57)| 00:00:28 | | |
|* 12 | HASH JOIN | | 259 | 20720 | 176K (57)| 00:00:28 | | |
| 13 | MERGE JOIN CARTESIAN | | 366 | 7320 | 4 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6929_73F9117C | 1 | 12 | 2 (0)| 00:00:01 | | |
| 15 | BUFFER SORT | | 366 | 2928 | 2 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6928_73F9117C | 366 | 2928 | 2 (0)| 00:00:01 | | |
| 17 | VIEW | VW_VT_4FBA27B6 | 259 | 15540 | 176K (57)| 00:00:28 | | |
| 18 | VECTOR GROUP BY | | 259 | 3367 | 176K (57)| 00:00:28 | | |
| 19 | HASH GROUP BY | | 259 | 3367 | 176K (57)| 00:00:28 | | |
| 20 | KEY VECTOR USE | :KV0000 | | | | | | |
| 21 | KEY VECTOR USE | :KV0001 | | | | | | |
| 22 | PARTITION RANGE SUBQUERY | | 160M| 1983M| 77948 (2)| 00:00:13 |KEY(SQ)|KEY(SQ)|
|* 23 | TABLE ACCESS INMEMORY FULL| SALES_FACT | 160M| 1983M| 77948 (2)| 00:00:13 |KEY(SQ)|KEY(SQ)|
-----------------------------------------------------------------------------------------------------------------------------------
Q2:
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1308K| 41M| 78385 (2)| 00:00:13 | | |
| 1 | SORT GROUP BY NOSORT | | 1308K| 41M| 78385 (2)| 00:00:13 | | |
|* 2 | HASH JOIN | | 1308K| 41M| 78385 (2)| 00:00:13 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 366 | 2928 | 1 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS INMEMORY FULL | DAY_DIMENSION | 366 | 2928 | 1 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 14M| 341M| 78349 (2)| 00:00:13 | | |
| 6 | JOIN FILTER CREATE | :BF0001 | 888 | 10656 | 1 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS INMEMORY FULL | PRODUCT_DIMENSION | 888 | 10656 | 1 (0)| 00:00:01 | | |
| 8 | JOIN FILTER USE | :BF0001 | 160M| 1983M| 77948 (2)| 00:00:13 | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 160M| 1983M| 77948 (2)| 00:00:13 |:BF0000|:BF0000|
|* 10 | TABLE ACCESS INMEMORY FULL| SALES_FACT | 160M| 1983M| 77948 (2)| 00:00:13 |:BF0000|:BF0000|
---------------------------------------------------------------------------------------------------------------------
Q3:
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
| 1 | SORT GROUP BY NOSORT | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
|* 2 | HASH JOIN | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 366 | 2928 | 4 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | DAY_DIMENSION | 366 | 2928 | 4 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 14M| 341M| 85248 (2)| 00:00:14 | | |
|* 6 | TABLE ACCESS FULL | PRODUCT_DIMENSION | 888 | 10656 | 9 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE JOIN-FILTER| | 160M| 1983M| 84839 (2)| 00:00:14 |:BF0000|:BF0000|
| 8 | TABLE ACCESS FULL | SALES_FACT | 160M| 1983M| 84839 (2)| 00:00:14 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------------
Q4:
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 259 | 20720 | 176K (57)| 00:00:28 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6936_73F9117C | | | | | | |
| 3 | VECTOR GROUP BY | | 366 | 2928 | 1 (0)| 00:00:01 | | |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | | | | | |
|* 5 | TABLE ACCESS INMEMORY FULL | DAY_DIMENSION | 366 | 2928 | 1 (0)| 00:00:01 | | |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6937_73F9117C | | | | | | |
| 7 | VECTOR GROUP BY | | 1 | 12 | 1 (0)| 00:00:01 | | |
| 8 | HASH GROUP BY | | 1 | 12 | 1 (0)| 00:00:01 | | |
| 9 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | |
|* 10 | TABLE ACCESS INMEMORY FULL | PRODUCT_DIMENSION | 888 | 10656 | 1 (0)| 00:00:01 | | |
| 11 | SORT GROUP BY NOSORT | | 259 | 20720 | 176K (57)| 00:00:28 | | |
|* 12 | HASH JOIN | | 259 | 20720 | 176K (57)| 00:00:28 | | |
| 13 | MERGE JOIN CARTESIAN | | 366 | 7320 | 4 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6937_73F9117C | 1 | 12 | 2 (0)| 00:00:01 | | |
| 15 | BUFFER SORT | | 366 | 2928 | 2 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6936_73F9117C | 366 | 2928 | 2 (0)| 00:00:01 | | |
| 17 | VIEW | VW_VT_4FBA27B6 | 259 | 15540 | 176K (57)| 00:00:28 | | |
| 18 | VECTOR GROUP BY | | 259 | 3367 | 176K (57)| 00:00:28 | | |
| 19 | HASH GROUP BY | | 259 | 3367 | 176K (57)| 00:00:28 | | |
| 20 | KEY VECTOR USE | :KV0000 | | | | | | |
| 21 | KEY VECTOR USE | :KV0001 | | | | | | |
| 22 | PARTITION RANGE SUBQUERY | | 160M| 1983M| 77948 (2)| 00:00:13 |KEY(SQ)|KEY(SQ)|
|* 23 | TABLE ACCESS INMEMORY FULL| SALES_FACT | 160M| 1983M| 77948 (2)| 00:00:13 |KEY(SQ)|KEY(SQ)|
-----------------------------------------------------------------------------------------------------------------------------------
Q5:
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
| 1 | SORT GROUP BY NOSORT | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
|* 2 | HASH JOIN | | 1308K| 41M| 85288 (2)| 00:00:14 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 366 | 2928 | 4 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | DAY_DIMENSION | 366 | 2928 | 4 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 14M| 341M| 85248 (2)| 00:00:14 | | |
|* 6 | TABLE ACCESS FULL | PRODUCT_DIMENSION | 888 | 10656 | 9 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE JOIN-FILTER| | 160M| 1983M| 84839 (2)| 00:00:14 |:BF0000|:BF0000|
| 8 | TABLE ACCESS FULL | SALES_FACT | 160M| 1983M| 84839 (2)| 00:00:14 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------------
Build the DWH:
create table day_dimension
as
WITH my_days as (
SELECT LEVEL ID,trunc(sysdate)-3900+LEVEL my_day
FROM DUAL
CONNECT BY LEVEL <= 4000)
--
select
id day_id,
my_day current_day,
to_number(to_char(my_day,'YYYYMM')) month_id,
to_number(to_char(my_day,'YYYY')) year,
to_char(my_day,'DAY') day_name,
to_char(my_day,'MONTH') month_name,
to_char(my_day,'DAY DDMONTH','NLS_DATE_LANGUAGE = GERMAN')german_day,
to_char(my_day,'DAY DDMONTH','NLS_DATE_LANGUAGE = FRENCH')french_day,
case when my_day = trunc(sysdate) then 'Y' else 'N' end current_day_flag,
case when last_day(my_day) = my_day then 'Y' else 'N' end month_end_flag,
MONTHS_BETWEEN(LAST_DAY(my_day),last_day(sysdate)) months_back
from
my_days;
alter table day_dimension ADD CONSTRAINT day_pk PRIMARY KEY (day_id) USING INDEX;
create table product_dimension
as
select rownum product_id,object_name product_name,object_type product_type,
object_name||'-'||object_name||'-'||object_name||'-'||object_name||'-'||object_name||'-'||object_name product_name_long
from dba_objects where rownum <=10000;
alter table product_dimension ADD CONSTRAINT prod_pk PRIMARY KEY (product_id) USING INDEX;
drop table sales_fact;
create table sales_fact
(day_id NUMBER(15,0) NOT NULL,
product_id NUMBER(15,0) NOT NULL,
sale_price number,
filler_text VARCHAR2(1000))
PARTITION BY RANGE (day_id) INTERVAL (1)
(PARTITION "PMIN" VALUES LESS THAN (1) SEGMENT CREATION DEFERRED)
;
alter table sales_fact add constraint day_id_fk foreign key (day_id)
references day_dimension (day_id) disable;
alter table sales_fact add constraint product_id_fk foreign key (product_id)
references product_dimension (product_id) disable;
insert into sales_fact (day_id,product_id,sale_price,filler_text)
SELECT 1 day_id,
trunc(DBMS_RANDOM.VALUE*10000) product_id,
round(DBMS_RANDOM.VALUE*1000,2)sale_price,
'The PL/SQL interface to the components of the Advisor is described at the end of this paper'filler_text
FROM DUAL
CONNECT BY LEVEL <= 40000;
commit;
begin
for i in 2..4000 loop
insert into sales_fact (day_id,product_id,sale_price,filler_text)
select i,product_id,sale_price,filler_text from sales_fact where day_id=1;
commit;
end loop;
end;
/
-- get stats:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'DWHADM',
tabname => 'DAY_DIMENSION',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
block_sample => TRUE,
cascade => TRUE);
end;
/
begin
for i in (select * from all_tables where table_name in ('SALES_FACT','PRODUCT_DIMENSION')) loop
DBMS_STATS.GATHER_TABLE_STATS (
ownname => i.owner,
tabname => i.table_name,
block_sample => TRUE,
degree => 4,
cascade => TRUE);
end loop;
end;
/