....
We have an environment with a huge amount of memory available - probably enough actually to just throw every table from the key star schema's fully in memory.
Is that however a "good" idea? Or should we populate just the large fact tables in memory and let the dimensions come from disk/buffer cache?
Or just the key measure and dimension keys off the fact table
....
Having all the required attributes/column from the FACT and DIMENSION table at in-memory will induce INMEMORY AGGREGATION (IMA) (aka Vector Transformation)
inmemory team explains them here
https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database5 How vector transformation improves performance ? Below is my test case to show that.
One of the key feature of Inmemory database is it doesn't ship IMCU across the nodes of RAC instance,
if you are on Engineered system you could make use of DUPLICATE /DUPLICATE ALL sub-clause that helps
us to duplicate dimension across all the RAC instance, while the FACT table would simply distribute by partition or sub-partition.
Joins could then takes place entirely within each instance, since all the data needed for each join would be in-memory.
This is analogous to "Partition wise join" since the entire dimension table resides in each IM column store
https://blogs.oracle.com/In-Memory/entry/oracle_database_in_memory_on2 rajesh@ORA12C> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
5 rows selected.
rajesh@ORA12C> show parameter inmemory_size
NAME TYPE VALUE
--------------------------------------------- ----------- -------------------------
inmemory_size big integer 152M
rajesh@ORA12C>
rajesh@ORA12C> select cdb from v$database ;
CDB
---
NO
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> create table jobs
2 as
3 select rownum as job_id,
4 job_title,
5 trunc(dbms_random.value(1,100)) as min_sal,
6 trunc(dbms_random.value(200,300)) as max_sal
7 from ( select distinct owner as job_title
8 from all_objects ) ;
Table created.
rajesh@ORA12C>
rajesh@ORA12C> alter table jobs
2 add constraint jobs_pk
3 primary key(job_id);
Table altered.
rajesh@ORA12C>
rajesh@ORA12C> create table dept
2 as
3 select rownum as deptno,
4 dept_name,
5 trunc(dbms_random.value(1,100)) as manager_id,
6 trunc(dbms_random.value(1,50)) as location_id
7 from ( select distinct object_type as dept_name
8 from all_objects) ;
Table created.
rajesh@ORA12C>
rajesh@ORA12C> alter table dept
2 add constraint dept_pk
3 primary key(deptno);
Table altered.
rajesh@ORA12C>
rajesh@ORA12C> create table emp
2 partition by hash(emp_id)
3 ( partition p1,
4 partition p2,
5 partition p3,
6 partition p4 )
7 as
8 select rownum as emp_id,
9 object_name as first_name,
10 object_name as last_name,
11 object_name||'@gmail.com' as email_id,
12 object_id as phone_no,
13 created as hire_date,
14 trunc(dbms_random.value(1,32)) as job_id,
15 trunc(dbms_random.value(1,5000)) as salary,
16 trunc(dbms_random.value(1,3000)) as comm,
17 rownum as manager_id ,
18 trunc(dbms_random.value(1,39)) as deptno
19 from all_objects;
Table created.
rajesh@ORA12C> declare
2 l_empno number;
3 begin
4 select max(emp_id) into l_empno
5 from emp;
6
7 execute immediate ' alter session force parallel dml parallel 4';
8 for i in 1..7
9 loop
10 dbms_application_info.set_client_info('i ='||i);
11 insert /*+ append */ into emp
12 ( EMP_ID,FIRST_NAME,LAST_NAME,EMAIL_ID,
13 PHONE_NO,HIRE_DATE,JOB_ID,SALARY,COMM,MANAGER_ID,
14 DEPTNO )
15 select l_empno+rownum,FIRST_NAME,LAST_NAME,EMAIL_ID,
16 PHONE_NO,HIRE_DATE,JOB_ID,SALARY,COMM,MANAGER_ID,
17 DEPTNO
18 from emp ;
19 l_empno := l_empno + sql%rowcount;
20 commit;
21 end loop;
22 execute immediate ' alter session disable parallel dml';
23 end;
24 /
PL/SQL procedure successfully completed.
rajesh@ORA12C>
rajesh@ORA12C> begin
2 dbms_stats.gather_table_stats(user,'emp',
3 degree=>2,
4 method_opt=>'for all columns size 254',
5 no_invalidate=>false,
6 cascade=>true );
7 end;
8 /
PL/SQL procedure successfully completed.
rajesh@ORA12C> select /*+ parallel(emp,4) */ count(*) from emp;
COUNT(*)
----------
11524480
1 row selected.
rajesh@ORA12C> select count(*) from dept;
COUNT(*)
----------
39
1 row selected.
rajesh@ORA12C> select count(*) from jobs;
COUNT(*)
----------
32
1 row selected.
rajesh@ORA12C> alter table dept inmemory;
Table altered.
rajesh@ORA12C> alter table jobs inmemory;
Table altered.
rajesh@ORA12C> alter table emp inmemory
2 no inmemory(emp_id,first_name,last_name,
3 hire_date,email_id,
4 phone_no,manager_id);
Table altered.
rajesh@ORA12C>
rajesh@ORA12C> column owner format a10
rajesh@ORA12C> column segment_name format a10
rajesh@ORA12C> column partition_name format a10
rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size
2 from v$im_segments;
no rows selected
rajesh@ORA12C> alter table emp modify partition p1 inmemory;
Table altered.
rajesh@ORA12C> select count(*) from emp partition(p1);
COUNT(*)
----------
2880928
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p2 inmemory;
Table altered.
rajesh@ORA12C> select count(*) from emp partition(p2);
COUNT(*)
----------
2881300
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p3 inmemory;
Table altered.
rajesh@ORA12C> select count(*) from emp partition(p3);
COUNT(*)
----------
2883687
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p4 inmemory;
Table altered.
rajesh@ORA12C> select count(*) from emp partition(p4);
COUNT(*)
----------
2878565
1 row selected.
rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size
2 from v$im_segments;
OWNER SEGMENT_NA PARTITION_ INMEMORY_SIZE
---------- ---------- ---------- -------------
RAJESH EMP P4 23527424
RAJESH EMP P1 23527424
RAJESH EMP P3 23527424
RAJESH EMP P2 23527424
4 rows selected.
rajesh@ORA12C>
Tkprof shows me this.
select d.location_id, j.min_sal,j.max_sal,
sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 6 14 0
Fetch 2 0.04 0.06 2 22 2 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.08 2 28 16 4
select /*+ no_vector_transform */ d.location_id, j.min_sal,j.max_sal,
sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.15 0 18 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.17 0.16 0 18 0 4
select /*+ no_vector_transform no_inmemory(e) no_inmemory(d) no_inmemory(j) */
d.location_id, j.min_sal,j.max_sal,
sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.23 15.83 203769 203818 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.24 15.84 203769 203818 0 4
Vector Transformation for smarter CPU processing.